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. 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:

1. Internal Fragmentation: Empty space within pages due to updates or deletes.
2. 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('usp_RebuildOrReorganizeIndexes','P') IS NOT NULL
BEGIN
	DROP PROC usp_RebuildOrReorganizeIndexes
END

GO

CREATE PROCEDURE dbo.usp_RebuildOrReorganizeIndexes
    @TableName NVARCHAR(128) = NULL,
    @DatabaseName NVARCHAR(128) = NULL,
    @IndexName NVARCHAR(128) = NULL,
    @MinFragmentationPercent FLOAT = 10
AS
BEGIN
    -- Declare a table variable to store index details
    DECLARE @IndexDetails TABLE (
        RowID INT IDENTITY(1,1),
        SchemaName NVARCHAR(128),
        TableName NVARCHAR(128),
        IndexName NVARCHAR(128),
        AvgFragmentationInPercent FLOAT
    );

    -- Declare a variable to store the database ID
    DECLARE @DbID INT = DB_ID(ISNULL(@DatabaseName, DB_NAME()));

    -- Insert index details into the table variable
    INSERT INTO @IndexDetails (SchemaName, TableName, IndexName, AvgFragmentationInPercent)
    SELECT 
        s.name AS SchemaName,
        o.name AS TableName,
        i.name AS IndexName,
        ips.avg_fragmentation_in_percent
    FROM 
        sys.dm_db_index_physical_stats(@DbID, NULL, NULL, NULL, 'LIMITED') 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 > @MinFragmentationPercent
        AND o.type = 'U' -- Only user tables
        AND (ISNULL(@TableName, '') = '' OR o.name = @TableName)
        AND (ISNULL(@IndexName, '') = '' OR i.name = @IndexName);

    -- Get the total number of rows (indexes) to process
    DECLARE @TableCount INT = (SELECT COUNT(*) FROM @IndexDetails);

    -- Declare variables for processing
    DECLARE @I INT = 1;
    DECLARE @SchemaName NVARCHAR(128)
    DECLARE @SQL NVARCHAR(MAX) = '';

    -- Process each index one by one
    WHILE @I <= @TableCount
    BEGIN
        -- Fetch the next index to process
        SELECT 
            @SchemaName = SchemaName,
            @TableName = TableName,
            @IndexName = IndexName
        FROM @IndexDetails
        WHERE RowID = @I;

        -- Build the appropriate SQL command based on fragmentation level
        IF @MinFragmentationPercent > 30 
        BEGIN
            SET @SQL = 'ALTER INDEX  ON . REBUILD;';
        END
        ELSE
        BEGIN
            SET @SQL = 'ALTER INDEX  ON . REORGANIZE;';
        END

		print(@SQL)
        -- Execute the SQL command
        EXEC sp_executesql @SQL;

        -- Move to the next index
        SET @I = @I + 1;
    END
END;
GO

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.