728x90

파티션 구성 정보를 조회하면서 파티션의 사이즈 및 압축 정보까지 한꺼 번에 볼 수 있도록 수정 하였음.

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

728x90

+ Recent posts