Table of contents
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
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
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
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
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;
ENDThanks 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_updatestatsDeep 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.