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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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