Index Fragmentation Report in Microsoft SQL Server

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.