How to obtain the size of a specific Index
Published: Aug 21, 2015
We know that sp_spaceUsed is a great way to obtain a few simple figures about a table such as row count, table size, and index size… but these are all cumulative. Therefore what happens if you want to know the specific size of an individual index?

Tuning a couple of queries led to my merging a few indexes as they were overlapping and could have been consolidated into just a single index.

Part of the method I used to prove that this was the way to go (other than the overhead of maintaining multiple indexes) was to demonstrate the disk space savings. But for that I needed to know the size of each individual index.

So how do we do this? Well the information is there, you just need a simple query and a slight manipulation in order to pull it out of SQL Server.

There are a few approaches to this, but for basic data I like to go with the following method as it’s fast and gave me all I needed:

use AdventureWorks2012
go

select object_name(s.object_id) tableName, i.name indexName,
      
case s.index_id when 0 then 'Heap'
          
when 1 then 'Clustered'
          
else 'NonClustered' end indexType,
      
sum(s.used_page_count) * 8 as indexSizeKB
from sys.dm_db_partition_stats as s
inner join sys.indexes as i
on s.object_id = i.object_id
and s.index_id = i.index_id
-- optional table name filter
--where s.object_id = object_id('sales.SalesOrderDetail')
group by object_name(s.object_id), i.name,
      
case s.index_id when 0 then 'Heap'
          
when 1 then 'Clustered'
          
else 'NonClustered' end
order by
tableName, indexSizeKB desc


Using this query for just the sales.salesOrderDetail table in AdventureWorks2012 produced the following:

You can therefore now see exactly how much space each index is using. Simple as that.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron