Skip to content

Instantly share code, notes, and snippets.

@JohnLBevan

JohnLBevan/ShowIndexFragmentation.sql

Created February 27, 2014 14:41
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save JohnLBevan/4d64ed9f50e407434c3d to your computer and use it in GitHub Desktop.
Save JohnLBevan/4d64ed9f50e407434c3d to your computer and use it in GitHub Desktop.
Download ZIP
Raw
ShowIndexFragmentation.sql
SELECT 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment