Skip to content

Instantly share code, notes, and snippets.

@juanonsoftware
Last active January 26, 2022 17:32
Show Gist options
  • Save juanonsoftware/66e1e0443f03c493ed2f1140374f86b3 to your computer and use it in GitHub Desktop.
Save juanonsoftware/66e1e0443f03c493ed2f1140374f86b3 to your computer and use it in GitHub Desktop.
Check Fragmentation of ALL Indexes in a Database
-- https://myadventuresincoding.wordpress.com/2013/05/27/sql-server-check-index-fragmentation-on-all-indexes-in-a-database/
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment