Esta es una versión mejorada del procedimiento automático de desfragmentación de índices, que usa una tabla para almacenar el resultado de la consulta y así evita usar una y otra vez dm_db_index_physical_stats que tiene serios problemas de rendimiento.

USE <BaseDatos>;

GO

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 TABLE dbo.FragmentacionIndices(

     Name sysname        NOT NULL

        PRIMARY KEY

    , Indice varchar(512)    NOT NULL

    , Tamano varchar(8)        NOT NULL

    , TotalPages bigint        NOT NULL

);

GO

CREATE PROCEDURE [dbo].[DefragmentaIndices](

@Tipo VARCHAR(10)=‘Grandes’ — Medianas, Pequenas, Genera

)

AS

DECLARE @db_id INT;

DECLARE @NumPages BIGINT;

DECLARE @NumIndexes INT;

DECLARE @Comando NVARCHAR(MAX);

DECLARE @DB INT

— Parametros «ALAMBRADOS»

    — +8192 Paginas: Grande

    — +256 Paginas: Mediano

    — -256 Paginas: Pequeno

— -32 Paginas: No Defragmenta

— La tabla se llena con las 400 Tablas + Grandes

    — Grandes: Defragmenta 10, Medianas: Defragmenta 10, Pequenas: Defragmanta 100

SET NOCOUNT ON;

SET @DB=DB_ID() –Requerido por modo de compatibilidad 80.

 

IF (@Tipo NOT IN(‘Grandes’, ‘Medianas’, ‘Pequenas’, ‘Genera’))

     BEGIN

     RAISERROR(‘Parametro @Tipo Invalido, use: Grandes, Medianas o Pequenas’, 16,1);

     RETURN;

     END

SET @db_id = DB_ID();

SET @Comando=»;

IF @Tipo=‘Genera’

    BEGIN

    TRUNCATE TABLE dbo.FragmentacionIndices;

    INSERT INTO dbo.FragmentacionIndices(Name, Indice, Tamano, TotalPages)

    SELECT TOP 400

        IndexPages.Name

        , ‘ALTER INDEX ‘

        + IndexPages.Name

        +‘ ON ‘+OBJECT_NAME(ObjectID)+‘ REBUILD;’ AS Indice

        , CASE WHEN totalPages>=8192 THEN ‘Grandes’

             WHEN totalPages>=256 THEN ‘Medianas’

             ELSE ‘Pequenas’ END AS Tamano

        , TotalPages

    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)> 32) AS IndexPages

    — 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

    ORDER BY (IndexDepth*IndexDepth*FragmentationRate*FragmentCount/100) DESC;

    RETURN;

    END

 

IF @Tipo=‘Grandes’

BEGIN

— Reindexa las 10 mas grandes sin importar el tamano

    SET @Comando=»

    SELECT TOP 10 @Comando=@Comando+CHAR(13)+CHAR(10)+Indice

    FROM dbo.FragmentacionIndices

    ORDER BY TotalPages DESC;

    — Borra

    DELETE dbo.FragmentacionIndices

FROM dbo.FragmentacionIndices

JOIN (SELECT TOP 10 Name FROM dbo.FragmentacionIndices

            ORDER BY TotalPages DESC) AS A

    ON FragmentacionIndices.Name=A.Name

END

IF @Tipo=‘Medianas’

BEGIN

— Reindexa las 10 mas grandes, que no sean «Grandes» (Medianas, Pequenas)

    SET @Comando=»

    SELECT TOP 10 @Comando=@Comando+CHAR(13)+CHAR(10)+Indice

    FROM dbo.FragmentacionIndices

    WHERE Tamano<>‘Grandes’

    ORDER BY TotalPages DESC;

    DELETE dbo.FragmentacionIndices

FROM dbo.FragmentacionIndices

JOIN (SELECT TOP 10 Name

            FROM dbo.FragmentacionIndices

            WHERE Tamano<>‘Grandes’

            ORDER BY TotalPages DESC) AS A

    ON FragmentacionIndices.Name=A.Name

 

END

IF @Tipo=‘Pequenas’

BEGIN

— Reindexa las 100 Pequenas + Grandes

    SET @Comando=»

    SELECT TOP 100 @Comando=@Comando+CHAR(13)+CHAR(10)+Indice

    FROM dbo.FragmentacionIndices

    WHERE Tamano=‘Pequenas’

    ORDER BY TotalPages DESC;

    DELETE dbo.FragmentacionIndices

FROM dbo.FragmentacionIndices

JOIN (SELECT TOP 100 Name

            FROM dbo.FragmentacionIndices

            WHERE Tamano=‘Pequenas’

            ORDER BY TotalPages DESC) AS A

    ON FragmentacionIndices.Name=A.Name

END

EXEC sp_executesql @Comando

GO

/*

— Pruebas

EXEC [dbo].[DefragmentaIndices] ‘Genera’

SELECT * FROM dbo.FragmentacionIndices

EXEC [dbo].[DefragmentaIndices] ‘Grandes’

EXEC [dbo].[DefragmentaIndices] ‘Medianas’

EXEC [dbo].[DefragmentaIndices] ‘Pequenas’

*/