깡민쓰~IT Story

파티션을 고려하지 못한 부분과 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)' desc

or

-- 가장 조각이 많이 난 인덱스 TOP 10
 
 SELECT TOP 10

            DB_NAME() AS 'DatabaseName'
 
          , OBJECT_NAME(S.OBJECT_ID) AS 'TableName'
 
          , I.NAME AS 'IndexName'
 
          , ROUND(AVG_FRAGMENTATION_IN_PERCENT, 2) AS 'Fragmentation %'
 
 FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,NULL) AS S
 
 INNER JOIN SYS.INDEXES AS I
 
 ON S.OBJECT_ID = I.OBJECT_ID AND S.INDEX_ID = I.INDEX_ID
 
 WHERE S.DATABASE_ID = DB_ID() -- 현재 데이터베이스
 
 AND I.NAME IS NOT NULL -- HEAP은 무시
 
 AND OBJECTPROPERTY(S.OBJECT_ID, 'IsMsShipped') = 0 -- 시스템 개체 무시
 
 ORDER BY [Fragmentation %] DESC