Index Fragmentation Report in Microsoft SQL Server

This content 8 years old. Please, read this page keeping its age in mind along with the fact technology changes fast and the information on this page me be outdated, not best practice, or plain wrong.

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

Speak Your Mind

*

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