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]

 Read More

SQL Server - How to find the largest sql index and table size

SQL Server - Predict ALTER INDEX REORGANIZE finish time

SQL Server Disable Indexes and Rebuild Indexes dynamically

SQL Index Fragmentation and sys.dm_db_index_physical_stats