Optimización Automática: Desfragmentación de Índices SQL 2005/2008

Por Javier Loria

Cuando en una base de datos relacional sobre una tabla se hacen operaciones de inserción, borrado y actualización de columnas es natural que se produzca fragmentación lógica de índices. Una de las tareas del DBA es realizar tareas para mantener estos niveles de fragmentación bajos. La forma nativa de SQL 2005 y SQL 2008 de desfragmentar índices es por medio de planes de mantenimiento de base de datos. Pero deber recordarse varias cosas:

1. En general las actividades de fragmentación de índices participan en transacciones y pueden bloquear durante mucho tiempo páginas y tablas completas, si no se tiene cuidado al seleccionar diferentes opciones. La opción ONLINE, disponible solo en la versión corporativa de SQL, permite hacer operaciones de desfragmentación sin bloquear las tablas.
2. El modo de recuperación de la base de datos puede afectar el desempeño de la desfragmentación. El ALTER INDEX REBUILD y DBCC DBREINDEX son transacciones mínimamente registradas en la bitácora de transacciones cuando el modo de recuperación de la BD es Bulk-logged o Simple. El ALTER INDEX REORGANIZE y el DBCC INDEXDEFRAG son siempre transacciones registradas completamente en la bitácora de transacciones.
3. El asistente de planes de mantenimiento ofrece dos alternativas:
   1. REORGANIZE (Reorganizar): Esta opción emplea una tarea que genera ALTER INDEX REORGANIZE para todas las tablas de una base de datos o para una lista de tablas seleccionadas.
   2. REBUILD (Reconstruir): genera un ALTER INDEX REBUILD para todas las tablas de una base de datos o para una lista de tablas seleccionadas.
   La diferencia entre un REORGANIZE y un REBUILD es que el REORGANIZE desfragmenta los niveles hoja del índice, mientras que el REBUILD desfragmenta todos los niveles del índice.
4. Realizar tareas de mantenimiento sobre todos los índices con una plan de mantenimiento es una tarea que puede tomar mucho tiempo y no ser aceptable por dos razones:
   1. Llena el transacción log.
   2. Bloquea una cantidad importante de recursos del servidor, durante una ventana grande de tiempo.

El código que sigue a continuación puede ayudar al mantenimiento automatizado de índices de una BD relacional que cumplan con las siguientes condiciones:

1. BD OLTP, mediana o altamente normalizada.
2. Tamaño entre 2 y 100 Gb.
3. Tablas: 500 a 10,000.
4. Puede ser en servidores 24×7.

La estrategia es usar el código para programar 2 jobs que desfragmenten los índices:

1. Job de Matenimiento Índices Medianos: (1 vez cada hora).
   1. 10 índices medianos: EXEC dbo.DefragmentaIndices ‘Medianos’
2. Mantenimiento Diario: (1 vez cada día, en horarios de menos ocupación del servidor)
   1. 10 índices grandes: EXEC dbo.DefragmentaIndices ‘Grandes’
   2. 100 índices pequeños: EXEC dbo.DefragmentaIndices ‘Pequenos’

Para escribir el código conté con la ayuda de: Eladio Rincón, Miguel Egea y Enrique Catalá.
CREATE FUNCTION FilteredIndexFragmentation(
     @DatabaseID                INT
    , @ObjectID                    INT
    , @IndexID                        INT
    , @PartitionNumber        INT=NULL
    , @AverageFragmentation INT =0
    , @FragmentCount        BIGINT =0)
— Author:                         Javier Loria, Solid Quality Mentors
— Create date:                    5/Dic/2008
— Description:                    Funcion que lista los indices con un porcentaje de fragmentacion LOGICA mayor al indicado,
— y con una cantidad mayor de fragmentos.
— Encapsula dm_db_index_physical_stats., se requiere para poder hacer CROSS APPLY.
— No reporta fragmentacion de tablas sin indices, indices XML o Geograficos.
— Emplea el modo limitado ‘LIMITED’, por el alto costo y mal desempeno del modo ‘DETAILED’
RETURNS @IndexStats TABLE(
     DatabaseID                    SMALLINT
    , ObjectID                        INT
    , IndexID                            INT
    , PartitionNumber            INT
    , IndexDepth                    TINYINT
    , FragmentationRate        FLOAT
    , FragmentCount            BIGINT
    , AverageFragmentSize FLOAT
    , PageCount                    BIGINT)
BEGIN
    INSERT INTO @IndexStats(DatabaseID, ObjectID, IndexID, PartitionNumber, IndexDepth,FragmentationRate
        , FragmentCount, AverageFragmentSize, PageCount)
    SELECT database_id, object_id, index_id, partition_number,
         index_depth, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count
    FROM sys.dm_db_index_physical_stats (@DatabaseID, @ObjectID, @IndexID, @PartitionNumber, ‘LIMITED’ )
    WHERE index_type_desc IN(‘CLUSTERED INDEX’, ‘NONCLUSTERED INDEX’)
    AND avg_fragmentation_in_percent > @AverageFragmentation
    AND fragment_count>@FragmentCount
RETURN
END
GO

CREATE PROCEDURE dbo.DefragmentaIndices(
— Author:                             Javier Loria, Solid Quality Mentors
— Create date:                    5/Dic/2008
— Description:                    Procedimiento que defragmenta indices, de una base de datos, de acuerdo al tamaño del indices.
— Indices Grandes:         10 Indices de cualquier tamaño, con mas de 30% de Fragmentacion y 10 o más segmentos
— Indices Medianos:        10 Indices entre 8192 y 32 páginas, mas del 20% de Fragmentacion y 3 o más segmentos
— Indices Pequenos: 100 Indices entre 256 y 32 páginas, , mas del 20% de Fragmentacion y 3 o más segmentos
— Parametros:                    @Tipo= Grandes, Medianos y Pequenos. Default=Grandes
    @Tipo    VARCHAR(10)=‘Grandes’        — Medianos, Pequenos
)
AS
DECLARE @db_id         INT;
DECLARE @NumPages     BIGINT;
DECLARE @NumIndexes INT;

DECLARE @Comando NVARCHAR(MAX);
DECLARE @DB INT
SET NOCOUNT ON;
SET @DB=DB_ID()        –Requerido por modo de compatibilidad 80.

IF (@Tipo NOT IN(‘Grandes’, ‘Medianos’, ‘Pequenos’))
    BEGIN
    RAISERROR(‘Parametro @Tipo Invalido, use: Grandes, Medianos o Pequenos’, 16,1);
    RETURN;
    END
SET @db_id = DB_ID(N’Adam’);
SET @Comando=”;

IF @Tipo=‘Grandes’
    BEGIN
    — Reindexa las 10 mas grandes sin importar el tamano
    SELECT TOP 10 @Comando=@Comando+CHAR(13)+CHAR(10)+‘ALTER INDEX ‘
        + Indexes.Name
        +‘ ON ‘+OBJECT_NAME(ObjectID)+‘ REBUILD;’
    FROM FilteredIndexFragmentation(@DB, NULL, NULL, NULL, 30,10) AS FIF
    JOIN SYS.INDEXES AS Indexes
        ON INDEXES.OBJECT_ID=ObjectID
            AND INDEXES.INDEX_ID=IndexID
    ORDER BY (IndexDepth*IndexDepth*FragmentationRate*FragmentCount/100) DESC
    END
ELSE
    BEGIN
    — Reindexa las 10 si es Medianos, 50 si es Pequenos
    SELECT TOP (CASE WHEN @Tipo=‘Medianos’ THEN 10 ELSE 50 END)
        @Comando=@Comando+CHAR(13)+CHAR(10)+‘ALTER INDEX ‘
        + IndexPages.Name
        +‘ ON ‘+OBJECT_NAME(ObjectID)+‘ REBUILD;’
    FROM (SELECT indexes.object_id
                , indexes.index_id
                , Indexes.Name
                , sum(allocation_units.total_pages) as totalPages
            FROM sys.indexes AS indexes
            JOIN sys.partitions AS partitions
            ON indexes.object_id = partitions.object_id
                    and indexes.index_id = partitions.index_id
            JOIN sys.allocation_units AS allocation_units
            ON partitions.partition_id = allocation_units.container_id
            WHERE indexes.index_id >0
             AND allocation_units.total_pages>0
            GROUP BY indexes.object_id, indexes.index_id, Indexes.Name
            HAVING sum(allocation_units.total_pages) BETWEEN 32 AND
                (CASE WHEN @Tipo=‘Medianos’ THEN 8192 ELSE 256 END)        
— Medianos si tienen menos de 8192 paginas, Pequenos si tienen menos de 256 paginas
            ) AS IndexPages
— No se emplea el CROSS APPLY por compatibilidad con nivel de compatibilidad 80 (SQL 2000),
— es posible que tenga un importante impacto en desempeno usar el CROSS APPLY.
— se recomienda usar CROSS APPLY para compatibilidad 90 o 100.
—    CROSS APPLY FilteredIndexFragmentation(@DB, IndexPages.object_id, IndexPages.index_id, NULL, 20,3) AS FIF
    JOIN FilteredIndexFragmentation(@DB, NULL, NULL, NULL, 20,3) AS FIF
    ON IndexPages.object_id=FIF.ObjectID
        AND IndexPages.index_id=FIF.IndexID
    — La columna IndexDepth esta deliberadamente 2 veces, para dar prioridad a indices mas profundos.
    ORDER BY (IndexDepth*IndexDepth*FragmentationRate*FragmentCount/100) DESC

    END
EXEC sp_executesql @Comando
GO 


Articulo de Javier Loria
Fuente:   Javier Loria SolidQ