View size of Clustered and Non Clustered index on a table

Hi Folks,

Happy New Year 2015,

Longtime I published my last post.Hope I will post regularly from now
onwards probably with some interesting post this year.

Today I am posting a query which most of developers/DBA’s often
require during optimization phase or when we have some limits on
our hardware constraints as far as space is concerned.

Open new query window under ssms and fire below query against
your respective database by providing appropriate table name.

DECLARE @TableName VARCHAR(200)
SET @TableName = ‘NAME_OF_YOUR_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)) / 1024 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) = @TableName
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id

Output:

When above query is executed against AdventureWorks DB
by providing Databaselog as table name produces below output,

image

Advertisements