Script to Check Index Fragmentation in SQL Server

  • This may take some time to get executed if the database size is large
  • You may filter it for some specific cases to run it faster
  • sys.indexes, sys.objects and sys.dm_db_index_physical_stats()
  • Mode can be “Sampled”, “Limited” or “Detailed”….Limited is the fastest one
  • If you run this query on secondary replica of Always ON groupp, you might encountera blocking issue
SELECT DB_NAME(ps.database_id) AS [Database Name], SCHEMA_NAME(o.[schema_id]) AS [Schema Name]
	, OBJECT_NAME(ps.OBJECT_ID) AS [Object Name], i.[name] AS [Index Name], ps.index_id
	, ps.index_type_desc, ps.avg_fragmentation_in_percent
	, ps.fragment_count, ps.page_count, i.fill_factor, i.has_filter
	, i.filter_definition, i.[allow_page_locks]
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL , N'LIMITED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK)
	ON ps.[object_id] = i.[object_id] 
	AND ps.index_id = i.index_id
INNER JOIN sys.objects AS o WITH (NOLOCK)
	ON i.[object_id] = o.[object_id]
WHERE ps.database_id = DB_ID()
--AND ps.page_count > 2500
ORDER BY ps.avg_fragmentation_in_percent DESC 
OPTION (RECOMPILE)

Full details on Microsoft’s Documentation