How to obtain the size of a specific Index
Published: 17th March 2014
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.
Comments:
NB: Comments will only appear once they have been moderated.