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

Question

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

Priyanka K S · Answer

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 This will List every index on every table in your database, ordered by the percentage of index fragmentation.

Firoz Shaikh · Answer

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 AND 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