I came across something that I found useful today when troubleshooting some query performance reports.
I won’t re-post the entire article, but here’s what I found useful and I tweaked it to my needs by ordering by avg_fragmentation_in_percent as well as only displaying results with IPS.page_counts greater than 1000 and avg_fragmentation_in_percent greater than 75%.
--To Find out fragmentation level of a given database --This query will give DETAILED information --CAUTION : It may take very long time, depending on the number of tables in the DB USE foodb GO SELECT object_name(IPS.object_id) AS [TableName], SI.name AS [IndexName], IPS.Index_type_desc, IPS.avg_fragmentation_in_percent, IPS.avg_fragment_size_in_pages, IPS.avg_page_space_used_in_percent, IPS.record_count, IPS.ghost_record_count, IPS.fragment_count, IPS.avg_fragment_size_in_pages, IPS.page_count FROM sys.dm_db_index_physical_stats(db_id(N'foodb'), NULL, NULL, NULL , 'DETAILED') IPS JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id WHERE ST.is_ms_shipped = 0 and IPS.page_count > 1000 and avg_fragmentation_in_percent > 75 ORDER BY avg_fragmentation_in_percent desc GO
