Database maintenance SQL

Index Rebuilding vs. Reorganization in SQL Server

As a database grows, maintaining optimal performance becomes crucial. One of the key contributors to performance degradation over time is index fragmentation...

Mar 19, 2026 12 min read

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.usp_RebuildOrReorganizeIndexes', 'P') IS NOT NULL
    DROP PROCEDURE dbo.usp_RebuildOrReorganizeIndexes;
GO

CREATE PROCEDURE dbo.usp_RebuildOrReorganizeIndexes
    -- ── Scope filters ──────────────────────────────────────────────
    @SchemaName                     NVARCHAR(128)   = NULL,   -- NULL = all schemas
    @TableName                      NVARCHAR(128)   = NULL,   -- NULL = all tables
    @IndexName                      NVARCHAR(128)   = NULL,   -- NULL = all indexes

    -- ── Fragmentation thresholds ───────────────────────────────────
    @MinFragmentationPct            FLOAT           = 10.0,   -- below this = skip
    @ReorganizeThreshold            FLOAT           = 30.0,   -- below this = REORGANIZE, >= REBUILD
    @MinPageCount                   INT             = 1000,   -- tiny indexes skip karo

    -- ── Scan quality ───────────────────────────────────────────────
    -- LIMITED  = fast, header-level (default, production-safe)
    -- SAMPLED  = 1% row sample, more accurate
    -- DETAILED = full scan, most accurate but slowest
    @ScanMode                       NVARCHAR(10)    = 'LIMITED',

    -- ── Rebuild options ────────────────────────────────────────────
    @OnlineRebuild                  BIT             = 0,      -- 1 = ONLINE (Enterprise only)
    @SortInTempDB                   BIT             = 1,      -- tempdb sort buffer use karo
    @FillFactor                     TINYINT         = 0,      -- 0 = existing fill factor use karo
    @MaxDOP                         TINYINT         = 0,      -- 0 = server default, 1 = serial
    @DataCompression                NVARCHAR(10)    = NULL,   -- NULL=no change | PAGE | ROW | NONE

    -- ── Online rebuild blocking guard ─────────────────────────────
    -- Jab ONLINE=ON ho aur koi blocker ho, kitne minute WAIT karo
    -- phir ABORT_AFTER_WAIT = BLOCKERS karke unhe kill karo
    -- 0 = feature disable karo
    @WaitAtLowPriorityMaxDuration   TINYINT         = 0,      -- minutes (0 = skip this option)

    -- ── Statistics ────────────────────────────────────────────────
    @UpdateStats                    BIT             = 1,      -- REORGANIZE ke baad stats update

    -- ── Maintenance window guard ──────────────────────────────────
    -- SP khud end time track karta hai; window cross hone par gracefully exit
    @MaxDurationMinutes             INT             = 120,    -- 0 = no limit

    -- ── Dry run ────────────────────────────────────────────────────
    @DryRun                         BIT             = 0       -- 1 = sirf plan print karo
