How to Find Size of the Index for Tables? – Interview Question of the Week #198

Question: How do you find the size of a table’s indexes?

Answer: Sum each index’s used_page_count in sys.dm_db_partition_stats. Each SQL Server page is 8 KB, so multiplying by 8 gives used space in KB.

A client asked this during a Comprehensive Database Performance Health Check. I use this query because it puts the schema, table, index and size together:

SELECT SCHEMA_NAME(t.schema_id) AS [Schema Name],
       t.name AS [Table name], ix.name AS [Index name],
       SUM(ps.used_page_count) * 8 AS [Index size (KB)],
       SUM(ps.used_page_count) * 8 / 1024.0 AS [Index size (MB)]
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
JOIN sys.tables AS t ON t.object_id = ix.object_id
GROUP BY t.object_id, t.schema_id, t.name, ix.index_id, ix.name
ORDER BY SCHEMA_NAME(t.schema_id), t.name, ix.index_id;

The sum covers all partitions of an index. This is used allocated space, including in-row, LOB and row-overflow pages; it is not the number of bytes occupied only by key values. Use reserved_page_count instead if the question is reserved space.

A heap has index_id = 0 and a NULL index name, so it can appear too. The clustered index includes the table’s stored rows. Do not count it a second time as both table data and an independent copy. Specialized memory-optimized storage needs other diagnostics.

For SQL Server 2022 and later this DMV requires VIEW DATABASE PERFORMANCE STATE and VIEW SECURITY DEFINITION; earlier versions require VIEW DATABASE STATE and VIEW DEFINITION. If you use another helpful size query, share it and I will give credit.

Related interview questions:

• What is Read Ahead Read in SQL Server? – Interview Question of the Week #197
• Do Index Reorganization Update Statistics? – Interview Question of the Week #196
• How to Find Queries Using an Index From SQL Server Plan Cache? – Interview Question of the Week #195
• How to Change Language for SSMS? – Interview Question of the Week #194
• How to STOP the Usage of SELECT * For Views? – Interview Question of the Week #193
• How to Escape a Single Quote in SQL Server? – Interview Question of the Week #192
• Where is the Default Settings for Backup Compression? – Interview Question of the Week #191

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.