You are here:Home » tsql » Index Fragmentation Details

Index Fragmentation Details

SELECT db_name(ps.database_id), object_name(ps.OBJECT_ID),
b.name, ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
AND ps.avg_fragmentation_in_percent > 30
ORDER BY ps.OBJECT_ID;