How to know size of index and fragmentation level of index ..?

Firoz Shaikh

5y728

Need to know index size and fragmentation to analyse time for rebuild

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Priyanka K S5y ago
1. SELECT dbschemas.[name] as 'Schema',
2. dbtables.[name] as 'Table',
3. dbindexes.[name] as 'Index',
4. indexstats.avg_fragmentation_in_percent,
5. indexstats.page_count
6. FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
7. INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
8. INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
9. INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
10. AND indexstats.index_id = dbindexes.index_id
11. WHERE indexstats.database_id = DB_ID()
12. ORDER BY indexstats.avg_fragmentation_in_percent desc
This will List every index on every table in your database, ordered by the percentage of index fragmentation.
0

SELECT dbschemas.[name] as 'schema', dbtables.[name] AS 'Table' ,dbindexes.[name] AS 'Index' ,indexstats.avg_fragmentation_in_percent ,CASE WHEN indexstats.avg_fragmentation_in_percent < 10 THEN 'NOTHING' WHEN indexstats.avg_fragmentation_in_percent >= 10 AND indexstats.avg_fragmentation_in_percent < 30 THEN 'REORGANIZE' WHEN indexstats.avg_fragmentation_in_percent >= 10 THEN 'REBUILD' END ,indexstats.page_count ,(indexstats.page_count*8)/1024 as size_in_MB 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() AND page_count > 1000 AND dbindexes.NAME IS NOT NULL ORDER BY dbtables.[name],indexstats.avg_fragmentation_in_percent DESC go