SQL Server – Check Index Fragmentation on ALL Indexes in a Database

Often, when I am using a development or test environment and I run into a situation where a query is behaving slower than normal, first thing I want to rule out is “Do I have any fragmented indexes?”. Here is a simple query that will list every index on every table in your database, ordered by percentage of index fragmentation. This query will work on SQL2K5 or newer.

SELECT dbschemas.[name] as 'Schema', 
	dbtables.[name] as 'Table', 
	dbindexes.[name] as 'Index',
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
	AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

The output will look like:

Schema   Table            Index                     alloc_unit_type_desc  avg_fragmentation_in_percent  page_count
History	 TrackingHistory  PK_TrackingHistory_trackingId IN_ROW_DATA	          97.5308641975309              81
History	 ProductHistory   PK_ProductHistory_productId   IN_ROW_DATA	          96.4508641975309              87
Account	 Customer         UC_Customer_email             IN_ROW_DATA	          95.5103734439834              241

That should do the trick!