Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Calculate the Size of Index

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]

 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


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

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.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer