Jump to Section
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
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
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;
ENDKey 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
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_updatestatsDeep 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.