Get the size of indexes for a given table

It’s important to be aware of how large the indexes are on your tables. If the sum index size is larger than the total table size, you may want to consider revising the design.

The following script will provide the disk size of all indexes associated with the given table.

SELECT
	OBJECT_NAME(I.OBJECT_ID) AS TableName,
	I.name AS IndexName,
	I.index_id AS IndexID,
	(8 * SUM(a.used_pages)) AS 'IndexSize(KB)',
	(8 * SUM(a.used_pages)/1000) AS 'IndexSize(MB)'
FROM
	sys.indexes AS I
JOIN
	sys.partitions AS P
		ON P.OBJECT_ID = I.OBJECT_ID
		AND P.index_id = I.index_id
JOIN
	sys.allocation_units AS A 
		ON A.container_id = P.partition_id
WHERE
	OBJECT_NAME(I.OBJECT_ID) = 'myTable' -- Set table name here.
GROUP BY
	I.OBJECT_ID,
	I.index_id,
	I.name
ORDER BY
	OBJECT_NAME(I.OBJECT_ID),
	I.index_id
GO

I hope that helps!

Subscribe

0 comments