Mantenimiento de índices en SQL Server: cómo evitar la fragmentación

Los índices son objetos de la base de datos diseñados de manera similar a los índices que usamos en los libros para encontrar contenidos en el mismo.

Éstos ordenan los datos en nuestras tablas, permitiendo un acceso más rápido y eficiente a aquéllos que estemos consultando, modificando o eliminando.

Por ello, la actualización de los registro de las tablas de la base datos obliga al servidor SQL a realizar ciertas operaciones que nos garanticen el orden de los datos en los índices. Debido a esta actualización, la información almacenada en los índices se ve fragmentada con su uso. Esta fragmentación depende de parámetros como el fill factor, número de páginas, tamaño del índice y frecuencia de actualización.

En definitiva, un índice sobre un tabla, debido a actualizaciones sobre ésta, puede perder eficacia y, con ello,  nuestras consultas volverse más lentas y su rendimiento deteriorarse.

Es necesario, pues, realizar periódicamente tareas de consulta del estado de nuestros índices, para mantener la fragmentación por debajo de unos límites recomendables. Ésta se mide en porcentaje, correspondiendo un valor bajo (entre 0% y 10%) a un estado óptimo del índice y siendo peor el estado cuando el valor de dicho indicador mayor sea.

Defragmentación, al rescate

Para evitar el deterioro del rendimiento en nuestro servidor, deberemos mantener nuestros índices en un estado de fragmentación óptimo. Lo podremos lograr sencillamente siguiendo estos pasos.

• Primer paso: detectar fragmentación en los índices de tu base de datos. Para ello, nos basaremos en la vista de sistema sys.dm_db_index_physical_stats, que encapsularemos en la siguiente query:

• Segundo paso: ejecutar un script para defragmentar los índices con problemas. El script determina si hay que hacer un Reorganize o un Rebuild para cada índice:

-- Ensure a USE  statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000); 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 AND page_count > 1000;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

Diferencias entre reconstruir (REBUILD) y reorganizar (REORGANIZE) un índice

Básicamente, existen dos maneras de volver a ordenar un índice. La más eficaz consiste en eliminar el índice y volver a crearlo, desde cero. Ésta garantiza un resultado óptimo, dejando la fragmentación en 0%. Como contrapartida, es una operación más costosa y genera bloqueos sobre la tabla cuyo índice se está actualizando. Usaremos esta opción (REBUILD) cuando la fragmentación del índice supere el 30%.

Para índices ligeramente fragmentados (entre el 10% y el 30%) existe la opción de reorganizar (REORGANIZE) el índice. Esta opción simplemente reordena los datos del índice dentro de las páginas que ocupa, devolviendo un orden adecuado al mismo. Se trata de una operación mucho más ligera, que no bloquea las tablas y vistas subyacentes.

Adicionalmente, habrá que tener en cuenta que solamente tiene sentido efectuar la operación de defragmentación sobre índices con un tamaño superior a 1000 páginas. Ya que, por debajo de este valor, SQL considera que es más eficiente escribir los datos de forma desordenada que mantener un índice poco efectivo, al tratarse de una tabla pequeña.

Referencias:

• Microsoft SQL Server 2000 Index Defragmentation Best Practices