SQL Tips #24 - Listing All Indexes and Their Fragmentation Percentage

Fragmented indexes slow queries down - this query lists every index with its fragmentation percentage, worst first.

The Query

SELECT  OBJECT_NAME(IDX.OBJECT_ID) AS Table_Name, 
    IDX.name AS Index_Name, 
    IDXPS.index_type_desc AS Index_Type, 
    IDXPS.avg_fragmentation_in_percent  Fragmentation_Percentage
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) IDXPS 
INNER JOIN sys.indexes IDX  ON IDX.object_id = IDXPS.object_id 
    AND IDX.index_id = IDXPS.index_id 
ORDER BY Fragmentation_Percentage DESC

Disclaimer

These queries are provided as a guide, and are by no means perfect. I didn't write all of these - many are collated from MSDN documentation and community sources over the years.

Tags