As a database grows, maintaining optimal performance becomes crucial. One of the key contributors to performance degradation over time is index fragmentation. SQL Server provides two essential methods to handle index fragmentation: Rebuilding and Reorganizing indexes.

Understanding Index Fragmentation

Indexes are like a roadmap for SQL Server, allowing it to quickly find and retrieve data. However, over time, changes to the data (such as inserts, updates, and deletes) can cause fragmentation, meaning the data in the index is no longer stored contiguously. This slows down data retrieval and increases the amount of work SQL Server has to do.

Fragmentation Types:

Internal Fragmentation: Empty space within pages due to updates or deletes.
External Fragmentation: Data pages are out of order, leading to slower scans.

To fix fragmentation, SQL Server provides two operations: Rebuilding and Reorganizing indexes.

Rebuild vs. Reorganize: What’s the Difference?

1. Rebuild Index

Rebuilding an index drops and recreates the index from scratch. This method ensures that all data is restructured in contiguous pages, which removes fragmentation completely.

Key Features of Rebuild:

Fully reorganizes the index pages.
Drops and recreates the index, which locks the table (unless done online).
Automatically updates statistics for the index.
Requires more resources (CPU, memory) and can be time-consuming for large tables.

Syntax:

ALTER INDEX  ON  REBUILD;

When to Rebuild?

Use when fragmentation exceeds 30%.
If you need maximum performance improvement.
When you have a maintenance window, as it’s more resource-intensive.

2. Reorganize Index

Reorganizing an index is a lighter operation compared to rebuilding. It defragments the index at the leaf level by physically reordering the data pages, but does not recreate the entire index.

Key Features of Reorganize:

Only affects the leaf-level pages of the index.
It’s an online operation, so the table remains accessible.
Consumes fewer resources than a rebuild.
Does not automatically update statistics.

Syntax:

ALTER INDEX  ON  REORGANIZE;

When to Reorganize?

Use when fragmentation is between 10% and 30%.
When you need a lightweight operation with minimal disruption.

How to Check Index Fragmentation

Before deciding to rebuild or reorganize an index, it’s essential to know the fragmentation level. You can check this using SQL Server’s built-in Dynamic Management Views (DMVs). The following query will show the average fragmentation percentage for indexes in your database:

SELECT
    OBJECT_NAME(ips.object_id) AS TableName,
    si.name AS IndexName,
    ips.avg_fragmentation_in_percent
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
JOIN
    sys.indexes si ON ips.object_id = si.object_id AND ips.index_id = si.index_id
WHERE
    ips.avg_fragmentation_in_percent > 10;

This query will return all indexes with fragmentation above 10%, allowing you to decide whether to rebuild or reorganize.

Automating Index Maintenance

Manually monitoring and maintaining indexes can be time-consuming, especially for large databases. Automating the process ensures that indexes are maintained regularly, keeping your database in top shape.

Here’s a script that automates index maintenance based on fragmentation levels:

