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

Question: How to Find Size of the Index for Tables?

Answer: Earlier this week, during the Comprehensive Database Performance Health Check, I was asked this question by my client. Let us see the script which will give us details related to Schema, Tables, Index and its size.

SELECT SCHEMA_NAME(t.schema_id) [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
INNER JOIN sys.indexes AS ix ON ps.[object_id] = ix.[object_id]
AND ps.[index_id] = ix.[index_id]
INNER JOIN sys.tables t ON t.OBJECT_ID = ix.object_id
GROUP BY t.[name], ix.[name], t.schema_id
ORDER BY t.[name]

When you run above script, it will return following resultset.

Let me know if you know any other script which one can use to find out the size of the index. I use this all the time with my customer and I find it very useful.

Here are few blog posts which are related to previous interview questions and answers.

• 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

Reference: Pinal Dave (https://blog.sqlauthority.com)