Index Fragmentation

The following script can be run in SSMS↗ to identify and address index fragmentation.

This script identifies indexes with fragmentation levels above a specified threshold and either reorganizes or rebuilds them based on the fragmentation percentage. The @DryRun variable allows you to test the script without making changes.

Changing the @DryRun variable to 0 will execute the commands and should be performed during a maintenance window because it may lock resources.

-- Configurable parameters
DECLARE @MinFrag FLOAT = 5.0;
DECLARE @MaxFrag FLOAT = 30.0;
DECLARE @DryRun BIT = 1; -- Set to 0 to execute

-- Logging table (optional)
IF OBJECT_ID('tempdb..#IndexMaintenanceLog') IS NOT NULL DROP TABLE #IndexMaintenanceLog;
CREATE TABLE #IndexMaintenanceLog (
    LogTime DATETIME,
    TableName NVARCHAR(128),
    IndexName NVARCHAR(128),
    Fragmentation FLOAT,
    Action NVARCHAR(20),
    Command NVARCHAR(MAX)
);

-- Cursor over fragmented indexes
DECLARE IndexCursor CURSOR FOR
SELECT 
    OBJECT_NAME(ps.object_id) AS TableName,
    i.name AS IndexName,
    ps.avg_fragmentation_in_percent,
    i.object_id,
    i.index_id
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.database_id = DB_ID()
  AND i.type_desc IN ('CLUSTERED', 'NONCLUSTERED')
  AND i.is_disabled = 0
  AND i.is_hypothetical = 0
  AND ps.page_count > 100 -- Skip tiny indexes
  AND ps.avg_fragmentation_in_percent >= @MinFrag;

DECLARE @TableName NVARCHAR(128), @IndexName NVARCHAR(128), @Frag FLOAT;
DECLARE @ObjId INT, @IdxId INT, @SQL NVARCHAR(MAX), @Action NVARCHAR(20);

OPEN IndexCursor;
FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName, @Frag, @ObjId, @IdxId;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @Frag BETWEEN @MinFrag AND @MaxFrag
    BEGIN
        SET @Action = 'REORGANIZE';
        SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REORGANIZE;';
    END
    ELSE IF @Frag > @MaxFrag
    BEGIN
        SET @Action = 'REBUILD';
        SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REBUILD;';
    END
    ELSE
    BEGIN
        SET @Action = 'SKIP';
        SET @SQL = '-- Fragmentation below threshold';
    END

    INSERT INTO #IndexMaintenanceLog
    VALUES (GETDATE(), @TableName, @IndexName, @Frag, @Action, @SQL);

    IF @DryRun = 0 AND @Action IN ('REORGANIZE', 'REBUILD')
        EXEC sp_executesql @SQL;

    FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName, @Frag, @ObjId, @IdxId;
END

CLOSE IndexCursor;
DEALLOCATE IndexCursor;

-- View log
SELECT * FROM #IndexMaintenanceLog ORDER BY LogTime;