Check Index Fragmentation on ALL Indexes in a Database
Here is a simple query that will list every index on every table in your database, ordered by the percentage of index fragmentation. This query will work on SQL2K5 or newer.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 dbtables.[name] DESC --ORDER BY indexstats.avg_fragmentation_in_percent desc |
The output will look like
Source: myadventuresincoding.wordpress.com
Or
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT OBJECT_NAME(DMV.object_id) AS TABLE_NAME , SI.NAME AS INDEX_NAME , avg_fragmentation_in_percent AS FRAGMENT_PERCENT , DMV.record_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS DMV LEFT OUTER JOIN SYS.INDEXES AS SI ON DMV.OBJECT_ID = SI.OBJECT_ID AND DMV.INDEX_ID = SI.INDEX_ID --WHERE avg_fragmentation_in_percent > 10 AND index_type_desc IN ( 'CLUSTERED INDEX', 'NONCLUSTERED INDEX' ) AND DMV.record_count >= 2000 --ORDER BY TABLE_NAME DESC ORDER BY FRAGMENT_PERCENT desc |