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

 

 

Últimas entradas de Javier Loria (ver todo)