SQL Fragmented Index Query

I put together this query on SQL Server 2008 to help me organize the fragmented indexes and thus get me pointed in the right direction for troubleshooting.

SELECT dbps.database_id, dbps.OBJECT_ID, dbps.index_id, sysi.name,
dbps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS dbps
INNER JOIN sys.indexes AS sysi ON dbps.OBJECT_ID = sysi.OBJECT_ID
AND dbps.index_id = sysi.index_id
WHERE dbps.database_id = DB_ID()
AND dbps.index_id > 0
ORDER BY dbps.avg_fragmentation_in_percent DESC
GO

Many of our systems automatically defrag in overnight tasks, but when things stop working, this helps me get systems back up and running.

Published by

Leroy Leese

Leroy is a Zend PHP Certified Engineer from Knoxville TN. He has been computing for over two decades, drag-racing for 16 years and spent a year with a band as a guitarist.