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)
1 Comment. Leave new
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