How to Check Index Fragmentation on Indexes in a Database

ELECT dbschemas.[name] as 'Schema',

dbtables.[name] as 'Table',

dbindexes.[name] as 'Index',

indexstats.avg_fragmentation_in_percent,

indexstats.page_count

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

AND indexstats.index_id = dbindexes.index_id

WHERE indexstats.database_id = DB_ID()

ORDER BY indexstats.avg_fragmentation_in_percent desc

This query can be modified to focus on specific tables by append the table name to the 'where' clause:

WHERE indexstats.database_id = DB_ID() AND dbtables.[name] like '%%'




The following table summarizes when to use each one:

Reference Values (in %)	Action	SQL statement
avg_fragmentation_in_percent > 5 AND < 30	Reorganize Index	ALTER INDEX REORGANIZE
avg_fragmentation_in_percent > 30	Rebuild Index	ALTER INDEX REBUILD

https://www.schneider-electric.com/en/faqs/FA234246/