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
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:
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.
Note: The complete code, including error handling and reporting, can be found further below.
T-SQL Code for Rebuilding indexes#
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
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:
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.