IF OBJECT_ID('dbo.ADTDB_IndexMaintenanceLog', 'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.ADTDB_IndexMaintenanceLog;
END

GO


IF OBJECT_ID('dbo.ADTDB_IndexMaintenanceLog', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.ADTDB_IndexMaintenanceLog (
        LogID               BIGINT              IDENTITY(1,1)  NOT NULL,
        RunID               UNIQUEIDENTIFIER    NOT NULL,
        RunDateTime         DATETIME2           NOT NULL,
        RowID               INT                 NOT NULL,  -- run ke andar row order (loop cursor)
        SchemaName          NVARCHAR(128)       NOT NULL,
        TableName           NVARCHAR(128)       NOT NULL,
        IndexName           NVARCHAR(128)       NOT NULL,
        IndexType           NVARCHAR(60)        NOT NULL,
        PartitionNumber     INT                 NOT NULL,
        FragmentationPct    FLOAT               NOT NULL,
        PageCount           BIGINT              NOT NULL,
        PlannedAction       NVARCHAR(20)        NOT NULL,
        ExecutedSQL         NVARCHAR(MAX)       NULL,
        ExecutionStatus     NVARCHAR(20)        NULL,
        ErrorMessage        NVARCHAR(MAX)       NULL,
        DurationMS          INT                 NULL,
        SkipReason          NVARCHAR(200)       NULL,
        CONSTRAINT PK_ADTDB_IndexMaintenanceLog PRIMARY KEY CLUSTERED (LogID)
    );

    CREATE UNIQUE NONCLUSTERED INDEX IX_ADTDB_IndexMaintenanceLog_RunID_RowID
        ON dbo.ADTDB_IndexMaintenanceLog (RunID, RowID);
END
GO



IF OBJECT_ID('dbo.uspADTDB_RebuildOrReorganizeIndexes', 'P') IS NOT NULL
    DROP PROCEDURE dbo.uspADTDB_RebuildOrReorganizeIndexes;
GO

CREATE PROCEDURE dbo.uspADTDB_RebuildOrReorganizeIndexes
AS
BEGIN
    SET NOCOUNT ON;

 
    DECLARE @MinFragmentationPct            FLOAT         = 10.0;      -- below this = skip
    DECLARE @ReorganizeThreshold            FLOAT         = 30.0;      -- below this = REORGANIZE, >= REBUILD
    DECLARE @MinPageCount                   INT           = 1000;      -- tiny indexes skip karo
    DECLARE @ScanMode                       NVARCHAR(10)  = 'DETAILED'; -- LIMITED | SAMPLED | DETAILED
    DECLARE @WaitAtLowPriorityMaxDuration   TINYINT       = 0;         -- minutes (0 = feature disable)
    DECLARE @MaxDurationMinutes             INT           = 120;       -- 0 = no limit


    DECLARE @OnlineRebuild BIT =
        CASE WHEN CAST(SERVERPROPERTY('EngineEdition') AS INT) IN (3,5,6,8)
             THEN 1 ELSE 0
        END;

    
    DECLARE @RunID     UNIQUEIDENTIFIER = NEWID();
    DECLARE @RunAt     DATETIME2        = SYSDATETIME();


    DECLARE @DbID INT = DB_ID();

    INSERT INTO dbo.ADTDB_IndexMaintenanceLog
        (RunID, RunDateTime, RowID, SchemaName, TableName, IndexName, IndexType,
         PartitionNumber, FragmentationPct, PageCount, PlannedAction)
    SELECT
        @RunID,
        @RunAt,
        ROW_NUMBER() OVER (ORDER BY ips.avg_fragmentation_in_percent DESC),
        s.name,
        o.name,
        i.name,
        i.type_desc,                        -- CLUSTERED / NONCLUSTERED / XML / etc.
        ips.partition_number,
        ips.avg_fragmentation_in_percent,
        ips.page_count,
        CASE
            WHEN ips.avg_fragmentation_in_percent >= @ReorganizeThreshold THEN 'REBUILD'
            ELSE 'REORGANIZE'
        END
    FROM
        sys.dm_db_index_physical_stats(@DbID, NULL, NULL, NULL, @ScanMode) ips
        JOIN sys.indexes  i ON ips.object_id   = i.object_id
                            AND ips.index_id    = i.index_id
        JOIN sys.objects  o ON o.object_id      = i.object_id
        JOIN sys.schemas  s ON s.schema_id      = o.schema_id
    WHERE
        ips.avg_fragmentation_in_percent  >  @MinFragmentationPct
        AND ips.page_count               >= @MinPageCount
        AND i.index_id                   >  0          -- heap skip
        AND i.is_disabled                =  0          -- disabled skip
        AND i.is_hypothetical            =  0          -- hypothetical skip
        AND o.type                       =  'U'        -- user tables only
        AND ips.alloc_unit_type_desc     =  'IN_ROW_DATA';  -- avoid duplicate LOB rows

   
    UPDATE dbo.ADTDB_IndexMaintenanceLog
    SET PlannedAction = 'REBUILD'
    WHERE RunID = @RunID
      AND IndexType IN ('CLUSTERED COLUMNSTORE', 'NONCLUSTERED COLUMNSTORE');

    

    -- REBUILD WITH (...) clause parts
    DECLARE @RebuildOptions     NVARCHAR(500) = 'SORT_IN_TEMPDB = ON';
    DECLARE @Sep                NVARCHAR(5)   = ', ';

    -- ONLINE (auto-detected in STEP 0.5)
    SET @RebuildOptions += @Sep + 'ONLINE = ' + CASE WHEN @OnlineRebuild=1 THEN 'ON' ELSE 'OFF' END;

    -- WAIT_AT_LOW_PRIORITY (only meaningful with ONLINE = ON)
    IF @OnlineRebuild = 1 AND @WaitAtLowPriorityMaxDuration > 0
    BEGIN
        SET @RebuildOptions +=
            '(WAIT_AT_LOW_PRIORITY (MAX_DURATION = '
            + CAST(@WaitAtLowPriorityMaxDuration AS NVARCHAR(5))
            + ' MINUTES, ABORT_AFTER_WAIT = BLOCKERS))';
    END

    DECLARE
        @TotalCount     INT         = (SELECT COUNT(*) FROM dbo.ADTDB_IndexMaintenanceLog WHERE RunID = @RunID),
        @I              INT         = 1,
        @SchName        NVARCHAR(128),
        @TblName        NVARCHAR(128),
        @IdxName        NVARCHAR(128),
        @IdxType        NVARCHAR(60),
        @PartNo         INT,
        @Action         NVARCHAR(20),
        @FragPct        FLOAT,
        @PageCnt        BIGINT,
        @SQL            NVARCHAR(MAX),
        @StatSQL        NVARCHAR(MAX),
        @StartTime      DATETIME2,
        @RunStart       DATETIME2   = SYSDATETIME(),
        @ErrMsg         NVARCHAR(MAX),
        @ProgressMsg    NVARCHAR(500);

    WHILE @I <= @TotalCount
    BEGIN
        -- ── Maintenance window check ──────────────────────────────
        IF @MaxDurationMinutes > 0
           AND DATEDIFF(MINUTE, @RunStart, SYSDATETIME()) >= @MaxDurationMinutes
        BEGIN
            SET @ProgressMsg = FORMATMESSAGE(
                '[WINDOW] Maintenance window of %d min reached. Stopping at index %d of %d.',
                @MaxDurationMinutes, @I, @TotalCount);
            RAISERROR(@ProgressMsg, 10, 1) WITH NOWAIT;

            -- Remaining rows SKIPPED mark karo
            UPDATE dbo.ADTDB_IndexMaintenanceLog
            SET ExecutionStatus = 'SKIPPED',
                SkipReason      = 'Maintenance window exceeded'
            WHERE RunID = @RunID AND RowID >= @I AND ExecutionStatus IS NULL;

            BREAK;
        END

        -- ── Fetch current row ─────────────────────────────────────
        SELECT
            @SchName  = SchemaName,
            @TblName  = TableName,
            @IdxName  = IndexName,
            @IdxType  = IndexType,
            @PartNo   = PartitionNumber,
            @FragPct  = FragmentationPct,
            @PageCnt  = PageCount,
            @Action   = PlannedAction
        FROM dbo.ADTDB_IndexMaintenanceLog
        WHERE RunID = @RunID AND RowID = @I;

        SET @StartTime  = SYSDATETIME();
        SET @ErrMsg     = NULL;
        SET @SQL        = NULL;

        -- ── Live progress message ─────────────────────────────────
        SET @ProgressMsg = FORMATMESSAGE(
            '[%d/%d] %s: %s.%s.%s  Frag=%s%%  Pages=%I64d  Partition=%d',
            @I, @TotalCount, @Action,
            @SchName, @TblName, @IdxName,
            CAST(CAST(@FragPct AS DECIMAL(5,2)) AS VARCHAR(10)), @PageCnt, @PartNo);
        RAISERROR(@ProgressMsg, 10, 1) WITH NOWAIT;

        -- ── Build SQL ─────────────────────────────────────────────

        IF @Action = 'REBUILD'
        BEGIN
         
            IF @IdxType IN ('CLUSTERED COLUMNSTORE', 'NONCLUSTERED COLUMNSTORE')
            BEGIN
                SET @SQL =
                    N'ALTER INDEX ' + QUOTENAME(@IdxName)
                  + N' ON '        + QUOTENAME(@SchName) + N'.' + QUOTENAME(@TblName)
                  + N' REBUILD;';
            END
            ELSE
            BEGIN
               
                SET @SQL =
                    N'ALTER INDEX ' + QUOTENAME(@IdxName)
                  + N' ON '        + QUOTENAME(@SchName) + N'.' + QUOTENAME(@TblName)
                  + N' REBUILD'
                  + CASE WHEN @PartNo > 1
                         THEN N' PARTITION = ' + CAST(@PartNo AS NVARCHAR(10))
                         ELSE N''
                    END
                  + CASE WHEN LEN(@RebuildOptions) > 0
                         THEN N' WITH (' + @RebuildOptions + N')'
                         ELSE N''
                    END
                  + N';';
            END
        END
        ELSE -- REORGANIZE
        BEGIN
          
            IF @IdxType IN ('CLUSTERED COLUMNSTORE', 'NONCLUSTERED COLUMNSTORE')
            BEGIN
                SET @SQL =
                    N'ALTER INDEX ' + QUOTENAME(@IdxName)
                  + N' ON '        + QUOTENAME(@SchName) + N'.' + QUOTENAME(@TblName)
                  + N' REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);';
            END
            ELSE
            BEGIN
                SET @SQL =
                    N'ALTER INDEX ' + QUOTENAME(@IdxName)
                  + N' ON '        + QUOTENAME(@SchName) + N'.' + QUOTENAME(@TblName)
                  + N' REORGANIZE'
                  + CASE WHEN @PartNo > 1
                         THEN N' PARTITION = ' + CAST(@PartNo AS NVARCHAR(10))
                         ELSE N''
                    END
                  + N';';
            END
        END

        -- ── Execute ───────────────────────────────────────────────
        BEGIN TRY
            EXEC sp_executesql @SQL;

            
            IF @Action = 'REORGANIZE'
                AND @IdxType NOT IN ('CLUSTERED COLUMNSTORE', 'NONCLUSTERED COLUMNSTORE')
            BEGIN
                SET @StatSQL =
                    N'UPDATE STATISTICS '
                  + QUOTENAME(@SchName) + N'.' + QUOTENAME(@TblName)
                  + N' ' + QUOTENAME(@IdxName) + N';';
                EXEC sp_executesql @StatSQL;
            END

            UPDATE dbo.ADTDB_IndexMaintenanceLog
            SET ExecutionStatus = 'SUCCESS',
                ExecutedSQL     = @SQL,
                DurationMS      = DATEDIFF(MILLISECOND, @StartTime, SYSDATETIME())
            WHERE RunID = @RunID AND RowID = @I;

        END TRY
        BEGIN CATCH
            SET @ErrMsg = ERROR_MESSAGE();

            UPDATE dbo.ADTDB_IndexMaintenanceLog
            SET ExecutionStatus = 'FAILED',
                ExecutedSQL     = @SQL,
                ErrorMessage    = @ErrMsg,
                DurationMS      = DATEDIFF(MILLISECOND, @StartTime, SYSDATETIME())
            WHERE RunID = @RunID AND RowID = @I;

            RAISERROR('[FAILED] %s on %s.%s: %s', 10, 1,
                      @IdxName, @SchName, @TblName, @ErrMsg) WITH NOWAIT;
        END CATCH

        SET @I = @I + 1;
    END -- WHILE

   
    RAISERROR('--- Run Complete ---', 10, 1) WITH NOWAIT;
    
    SELECT
        RowID,
        SchemaName,
        TableName,
        IndexName,
        IndexType,
        PartitionNumber,
        CAST(FragmentationPct AS DECIMAL(5,2))  AS [Frag%],
        PageCount,
        PlannedAction                           AS [Action],
        ExecutionStatus                         AS [Status],
        DurationMS,
        SkipReason,
        ErrorMessage
    FROM dbo.ADTDB_IndexMaintenanceLog
    WHERE RunID = @RunID
    ORDER BY
        CASE ExecutionStatus WHEN 'FAILED' THEN 0 ELSE 1 END,
        FragmentationPct DESC;

END;
GO


EXEC dbo.uspADTDB_RebuildOrReorganizeIndexes;

How it works:

The script checks the fragmentation level of each index.
If fragmentation is above 30%, it rebuilds the index.
If fragmentation is between 10% and 30%, it reorganizes the index.
This process can be scheduled as a SQL Server Agent job to run during off-peak hours, ensuring regular maintenance.

When to Rebuild vs. Reorganize?

Rebuild: Use when fragmentation is above 30%. This is a more resource-intensive operation but provides the best performance improvement.
Reorganize: Use when fragmentation is between 10% and 30%. It is less disruptive and can be run while the database is in use.

For very large databases, consider rebuilding indexes online to avoid downtime. However, note that online index rebuilds require SQL Server Enterprise Edition or higher.

Results for "Reorganization"

Index Rebuilding vs. Reorganization in SQL Server