[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 😊.
PS: The above script is for row-store indexes.
See you soon with another script.
This post is part of The Scripts Garage. You can find all scripts at The Scripts Garage.