파티션 구성 정보를 조회하면서 파티션의 사이즈 및 압축 정보까지 한꺼 번에 볼 수 있도록 수정 하였음.
select a.PartitionName, a.FuncName, a.PartitionCount, a.Type, a.Value, a.Equality, a.Destnation_id, c.name as 'FileGroupName'
, object_name(d.object_id) as 'ObjectName'
, d.used_page_count * 8 as 'Total Used(KB)'
, d.reserved_page_count * 8 as 'Total Reserved(KB)'
, row_count as 'RowCount'
, data_compression_desc
from ( select a.name as 'PartitionName', b.name as 'FuncName', b.fanout as 'PartitionCount'
, case when boundary_value_on_right = 1 then 'Right' else 'Left' end as 'Type'
, d.value, case when boundary_value_on_right = 1 then '<=' else '>=' end as 'Equality'
, case when boundary_value_on_right = 1 then d.boundary_id + 1 else d.boundary_id end as 'destnation_id'
, a.data_space_id
from sys.partition_schemes a with(nolock)
inner join sys.partition_functions b with(nolock) on a.function_id = b.function_id
inner join sys.partition_parameters c with(nolock) on b.function_id = c.function_id
inner join sys.partition_range_values d with(nolock) on c.function_id = d.function_id and c.parameter_id = d.parameter_id
union all
select a.name as 'PartitionName', b.name as 'FuncName', b.fanout as 'PartitionCount', 'Right' as 'Type', d.value, '>', 1, a.data_space_id
from sys.partition_schemes a with(nolock)
inner join sys.partition_functions b with(nolock) on a.function_id = b.function_id
inner join sys.partition_parameters c with(nolock) on b.function_id = c.function_id
inner join ( select function_id, parameter_id, min(value) as 'value'
from sys.partition_range_values with(nolock)
group by function_id, parameter_id
) d on c.function_id = d.function_id and c.parameter_id = d.parameter_id
where b.boundary_value_on_right = 1
union all
select a.name as 'PartitionName', b.name as 'FuncName', b.fanout as 'PartitionCount', 'Left' as 'Type', d.value, '<', d.boundary_id, a.data_space_id
from sys.partition_schemes a with(nolock)
inner join sys.partition_functions b with(nolock) on a.function_id = b.function_id
inner join sys.partition_parameters c with(nolock) on b.function_id = c.function_id
inner join ( select function_id, parameter_id, max(value) as 'value', max(boundary_id) + 1 as 'boundary_id'
from sys.partition_range_values with(nolock)
group by function_id, parameter_id
) d on c.function_id = d.function_id and c.parameter_id = d.parameter_id
where b.boundary_value_on_right = 0
) a
inner join sys.destination_data_spaces b with(nolock) on a.data_space_id = b.partition_scheme_id and a.destnation_id = b.destination_id
inner join sys.data_spaces c with(nolock) on b.data_space_id = c.data_space_id
cross apply ( select bb.object_id, bb.used_page_count, bb.reserved_page_count, bb.row_count, cc.data_compression_desc
from sys.indexes aa with(nolock)
inner join sys.dm_db_partition_stats bb with(nolock) on aa.object_id = bb.object_id and aa.index_id = bb.index_id
inner join sys.partitions cc with(nolock) on aa.object_id = cc.object_id and aa.index_id = cc.index_id
where aa.data_space_id = a.data_space_id
and a.destnation_id = bb.partition_number and a.destnation_id = cc.partition_number
) d
order by a.PartitionName, a.Destnation_id
'IT이야기 > MS-SQLDMV' 카테고리의 다른 글
| [DMV] 가장 오래 돌고 있는 세션의 요청 쿼리 정보 확인 (0) | 2016.06.29 |
|---|---|
| [DMV] Lock으로 인해 Block 된 쿼리 찾기 (0) | 2016.06.29 |
| [DMV] 현재 DB의 Object 중에 Buffer Pool 메모리 상에 있는 Object의 사용량 (0) | 2016.06.29 |
| [DMV] 파일 그룹별 전체 크기와 할당 및 사용하고 있는 공간 확인 (0) | 2016.06.29 |
| [DMV] 각 테이블에 할당된 공간과 실제 데이터와 인덱스가 차지하고 있는 공간 확인 (0) | 2016.06.29 |