29 July,2013 by Jack Vamvas
Question: How can I calculate the index size in kilobytes for individual indexes?
Sp_spaceused returns the aggregate size of indexes in a table , but I need the individual indexes. I’ve read How to find the largest sql index and table size which does a good job of returning index disk usage , but doesn’t return individual indexes.
Answer: One method is to query on the sys.dm_db_partition_stats. Every partition page and row-count information is maintained.
The used_page_count column returns the total number of pages used for the partition. By grouping together these pages on the index name and adding the used_page_count, returns the disk space used.
SELECT ix.[name] AS IndexName ,SUM(ps.[used_page_count]) * 8 AS IndexSize_KB FROM sys.dm_db_partition_stats AS ps JOIN sys.indexes AS ix ON ps.[object_id] = ix.[object_id] AND ps.[index_id] = ix.[index_id] GROUP BY ix.[name]