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.


OBJECT_NAME(i.OBJECT_ID) AS TableName, 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,


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



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s