Home > INDEXES > size of all non clustered indexes (NCI) in a database sql 2005

size of all non clustered indexes (NCI) in a database sql 2005

use the below query to get the size of all NCI in a particular database .. works for 2005 and above

SELECT
CURRENT_TIMESTAMP as ‘DateTimeStamp’,
@@Servername     ServerName,
db_name() DBName,
OBJECT_NAME(partition_stats.object_id) ObjectName,
SUM(used_page_count) AS used_page_count,
(SUM(used_page_count) * 8) / 1024.0 / 1024.0 AS used_page_size_GB,
SUM(reserved_page_count) AS reserved_page_count,
(SUM(reserved_page_count) * 8) / 1024.0 / 1024.0 AS reserved_page_size_GB
FROM sys.dm_db_partition_stats AS partition_stats
INNER JOIN sys.indexes AS indexes ON partition_stats.object_id = indexes.object_id
and partition_stats.index_id = indexes.index_id
where indexes.type_desc = ‘NONCLUSTERED’
GROUP BY OBJECT_NAME(partition_stats.object_id)
ORDER BY OBJECT_NAME(partition_stats.object_id)

 

 

Advertisements
Categories: INDEXES Tags:
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: