파티션을 고려하지 못한 부분과 index_id 비교를 잘못해서 인덱스 크기 잘못 계산된 부분 재공유 합니다.
파티션을 이루는 파티션의 개수와 압축을 이용한 파티션의 개수를 같이 보여 줍니다.
SQL Server 2008 Enterprise Edition이 아니면 압축 관련 부분은 주석 처리하시면 됩니다.
select object_name(max(object_id))
, max(b.PartitionCnt) as 'PartitionCnt'
, max(b.CompressionCnt) as 'CompressionPartitionCnt'
, sum(used_page_count) * 8 as 'Total Used(KB)'
, sum(reserved_page_count) * 8 as 'Total Reserved(KB)'
, sum(case when index_id = 0 then used_page_count else 0 end) * 8 as 'Heap(KB)'
, sum(case when index_id = 1 then used_page_count else 0 end) * 8 as 'Cluster(KB)'
, sum(case when index_id >= 2 then used_page_count else 0 end) * 8 as 'Index(KB)'
, max(row_count) as 'RowCount'
, sum(in_row_used_page_count) * 8 as 'Row Used(KB)'
, sum(in_row_reserved_page_count) * 8 as 'Row Reserved(KB)'
, sum(lob_used_page_count) * 8 as 'LOB Used(KB)'
, sum(lob_reserved_page_count) * 8 as 'LOB Reserved(KB)'
, sum(row_overflow_used_page_count) * 8 as 'Overflow Used(KB)'
, sum(row_overflow_reserved_page_count) * 8 as 'Overflow Reserved(KB)'
from ( select object_id
, index_id
, sum(used_page_count) as used_page_count
, sum(reserved_page_count) as reserved_page_count
, sum(row_count) as row_count
, sum(in_row_used_page_count) as in_row_used_page_count
, sum(in_row_reserved_page_count) as in_row_reserved_page_count
, sum(lob_used_page_count) as lob_used_page_count
, sum(lob_reserved_page_count) as lob_reserved_page_count
, sum(row_overflow_used_page_count) as row_overflow_used_page_count
, sum(row_overflow_reserved_page_count) as row_overflow_reserved_page_count
from sys.dm_db_partition_stats with(nolock)
where object_name(object_id) not like 'sys%'
group by object_id, index_id
) as a
cross apply ( select count(aa.object_id) as 'PartitionCnt'
, sum(case when aa.data_compression > 0 then 1 else 0 end) as 'CompressionCnt'
from sys.partitions aa
where aa.object_id = a.object_id and aa.index_id = a.index_id
group by aa.object_id, aa.index_id
) b
group by object_id
order by 'Total Used(KB)'