Find the size of all Indexes in a database

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 233835 More actions · Answer 1

Calling the index physical stats dmv in a cursor could have serious ramifications on a large database, you should bear this in mind.

Query sys.partitions and sys.allocation_units for details of index allocated and used space.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Bodhisatya Old Hand Points: 313 More actions · Answer 2

Thanks Perry for the feedback. Will try to rewrite the same as you advised

Perry Whittle SSC Guru Points: 233835 More actions · Answer 3

I prefer this

SELECTOBJECT_NAME(i.object_id) AS TableName

, ISNULL(i.name, 'HEAP') AS IndexName

, i.index_id as IndexID

, i.type_desc AS IndexType

, p.partition_number AS PartitionNo

, p.rows AS NumRows

, au.type_desc AS InType

, au.total_pages AS NumPages

, au.total_pages * 8 AS TotKBs

, au.used_pages * 8 AS UsedKBs

, au.data_pages * 8 AS DataKBs

FROM sys.indexes i INNER JOIN sys.partitions p

ON i.object_id = p.object_id AND i.index_id = p.index_id

INNER JOIN sys.allocation_units au ON

CASE

WHEN au.type IN (1,3) THEN p.hobt_id

WHEN au.type = 2 THEN p.partition_id

END = au.container_id

WHERE OBJECT_NAME(i.object_id) NOT LIKE 'sys%'

AND OBJECT_NAME(i.object_id) NOT LIKE 'queue%'

AND object_name(i.object_id) <> 'dtproperties'

ORDER BY p.rows desc

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