Archive

Posts Tagged ‘INDEXES’

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

February 10, 2012 Leave a comment

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:

Hello world!

January 25, 2012 1 comment

Hi All !!! this blog is about my SQL DBA exp ..

Categories: INDEXES Tags: