SQL Server: check index fragmentation
Two queries that list index fragmentation in the current database: a quick read, then a sampled read.
Two queries to check index fragmentation in the current database, based on sys.dm_db_index_physical_stats. The first lists the fragmentation of all indexes. The second reads only the leaf level of user tables, from a sample, and adds the space used per page, the fill factor and the size of the index.
Fragmentation of all indexes
SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc
Leaf level, sampled
SELECT Coalesce(Object_Schema_Name(indexes.object_id) + '.', '')
+ Coalesce(Object_Name(indexes.object_id) + '/', '')
+ Coalesce(indexes.name, 'Heap'),
Str(avg_fragmentation_in_percent, 10,1) AS [avg_fragmentation_%],
Str(avg_page_space_used_in_percent, 10,1) AS [avg_page_space_used_%],
fill_factor,
Str((avg_record_size_in_bytes * record_count) / (1024.0 * 1024), 10,2) AS [IndexSize_(MB)]
FROM sys.dm_db_index_physical_stats(Db_Id(), NULL, NULL, NULL, 'Sampled') AS IPS
INNER JOIN sys.indexes
ON indexes.index_id = IPS.index_id AND indexes.object_id = IPS.object_id
WHERE ObjectProperty(indexes.object_id, 'IsUserTable') = 1 AND index_level=0 --leaf level
ORDER BY [avg_fragmentation_%] DESC;
Notes
- With
NULLas the mode, the first query uses the default mode (LIMITED), which is faster but less precise than theSampledmode used by the second. - The second query shows
Heapfor tables without a clustered index.