Hola Todos,

El siguiente es un script de Transact-SQL para verificar el estado de fragmentacion de los indices en una base de datos de SQL. Este Script te permite no solo verificar su estado, también te permite realizar una reorganización o la reconstrucción.
Espero les sirva
Saludos,
Samuel

— Seleccione primero la base de datos USE GO

SET NOCOUNT ON

— ====================================================================================
— || Configuration variables:
— || – 5 es el porcentaje minimo para comenzar a hacer re organizacion de indices
— || – 30 es el porcentaje de criticidad maximo para comenzar un rebuild de indices
— || – por debajo del 30 %, realiza re organización de indices y por encima un rebuild
— || – 0 es el valor de relleno por defecto puede variar de 0 a 99
— || – La documentacion de Microsoft recomienda SET a 80
— || – @report_only SET 1 para informe ó SET 0 para informe y proceso de optimizacion
— ====================================================================================

DECLARE @reorg_frag_thresh   float   SET @reorg_frag_thresh   = 5.0
DECLARE @rebuild_frag_thresh float   SET @rebuild_frag_thresh = 30.0
DECLARE @fill_factor         tinyint SET @fill_factor         = 90
DECLARE @report_only         bit     SET @report_only         = 1

— Variables requeridas para el procesamiento

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)
DECLARE @intentions     nvarchar(4000)
DECLARE @table_var      TABLE(
                          objectid     int,
                          indexid      int,
                          partitionnum int,
                          frag         float
                        )

— Se seleccionan las tablas con indices defragmentados
— sys.dm_db_index_physical_stats comprueba el estado de los indices
— y nos retorna el nombre del indice.

INSERT INTO
    @table_var
SELECT
    [object_id]                    AS objectid,
    [index_id]                     AS indexid,
    [partition_number]             AS partitionnum,
    [avg_fragmentation_in_percent] AS frag
FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ‘LIMITED’)
WHERE
    [avg_fragmentation_in_percent] > @reorg_frag_thresh AND
    index_id > 0

DECLARE partitions CURSOR FOR
    SELECT * FROM @table_var

OPEN 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 WITH (NOLOCK)
            JOIN sys.schemas as s WITH (NOLOCK)
                ON s.[schema_id] = o.[schema_id]
    WHERE
        o.[object_id] = @objectid

    SELECT
        @indexname = QUOTENAME([name])
    FROM
        sys.indexes WITH (NOLOCK)
    WHERE
        [object_id] = @objectid AND
        [index_id] = @indexid

    SELECT
        @partitioncount = count (*)
    FROM
        sys.partitions WITH (NOLOCK)
    WHERE
        [object_id] = @objectid AND
        [index_id] = @indexid

    — Aqui construimos el comando a ejecutar según los criterios de % de criticidad

    SET @intentions =
        @schemaname + N’.’ +
        @objectname + N’.’ +
        @indexname + N’:’ + CHAR(13) + CHAR(10)
    SET @intentions =
        REPLACE(SPACE(LEN(@intentions)), ‘ ‘, ‘=’) + CHAR(13) + CHAR(10) +
        @intentions
    SET @intentions = @intentions +
        N’ FRAGMENTATION: ‘ + CAST(@frag AS nvarchar) + N’%’ + CHAR(13) + CHAR(10)

    — Instrucción para hacer una defragmentación
— puede usarce tambien un DBCC INDEXDEFRAG (‘DATABASE’,’TABLA’)

IF @frag < @rebuild_frag_thresh BEGIN
        SET @intentions = @intentions +
            N’ OPERATION: REORGANIZE’ + CHAR(13) + CHAR(10)
        SET @command =
            N’ALTER INDEX ‘ + @indexname +
            N’ ON ‘ + @schemaname + N’.’ + @objectname +
            N’ REORGANIZE’
    END

— Instrucción para un rebuild del indice
— puede usarce tambien un DBCC DBREINDEX (‘database.dbo.tabla’,’indice’,@fill_factor)

    IF @frag >= @rebuild_frag_thresh BEGIN
        SET @intentions = @intentions +
            N’ OPERATION: REBUILD’ + CHAR(13) + CHAR(10)
        SET @command =
            N’ALTER INDEX ‘ + @indexname +
            N’ ON ‘ + @schemaname + N’.’ +     @objectname +
            N’ REBUILD’
    END
    IF @partitioncount > 1 BEGIN
        SET @intentions = @intentions +
            N’ PARTITION: ‘ + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10)
        SET @command = @command +
            N’ PARTITION=’ + CAST(@partitionnum AS nvarchar(10))
    END
    IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN
        SET @intentions = @intentions +
            N’ FILL FACTOR: ‘ + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10)
        SET @command = @command +
            N’ WITH (FILLFACTOR = ‘ + CAST(@fill_factor AS nvarchar) + ‘)’
    END

    — Executa las acciones de informe ó optimizacion acorde con el valor de @report_only

    IF @report_only = 0 BEGIN
        SET @intentions = @intentions + N’ EXECUTING: ‘ + @command
        PRINT @intentions
        EXEC (@command)
    END ELSE BEGIN
        PRINT @intentions
    END

END

CLOSE partitions
DEALLOCATE partitions

GO

Leave a Comment