How to rebuild all indexes in MS SQL server database

Discover how to optimize your MS SQL Server Database by rebuilding all indexes. Dive into our comprehensive guide and improve database performance today (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 performance.

In this guide, we'll dive into rebuilding MSSQL indexes to ensure optimal database efficiency.

Why is Rebuilding Database Indexes Essential?

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

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

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 SQL code to identify these indexes combines data from sys.dm_db_index_physical_stats, sys.indexes, and sys.objects.

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

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

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

For instance, using the Umbraco 7 database, the report might display the 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 provided SQL code has been simplified for readability. The complete code, including error handling and reporting, can be found further below.

Rebuilding indexes code outline

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

Complete working TSQL Code for Comprehensive Index Management

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; -- Uncomment this line if you want to run the command

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

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

After the Indexes Rebuild

Post the rebuilding operation, the SQL Server engine automatically refreshes the selected indexes.

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

For maintaining optimal performance, consider updating the database statistics using:

exec sp_updatestats

Deep Dive and Additional Resources

For those wishing to understand the intricacies of index rebuilding and reorganizing, the official Microsoft Documentation is a treasure trove of information.

Access the complete source code for this post on GitHub.

In Conclusion: Maximizing Database Efficiency

Rebuilding database indexes is akin to a routine health checkup for your database.

By addressing fragmentation proactively, you ensure optimal performance and swift application responses.

Remember, a well-maintained database is pivotal for seamless application operations.

📢 Interested in speeding up your database? Reach out to us!

And while you're here, don't forget to explore more insightful articles on our blog.

↑ Top ↑