Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

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

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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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


© Copyright 2020 SQLTraining Ltd.