AS
BEGIN
    SET NOCOUNT ON;

    -- ============================================================
    -- STEP 0 : Parameter validation
    -- ============================================================
    IF @ScanMode NOT IN ('LIMITED', 'SAMPLED', 'DETAILED')
    BEGIN
        RAISERROR('@ScanMode must be LIMITED, SAMPLED, or DETAILED.', 16, 1);
        RETURN;
    END

    IF @DataCompression IS NOT NULL AND @DataCompression NOT IN ('PAGE','ROW','NONE')
    BEGIN
        RAISERROR('@DataCompression must be PAGE, ROW, NONE, or NULL.', 16, 1);
        RETURN;
    END

    IF @FillFactor > 100
    BEGIN
        RAISERROR('@FillFactor must be 0–100. (0 = keep existing)', 16, 1);
        RETURN;
    END

    -- ============================================================
    -- STEP 1 : Session-level temp log (no permanent table)
    --          Yahi is run ka audit trail hai
    -- ============================================================
    IF OBJECT_ID('tempdb..#IndexRunLog') IS NOT NULL
        DROP TABLE #IndexRunLog;

    CREATE TABLE #IndexRunLog (
        RowID               INT             NOT NULL,
        SchemaName          NVARCHAR(128)   NOT NULL,
        TableName           NVARCHAR(128)   NOT NULL,
        IndexName           NVARCHAR(128)   NOT NULL,
        IndexType           NVARCHAR(60)    NOT NULL,  -- CLUSTERED, NONCLUSTERED, COLUMNSTORE, etc.
        PartitionNumber     INT             NOT NULL,
        FragmentationPct    FLOAT           NOT NULL,
        PageCount           BIGINT          NOT NULL,
        PlannedAction       NVARCHAR(20)    NOT NULL,  -- REBUILD / REORGANIZE
        ExecutedSQL         NVARCHAR(MAX)   NULL,
        ExecutionStatus     NVARCHAR(20)    NULL,      -- SUCCESS / FAILED / SKIPPED / DRYRUN
        ErrorMessage        NVARCHAR(MAX)   NULL,
        DurationMS          INT             NULL,
        SkipReason          NVARCHAR(200)   NULL
    );

    -- ============================================================
    -- STEP 2 : Fragmented index list — partition-aware
    -- ============================================================
    DECLARE @DbID INT = DB_ID();

    INSERT INTO #IndexRunLog
        (RowID, SchemaName, TableName, IndexName, IndexType,
         PartitionNumber, FragmentationPct, PageCount, PlannedAction)
    SELECT
        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
        AND (NULLIF(@SchemaName, '') IS NULL OR s.name = @SchemaName)
        AND (NULLIF(@TableName,  '') IS NULL OR o.name = @TableName)
        AND (NULLIF(@IndexName,  '') IS NULL OR i.name = @IndexName);

    -- ============================================================
    -- STEP 3 : Columnstore indexes — force REBUILD
    --          (REORGANIZE on columnstore = delta store flush only,
    --           fragmentation metric alag hoti hai — DBA trap!)
    -- ============================================================
    UPDATE #IndexRunLog
    SET PlannedAction = 'REBUILD'
    WHERE IndexType IN ('CLUSTERED COLUMNSTORE', 'NONCLUSTERED COLUMNSTORE');

    -- ============================================================
    -- STEP 4 : Dry Run — sirf plan dikhao
    -- ============================================================
    IF @DryRun = 1
    BEGIN
        RAISERROR('--- DRY RUN MODE: No changes will be made ---', 10, 1) WITH NOWAIT;

        SELECT
            RowID,
            SchemaName,
            TableName,
            IndexName,
            IndexType,
            PartitionNumber,
            CAST(FragmentationPct AS DECIMAL(5,2))  AS [Frag%],
            PageCount,
            PlannedAction,
            'DRYRUN'                                AS WillExecute
        FROM #IndexRunLog
        ORDER BY FragmentationPct DESC;

        RETURN;
    END

    -- ============================================================
    -- STEP 5 : Build option clauses (reusable strings)
    -- ============================================================

    -- REBUILD WITH (...) clause parts
    DECLARE @RebuildOptions     NVARCHAR(500) = '';
    DECLARE @Sep                NVARCHAR(5)   = '';  -- first option ke pehle koi comma nahi

    -- SORT_IN_TEMPDB
    SET @RebuildOptions += @Sep + 'SORT_IN_TEMPDB = ' + CASE WHEN @SortInTempDB=1 THEN 'ON' ELSE 'OFF' END;
    SET @Sep = ', ';

    -- ONLINE
    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

    -- MAXDOP
    IF @MaxDOP > 0
        SET @RebuildOptions += @Sep + 'MAXDOP = ' + CAST(@MaxDOP AS NVARCHAR(3));

    -- FILLFACTOR
    IF @FillFactor > 0
        SET @RebuildOptions += @Sep + 'FILLFACTOR = ' + CAST(@FillFactor AS NVARCHAR(3));

    -- DATA_COMPRESSION
    IF @DataCompression IS NOT NULL
        SET @RebuildOptions += @Sep + 'DATA_COMPRESSION = ' + @DataCompression;

    -- ============================================================
    -- STEP 6 : Process loop — with maintenance window guard
    -- ============================================================
    DECLARE
        @TotalCount     INT         = (SELECT COUNT(*) FROM #IndexRunLog),
        @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 #IndexRunLog
            SET ExecutionStatus = 'SKIPPED',
                SkipReason      = 'Maintenance window exceeded'
            WHERE 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 #IndexRunLog
        WHERE 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
            -- Columnstore: no WITH options needed (they're silently ignored anyway,
            -- but keeping it clean for DBA readability)
            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
                -- Partition-aware: agar 1 se zyada partition hai to PARTITION = N karo
                -- (ips mein ek row per partition hoti hai)
                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
            -- Columnstore REORGANIZE = delta store flush (different intent)
            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;

            -- REORGANIZE ke baad statistics update (REBUILD auto-updates)
            IF @Action = 'REORGANIZE' AND @UpdateStats = 1
                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 #IndexRunLog
            SET ExecutionStatus = 'SUCCESS',
                ExecutedSQL     = @SQL,
                DurationMS      = DATEDIFF(MILLISECOND, @StartTime, SYSDATETIME())
            WHERE RowID = @I;

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

            UPDATE #IndexRunLog
            SET ExecutionStatus = 'FAILED',
                ExecutedSQL     = @SQL,
                ErrorMessage    = @ErrMsg,
                DurationMS      = DATEDIFF(MILLISECOND, @StartTime, SYSDATETIME())
            WHERE 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

    -- ============================================================
    -- STEP 7 : Final summary (from session temp table)
    -- ============================================================
    RAISERROR('--- Run Complete ---', 10, 1) WITH NOWAIT;

    -- 7a. Aggregate summary
    SELECT
        PlannedAction                       AS [Action],
        ExecutionStatus                     AS [Status],
        COUNT(*)                            AS [IndexCount],
        SUM(PageCount)                      AS [TotalPages],
        MIN(CAST(FragmentationPct AS DECIMAL(5,2))) AS [MinFrag%],
        MAX(CAST(FragmentationPct AS DECIMAL(5,2))) AS [MaxFrag%],
        AVG(DurationMS)                     AS [AvgDurationMS],
        SUM(DurationMS)                     AS [TotalDurationMS]
    FROM #IndexRunLog
    GROUP BY PlannedAction, ExecutionStatus
    ORDER BY PlannedAction, ExecutionStatus;

    -- 7b. Detailed log (FAILED first, then rest by frag%)
    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 #IndexRunLog
    ORDER BY
        CASE ExecutionStatus WHEN 'FAILED' THEN 0 ELSE 1 END,
        FragmentationPct DESC;

END;
GO


-- ============================================================
--  USAGE EXAMPLES
-- ============================================================

-- 1. Dry Run — plan dekho, kuch execute nahi hoga
EXEC dbo.usp_RebuildOrReorganizeIndexes
    @DryRun = 1;

-- 2. Normal run — default thresholds, SAMPLED scan
EXEC dbo.usp_RebuildOrReorganizeIndexes
    @ScanMode = 'SAMPLED';

-- 3. Specific table, serial rebuild (low-priority server)
EXEC dbo.usp_RebuildOrReorganizeIndexes
    @TableName  = 'PAY_SalaryDetails',
    @MaxDOP     = 1;

-- 4. Enterprise: ONLINE rebuild, 60-min window, PAGE compression
EXEC dbo.usp_RebuildOrReorganizeIndexes
    @OnlineRebuild                  = 1,
    @WaitAtLowPriorityMaxDuration   = 2,    -- 2 min wait, phir blockers kill
    @MaxDurationMinutes             = 60,
    @DataCompression                = 'PAGE',
    @FillFactor                     = 80,
    @MaxDOP                         = 4;

-- 5. Specific schema, aggressive thresholds
EXEC dbo.usp_RebuildOrReorganizeIndexes
    @SchemaName             = 'dbo',
    @MinFragmentationPct    = 5,
    @ReorganizeThreshold    = 15,
    @MinPageCount           = 500,
    @ScanMode               = 'DETAILED';

-- 6. Sirf specific index
EXEC dbo.usp_RebuildOrReorganizeIndexes
    @TableName  = 'ATT_DailyRecord',
    @IndexName  = 'IX_ATT_DailyRecord_EmpDate';

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.