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]
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
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |