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?

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

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.

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

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 (https://blog.sqlauthority.com)

Quest

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

    Reply

Leave a Reply