As we know, sp_spaceused gives the size of table and index but it gives the sum of size of all indexes on a table. What if you need to capture size of individual index on a table? Thats where the following two queries comes handy:

Query 1:

Uses sys.indexes and sys.dm_db_partition_stats DMF to calculate the size of individual index on a table.

-- Ensure a USE <databasename> statement has been executed first.
SELECT i.[name] AS IndexName
	,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]
GROUP BY i.[name]
ORDER BY i.[name]
GO

Sample output of the first query when executed against AdventureWorks database:

Query 2:

Uses sys.dm_db_index_physical_stats and sys.dm_db_partition_stats to calculate the size of individual index on a table. This query is more reliable as compared to first query because it uses DMFs.

Sample output of the second query when executed against AdventureWorks database:

-- Ensure a USE  statement has been executed first.
SELECT [DatabaseName]
	,[ObjectId]
	,[ObjectName]
	,[IndexId]
	,[IndexDescription]
	,CONVERT(DECIMAL(16, 1), (SUM([avg_record_size_in_bytes] * [record_count]) / (1024.0 * 1024))) AS [IndexSize(MB)]
	,[lastupdated] AS [StatisticLastUpdated]
	,[AvgFragmentationInPercent]
FROM (
	SELECT DISTINCT DB_Name(Database_id) AS 'DatabaseName'
		,OBJECT_ID AS ObjectId
		,Object_Name(Object_id) AS ObjectName
		,Index_ID AS IndexId
		,Index_Type_Desc AS IndexDescription
		,avg_record_size_in_bytes
		,record_count
		,STATS_DATE(object_id, index_id) AS 'lastupdated'
		,CONVERT([varchar](512), round(Avg_Fragmentation_In_Percent, 3)) AS 'AvgFragmentationInPercent'
	FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'detailed')
	WHERE OBJECT_ID IS NOT NULL
		AND Avg_Fragmentation_In_Percent <> 0
	) T
GROUP BY DatabaseName
	,ObjectId
	,ObjectName
	,IndexId
	,IndexDescription
	,lastupdated
	,AvgFragmentationInPercent

I hope you will find this information useful 🙂