This blog post first appeared on SQLMaestros
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.
SELECT database_id,[Object_ID], avg_fragmentation_in_percent AS Fragmentation FROM sys.dm_db_index_physical_stats (Db_id(DB_NAME()) , NULL , NULL , NULL , NULL)
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 FROM sys.dm_db_index_physical_stats (Db_id(DB_NAME()) , NULL , NULL , NULL , 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 GO
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.