SQL Server 2005 - How do I find out the disk size of a specific index

Question

How do you find the disk size of a specific index in sql server 2005?

Answer 1

USE master

SELECT SUM(used_page_count) * 8 AS [Size in kb]
FROM sys.indexes i 
JOIN sys.dm_db_partition_stats p ON p.object_id = i.object_id AND i.index_id = p.index_id
WHERE i.object_id=OBJECT_ID('dbo.spt_values') AND i.name='ix2_spt_values_nu_nc'

Answer 2

Find out the disk size of an index:

SELECT
    OBJECT_NAME(I.OBJECT_ID) AS TableName,
    I.name AS IndexName,   
    8 * SUM(AU.used_pages) AS 'Index size (KB)',
    CAST(8 * SUM(AU.used_pages) / 1024.0 AS DECIMAL(18,2)) AS 'Index size (MB)'
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 AU ON AU.container_id = P.partition_id
WHERE 
    OBJECT_NAME(I.OBJECT_ID) = '<TableName>'    
GROUP BY
    I.OBJECT_ID,    
    I.name
ORDER BY
    TableName

Answer 3

You can use the stored procedure "sp_spaceused". it will tell you how much space is used for the index as well as the space used for data.

USE AdventureWorks2012;
GO
EXEC sp_spaceused 'Purchasing.Vendor';
GO

More info: http://msdn.microsoft.com/en-us/library/ms188776.aspx