How to rebuild all indexes in MS SQL server database

Discover how to optimize your database indexes without extra tools. Learn how to identify indexes to heal based on avg_fragmentation and how to refresh the database statistics (T-SQL script included!)

Understanding the need to rebuild Indexes in MS SQL Server#

Database indexes, much like the index in a book, allow for quicker data retrieval.

However, over time, these indexes can become fragmented, leading to decreased database performance.

Does rebuilding indexes improve performance?#

Fragmented indexes can slow down query performance due to the increased I/O operations needed to locate the data the index points to.

As the fragmentation increases, scan operations become particularly affected, leading to slower application responses.

Heavily fragmented indexes can degrade query performance because additional I/O is required to locate data to which the index points. More I/O causes your application to respond slowly, especially when scan operations are involved

Microsoft docs
Visual representation of Microsoft's best practices for index rebuilding and fragmentation management

Visual representation of Microsoft's best practices for index rebuilding and fragmentation management

Identifying indexes in need of rebuilding#

Before we delve into the actual SQL code, it's crucial to know which indexes need rebuilding.

This determination is based on the avg_fragmentation_in_percent values from Microsoft’s recommendations.

Generally, indexes with fragmentation over 30% and those named with 'IX' or 'PK' are prime candidates.

The below SQL code to identify these indexes combines data from sys.dm_db_index_physical_stats, sys.indexes, and sys.objects:

SELECT i.[name],
s.[index_type_desc], --s.[index_type_desc]
o.[name],
s.[avg_fragmentation_in_percent],
o.type_desc
FROM sys.dm_db_index_physical_stats (DB_ID(@DatabaseName), NULL, NULL, NULL, NULL) AS s
INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
INNER JOIN sys.objects AS o ON i.object_id = o.object_id
WHERE (s.avg_fragmentation_in_percent > 30 and (i.[Name] like '%IX%' OR i.[Name] like '%PK%'))

Launching the report first#

After executing the above SQL code, you'll see a detailed report.

The report displays key data for investigation: IndexName, IndexType, TableName, AvgFragmentationInPercent, and ObjectTypeDescription.

SQ report of indexes to heal - for the Umbraco 7 database

SQ report of indexes to heal - for the Umbraco 7 database

Note: The complete code, including error handling and reporting, can be found further below.

T-SQL Code for Rebuilding indexes#

DECLARE @DatabaseName NVARCHAR(MAX) = '[database]'
DECLARE @CurrentSchemaName NVARCHAR(MAX)
DECLARE @CurrentIndexName NVARCHAR(MAX)
DECLARE @CurrentTableName NVARCHAR(MAX)
DECLARE @CmdRebuidIndex NVARCHAR(MAX)

DECLARE @tempIndexTable TABLE
(
    RowID                       int not null primary key identity(1,1),   
    IndexName                   NVARCHAR(MAX),
    IndexType                   NVARCHAR(MAX),
    TableName                   NVARCHAR(MAX),
    SchemaName                  NVARCHAR(MAX),
    AvgFragmentationInPercent   FLOAT,
    ObjectTypeDescription       NVARCHAR(MAX)     
)

INSERT INTO @tempIndexTable (IndexName, IndexType, TableName, SchemaName, AvgFragmentationInPercent, ObjectTypeDescription) (
    SELECT
        i.[name],
        s.[index_type_desc], --s.[index_type_desc]
        o.[name],
        sch.name,
        s.[avg_fragmentation_in_percent],
        o.type_desc
    FROM
        sys.dm_db_index_physical_stats (DB_ID(@DatabaseName), NULL, NULL, NULL, NULL)   AS  s   INNER JOIN
        sys.indexes                                                                     AS  i   ON  s.object_id = i.object_id
                                                                                                    AND s.index_id = i.index_id INNER JOIN
        sys.objects                                                                     AS  o   ON  i.object_id = o.object_id   INNER JOIN
        sys.schemas                                                                     AS  sch ON  sch.schema_id = o.schema_id
    WHERE (s.avg_fragmentation_in_percent > 30 and (i.[Name] like '%IX%' OR i.[Name] like '%PK%'))  
)

PRINT 'Indexes to rebuild:'
SELECT * FROM @tempIndexTable;

RETURN; -- Comment or remove this line if you want to run the full process

DECLARE @totalCount INTEGER
SELECT @totalCount = count(1) FROM @tempIndexTable
DECLARE @counter INTEGER = 1

WHILE(@counter <= @totalCount)
BEGIN   

    SET @CurrentIndexName = (SELECT top 1 IndexName FROM @tempIndexTable WHERE RowID = @counter);
    SET @CurrentTableName = (SELECT top 1 TableName FROM @tempIndexTable WHERE RowID = @counter);
    SET @CurrentSchemaName = (SELECT top 1 SchemaName FROM @tempIndexTable WHERE RowID = @counter);
    
    PRINT 'Rebuild starting (' + convert(VARCHAR(5), @counter) + '/' + convert(VARCHAR(5), @totalCount) + ') [' + @CurrentIndexName + 
    '] ON [' + @CurrentSchemaName + '].[' + @CurrentTableName + '] at ' 
    + convert(varchar, getdate(), 121)

    BEGIN TRY
        SET @CmdRebuidIndex = 'ALTER INDEX [' + @CurrentIndexName + '] ON [' + @CurrentSchemaName + '].[' + @CurrentTableName + '] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)'
            EXEC (@CmdRebuidIndex)
            PRINT 'Rebuild executed [' + @CurrentIndexName + '] ON [' + @CurrentSchemaName + '].[' + @CurrentTableName + '] at ' + convert(varchar, getdate(), 121)
    END TRY
    BEGIN CATCH
        PRINT 'Failed to rebuild [' + @CurrentIndexName + '] ON [' + @CurrentSchemaName + '].[' + @CurrentTableName + ']'
        PRINT ERROR_MESSAGE()
    END CATCH

    SET @counter += 1;
END

Key steps:

  • Locate and store the indexes needing rebuilding in a temporary table (@tempIndexTable).
  • Display the chosen indexes and their metrics.
  • Loop through each index, executing the rebuild operation.

Thanks to our reader, Paweł, we've refined the initial script for a more comprehensive index management approach, which also considers the schema name.

Choosing between online and offline index rebuilding#

Rebuilding an index can be done either online or offline, with each method having its own merits:

  • Online Rebuilding: Ensures that the database remains available during the operation but requires more time and disk space.
  • Offline Rebuilding: This is faster but makes the database unavailable for the duration of the operation.

For applications requiring continuous availability, the online option is advisable.

Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online. For more information, see INDEX and Perform Index Operations Online

Microsoft docs

Update stats after the indexes rebuild#

After rebuilding, the SQL Server engine automatically refreshes the selected indexes.

The duration of this operation is directly proportional to the index size.

To maintain optimal performance, consider updating the database statistics.

It's easy, just execute the below command:

exec sp_updatestats

Deep dive and additional resources#

If you wish to understand the intricacies of index rebuilding and reorganizing, the official Microsoft Documentation is a treasure trove of information.

You can access the complete source code for this post on GitHub.

↑ Top ↑