How do you determine the size of an index in SQL Server?

Question

I have an index -- let's call it IX_MY_INDEX -- in a SQL Server table (both compatibility modes 80 and 90) that I would like to determine the size of. How do I do this?

Update: Allain Lalonde's second solution below only works when the compatibility mode is set to 90; however, the particular database I am working on is in compatibility mode 80. Does anyone have a solution for compatibility mode 80?

Answer 1

You can determine the size of all indexes on a table (and other things) like so:

EXEC sp_spaceused TableName;

For a full breakdown you may:

SELECT * 
FROM sys.dm_db_index_physical_stats(db_id(), object_id('TableName'), null, null, 'DETAILED');

Answer 2

Adapted from the post on How to Find Size of All the Indexes on the Database, you can use:

SELECT OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName,
       OBJECT_NAME(i.OBJECT_ID) AS TableName,
       i.[name] AS IndexName,
       i.index_id AS IndexID,
       8 * SUM(a.used_pages) AS [Indexsize(KB)]
FROM sys.indexes i
JOIN sys.partitions p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID, i.index_id, i.[name]
ORDER BY OBJECT_NAME(i.OBJECT_ID), i.index_id

Which uses the following tables:

• sys.indexes
• sys.partitions
• sys.allocation_units