LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon