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 to retrieve index fragmentation levels in SQL Server | |
| -- This script will list fragmented indexes in the database, showing table name, index name, fragmentation percentage, and more | |
| SELECT | |
| OBJECT_NAME(s.OBJECT_ID) AS TableName, -- Table name where the index is located | |
| i.name AS IndexName, -- Name of the index | |
| ROUND(s.avg_fragmentation_in_percent, 2) AS FragmentationPercentage, -- Fragmentation percentage rounded to 2 decimal places | |
| s.page_count AS PageCount, -- Number of pages used by the index | |
| i.type_desc AS IndexType, -- Type of the index (Clustered, Nonclustered, etc.) | |
| s.stats_id AS StatsID -- Statistics ID for the index | |
| FROM | |
| sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') AS s | |
| JOIN | |
| sys.indexes AS i | |
| ON | |
| s.OBJECT_ID = i.OBJECT_ID AND s.index_id = i.index_id | |
| WHERE | |
| i.type_desc IN ('CLUSTERED', 'NONCLUSTERED') | |
| ORDER BY | |
| s.avg_fragmentation_in_percent DESC; |
by 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:
| -- show fragmentation for heaps in database | |
| SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; | |
| GO | |
| IF OBJECT_ID('tempdb..#HeapDetails') IS NOT NULL | |
| DROP TABLE #HeapDetails; | |
| CREATE TABLE #HeapDetails ( | |
| ObjectName SYSNAME, | |
| PageCount INT, | |
| AvgPageSpaceUsedPercent FLOAT, | |
| RecordCount INT, | |
| ForwardedRecordCount INT | |
| ); | |
| DECLARE HeapCursor CURSOR FOR | |
| SELECT object_id | |
| FROM sys.indexes | |
| WHERE index_id = 0; | |
| DECLARE @ObjectID INT; | |
| OPEN HeapCursor; | |
| FETCH NEXT FROM HeapCursor INTO @ObjectID; | |
| WHILE @@FETCH_STATUS = 0 | |
| BEGIN | |
| INSERT INTO #HeapDetails (ObjectName, PageCount, AvgPageSpaceUsedPercent, RecordCount, ForwardedRecordCount) | |
| SELECT | |
| OBJECT_NAME(@ObjectID), | |
| page_count, | |
| avg_page_space_used_in_percent, | |
| record_count, | |
| forwarded_record_count | |
| FROM sys.dm_db_index_physical_stats ( | |
| DB_ID(), | |
| @ObjectID, | |
| 0, | |
| NULL, | |
| 'DETAILED' | |
| ); | |
| FETCH NEXT FROM HeapCursor INTO @ObjectID; | |
| END | |
| CLOSE HeapCursor; | |
| DEALLOCATE HeapCursor; | |
| SELECT * | |
| FROM #HeapDetails | |
| WHERE ForwardedRecordCount > 0 | |
| ORDER BY ObjectName; | |
| DROP TABLE #HeapDetails; |
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