In this post I’m sharing more scripts to help with SQL Server performance tuning. This time, we’re looking at index and heap fragmentation which are useful for pinpointing inefficiencies on your database.
Fragmentation, whether in indexes or heaps, can lead to slower queries, inefficient storage, and increased maintenance times. Rebuilding fragmented indexes, converting heaps to clustered indexes, and maintaining good statistics can improve overall SQL Server health and performance.
The following scripts and information is included here:
> Script 1: Get Index Fragmentation Levels
> Script 2: Identifying Heap Fragmentation
> Addressing Fragmentation & Stale Stats
> More Performance Troubleshooting Tips
Script 1: Get Index Fragmentation Levels
This script helps retrieves key information about your SQL Server indexes and their fragmentation levels:
Script 2: Identifying Heap Fragmentation
Heaps, or tables without clustered indexes, are prone to fragmentation. Fragmentation in heaps can impact query performance, and potentially increase the time it takes for full backups to complete.
Use this script to identify fragmented heaps:
The ForwardRecordCount column is of particular interest, if you see a huge number on a heap then perhaps it’s the reason queries are running slow. If it’s a small table, I wouldn’t worry about this much.
Addressing Fragmentation & Stale Stats
After running the scripts above and identifying fragmentation, here’s how to address these issues:
Maintain Optimal Statistics
We don’t need to go too granular in explaining how to check if your statistics are good. We just need to make sure we keep them up to date, which can be done by having a Maintenance Plan for Updating Statistics.
For more information on updating stats, checkout this MS Docs Page: Update Statistics Task (Maintenance Plan). Stale statistics can seriously hurt query performance, be sure to refresh them periodically.
Index Maintenance
We should rebuild our indexes on tables to reduce fragmentation by using the ALTER INDEX REBUILD command. If on SQL Server Enterprise Edition you can perform this without impacting table availability using one of the available options. For indexes with lower fragmentation levels, consider using the ALTER INDEX REORGANIZE command. This is a more lightweight operation than rebuilding indexes but still helps to improve performance.
It’s a good idea to set up maintenance plans for addressing fragmentation, especially on large tables, and schedule them during off-peak hours.
For more information, these Microsoft Documentation Pages will help:
> Index Rebuild vs Reorganize
> ALTER INDEX Docs
Rebuild or Avoid Heaps
Rebuilding a heap can help to eliminate fragmentation and optimize the table’s data storage.
You can convert a heap to a clustered index to resolve fragmentation and enhance sequential data access:
-- Create clustered index on a table with no clustered index (heap) CLUSTERED INDEX IX_ClusteredIndexName ON TableName(ColumnName);
More Performance Troubleshooting Tips
To provide you with some general follow-up material, I’ll recommend my other post, SQL Server Performance Troubleshooting Scripts. It covers identifying SQL worker thread issues, monitoring running queries, and optimizing stored procedures. It also contains links to check the health of High Availability (HA) environments, like Always On, replication, and Mirroring.
Feel free to leave a comment if there’s something I’ve missed or if you have any suggestions for improvement. Ty!
Leave a Reply