We do create indexes to improve the search performance of our queries. But over a period of time, because of DML operations (inserts/deletes/updates), the data will get spread across the disc (Physical order of pages are NOT in sequence). This is called fragmentation. Watch this video by @A_Bansal to understand index fragmentation & page splits.
Fragmented indexes will primarily slow down ‘search’ performance. We need to identify all the indexes that are fragmented and defrag them to achieve better performance. This should be part of our SQL Server Maintenance jobs. So, how to identify the fragmentation of all indexes in a database? Here is the query.
[Object_ID], avg_fragmentation_in_percent AS Fragmentation
sys.dm_db_index_physical_stats is a system DMF that accepts 5 parameters and returns fragmentation and size information of indexes specified on a table. You can learn more about the said DMF from Microsoft Docs.
To get meaningful information that helps us to identify all the index names and their respective fragmentation percentages we may need to join the above DMF with sys.indexes. Here is the script for that.
SELECT object_name(ips.object_id) AS TableName,
ips.index_id, name AS IndexName, avg_fragmentation_in_percent,db_name(ips.database_id) AS DatabaseName
, NULL) AS ips
INNER JOIN sys.indexes AS SI
ON ips.object_id = SI.object_id
AND ips.index_id = SI.index_id
WHERE ips.avg_fragmentation_in_percent > 5
AND SI.index_id <> 0
According to Microsoft guidelines, indexes that are having fragmentation less than 5% or indexes that are having less than 1000 pages can be ignored. That is the reason why we have ‘ips.avg_fragmentation_in_percent > 5’ filter in our query. ‘SI.index_id <> 0’ condition is to filter out heaps from the results, as our primary concentration is to defrag indexes.
So now, we have identified all the fragmented indexes, how to defrag them? That is our next blog 😊.