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)

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
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


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.