What is the size of your index?

I got the following question a couple days ago:

“I have one problem with my database. The size of mdf file is huge and i feel it is not the data that is causing the increase in size, i feel indexes might be occupying space.  Is there any way to find that indexes are occupying space or data is occupying space. Thanks in advance”

I jumped out to my blog because I’d already written something up on that, only to find that it was still in my draft blog folder and wasn’t really written.  So time to finish it…

Indexes can and do take up a significant amount of space in a database.  And in a lot of the databases, that I’ve worked on, the the non-clustered indexes take up more space than the clustered index or heap does.

Size information for indexes can found in the dynamic management view sys.dm_db_index_usage_stats.  This view provides partition level detail on row and page counts for indexes.  The page counts return reserved, data, and used counts for each partition.

I use the following query for a breakdown of size per partition/index/table:

SELECT
OBJECT_SCHEMA_NAME(i.object_id) as schema_name
,OBJECT_NAME(i.object_id) as table_name
,COALESCE(i.name, space(0)) as index_name
,ps.partition_number
,ps.row_count
,CAST(((ps.in_row_data_page_count) * CAST(8 as float))/1024. as decimal(12,2)) as data_size_in_mb
,CAST(((ps.in_row_used_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) * CAST(8 as float))/1024. as decimal(12,2)) as used_size_in_mb
,CAST((ps.reserved_page_count * CAST(8 as float))/1024. as decimal(12,2)) as total_size_in_mb
,i.type_desc
FROM sys.indexes i
INNER JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id
ORDER BY object_name(i.object_id), total_size_in_mb desc

The output of which looks a like this:

As is shown in the first row, the index PK_Address_AddressID on Person.Address has 19,614 rows and takes up 2.20 MB of disk space.  Pretty simple.