Friday, March 5, 2010

TSQL : Total Size of Partioned Indexes

If you run my Index size script you get a row for each partition.

This version rolls up the results by grouping on the common columns and summing the index size information. Far more helpful for a quick glance...

SELECT   sys_schemas.name AS SchemaName
,sys_objects.name AS TableName
,sys_indexes.name AS IndexName
,sys_indexes.type_desc AS IndexType
,MAX(partitions.rows) AS TableRows
,SUM(partition_stats.used_page_count) * 8 AS IndexSizeKB
,CAST(SUM(partition_stats.used_page_count) * 8 / 1024.00 AS Decimal(10,3))AS IndexSizeMB
,CAST(SUM(partition_stats.used_page_count) * 8 / 1048576.00 AS Decimal(10,3)) AS IndexSizeGB
FROM sys.dm_db_partition_stats partition_stats
INNER JOIN sys.indexes sys_indexes
ON partition_stats.[object_id] = sys_indexes.[object_id]
AND partition_stats.index_id = sys_indexes.index_id
AND sys_indexes.type_desc <> 'HEAP'
INNER JOIN sys.objects sys_objects
ON sys_objects.[object_id] = partition_stats.[object_id]
INNER JOIN sys.schemas sys_schemas  
ON sys_objects.[schema_id] = sys_schemas.[schema_id]
AND sys_schemas.name <> 'SYS'
INNER JOIN sys.partitions partitions
  ON partitions.object_id = partition_stats.object_id 
--WHERE partition_stats.[object_id] = object_id('dbo.tablename')
GROUP BY
sys_schemas.name
,sys_objects.name
,sys_indexes.name
,sys_indexes.type_desc
ORDER BY 6 desc

No comments: