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.

Reference: Pinal Dave (

SQL DMV, SQL Index, SQL Scripts, SQL Server
Previous Post
What is Read Ahead Read in SQL Server? – Interview Question of the Week #197
Next Post
Does Sort Order in Index Column Matters for Performance? – Interview Question of the Week #199

Related Posts

1 Comment. Leave new

  • Wilfred van Dijk
    November 5, 2018 1:22 pm

    although deprecated, sysindexes makes this query much easier:

    select object_schema_name(id) as SchemaName, object_name(id) as TableName, name as IndexName, dpages/128 as IndexSizeMB, rowcnt
    from sysindexes
    where indid in (1,2)
    and FirstIAM is not null
    and objectproperty(id, ‘IsUserTable’) = 1
    order by 1,2


Leave a Reply Cancel reply

Exit mobile version