깡민쓰~IT Story

select  top (100)

        case

            when c.dbid = 32767 then 'ResourceDB'

            when c.dbid is null then 'NONE'

        else db_name(c.dbid) end as 'DBName'

,       isnull(object_name(c.objectid, c.dbid), c.text) as 'PlanQuery'

,       b.execution_count as 'Total Execute'

,       b.execution_count * 1.0 / datediff(ss, b.creation_time, dateadd(ss, 1, b.last_execution_time)) as 'ExecCount/sec'

,       b.total_worker_time * 1.0 / b.execution_count / 1000 as 'CPUTimes(ms)/exec'

,       b.max_worker_time * 1.0 / 1000 as 'MaxCPUTimes(ms)'

,       b.total_physical_reads / b.execution_count as 'PhysicalReads/exec'

,       b.max_physical_reads as 'MaxPhysicalReads'

,       (b.total_logical_writes + total_logical_reads) / b.execution_count as 'LogicalIO/exec'

,       b.max_logical_writes + b.max_logical_reads as 'MaxLogicalIO'

,       b.total_elapsed_time * 1.0 / b.execution_count / 1000 as 'Duration(ms)/exec'

,       b.max_elapsed_time * 1.0 / 1000 as 'MaxDuration(ms)'

,       substring(c.text, (b.statement_start_offset / 2) + 1, ((case b.statement_end_offset when -1 then datalength(c.text) else b.statement_end_offset end - b.statement_start_offset)/2) + 1) as 'StatementQuery'

,       d.query_plan

from              sys.dm_exec_query_stats b with(nolock)

    cross apply   sys.dm_exec_sql_text(b.sql_handle) c

    cross apply   sys.dm_exec_text_query_plan(b.plan_handle, b.statement_start_offset, b.statement_end_offset) d

order by 'LogicalIO/exec' desc

--order by 'MaxLogicalIO' desc

--order by 'ExecCount/sec' desc

--order by 'CPUTimes(ms)/exec' desc

--order by 'MaxCPUTimes(ms)' desc

--order by 'PhysicalReads/exec' desc

--order by 'MaxPhysicalReads' desc

--order by 'Duration(ms)/exec' desc

--order by 'MaxDuration(ms)' desc

select  top 10

        a.session_id, a.host_name, a.client_interface_name, a.login_name, a.status, a.last_request_start_time

,       b.start_time, b.status, b.command, db_name(c.dbid) as 'DBName', object_name(c.objectid, c.dbid) as 'OBJName', b.last_wait_type, b.wait_time

,       b.cpu_time, b.total_elapsed_time, b.reads, b.writes, b.logical_reads

,       substring(c.text, (b.statement_start_offset / 2) + 1, ((case b.statement_end_offset when -1 then datalength(c.text) else b.statement_end_offset end - b.statement_start_offset)/2) + 1) as 'StatementQuery'

,       d.query_plan

from               sys.dm_exec_sessions a

    inner join     sys.dm_exec_requests b on a.session_id = b.session_id

    cross apply   sys.dm_exec_sql_text(b.sql_handle) c

    cross apply   sys.dm_exec_text_query_plan(b.plan_handle, b.statement_start_offset, b.statement_end_offset) d

where a.session_id > 50 and a.last_request_start_time > a.last_request_end_time

order by a.last_request_start_time, a.session_id

--order by b.cpu_time desc

--order by b.total_elapsed_time desc

--order by b.reads + b.writes desc

--order by b.logical_reads desc

select   a.session_id as 'BlockedSessionID', b.blocking_session_id

,        b.wait_duration_ms / 1000 as 'WaitDuration(Sec)', b.wait_type

,        c.resource_type, db_name(c.resource_database_id) as 'ResourceDBName', c.request_mode, c.request_type, c.request_status, b.resource_description

,        db_name(e.dbid) as 'BlockedDBName', object_name(e.objectid) as 'BlockedObjectName', e.text as 'BlockedQuery'

,        substring(e.text, (d.statement_start_offset / 2) + 1, ((case d.statement_end_offset when -1 then datalength(e.text) else d.statement_end_offset end - d.statement_start_offset)/2) + 1) as 'BlockedStmt'

,        db_name(g.dbid) as 'BlockingDBName', object_name(g.objectid) as 'BlockingObjectName', g.text as 'BlockingQuery'

,        a.task_address, a.worker_address, b.waiting_task_address, b.blocking_task_address

from            sys.dm_os_tasks a with(nolock)

    inner join  sys.dm_os_waiting_tasks b with(nolock)  on a.task_address = b.waiting_task_address or a.task_address = b.blocking_task_address

    inner join  sys.dm_tran_locks c with(nolock)        on b.resource_address = c.lock_owner_address

    inner join  sys.dm_exec_requests d with(nolock)     on a.session_id = d.session_id and a.request_id = d.request_id

    cross apply sys.dm_exec_sql_text(d.sql_handle) e

    inner join  sys.dm_exec_connections f               on b.blocking_session_id = f.session_id

    cross apply sys.dm_exec_sql_text(f.most_recent_sql_handle) g

where b.blocking_session_id is not null

--  and b.wait_duration_ms > 3000

select  top 20

        db_name(a.database_id) as 'DBName', object_name(b.object_id, a.database_id) as 'OBJName'

,       b.index_id, isnull(max(c.name), 'Heap') as 'IndexName', a.page_type

,       left(cast(count(*) * 1.0 / max(b.used_pages) as varchar(100)), 6) as 'Ratio'

,       count(*) as 'BufferPageCNT', max(b.used_pages) as 'UsedPageCNT', max(b.total_pages) as 'TotalPageCNT'

,       sum(case when is_modified = 1 then 1 else 0 end) as 'ModifyCNT'

,       count(*) * 8 / 1024.0 as 'BufferSize(MB)', sum(a.free_space_in_bytes) / 1024.0 / 1024.0 as 'FreeSpace(MB)'

,       max(b.used_pages) * 8 / 1024.0 as 'TotalUsed(MB)', max(b.total_pages) * 8 / 1024.0 as 'TotalReserved(MB)'

,       case when a.page_type = 'TEXT_MIX_PAGE' then cast(max(b.rows) * (count(*) * 1.0 / max(b.used_pages)) as bigint) else sum(a.row_count) end as 'BufferRowCNT'

,       max(b.rows) as 'TotalRowCNT'

from                sys.dm_os_buffer_descriptors a with(nolock)

    inner join  ( select    a.allocation_unit_id, a.type_desc, a.total_pages, a.used_pages, b.object_id, b.index_id, b.rows

                    from            sys.allocation_units a with(nolock)

                        inner join  sys.partitions b with(nolock) on a.container_id = b.hobt_id and (a.type = 1 or a.type = 3)

                    union all

                    select    a.allocation_unit_id, a.type_desc, a.total_pages, a.used_pages, b.object_id, b.index_id, b.rows

                    from            sys.allocation_units a with(nolock)

                        inner join  sys.partitions b with(nolock) on a.container_id = b.partition_id and a.type = 2

                  ) b on a.allocation_unit_id = b.allocation_unit_id

    left outer join sys.indexes c on b.object_id = c.object_id and b.index_id = c.index_id

where a.database_id = db_id() and b.used_pages > 0

group by a.database_id, b.object_id, b.index_id, a.page_type

order by count(*) desc

--order by Ratio desc

--order by ModifyCNT desc

select   a.name

,        b.size * 8 / 1024 as 'PhysicalSize(MB)', (b.size - sum(total_pages)) * 8 / 1024 as 'UnAllocationSize(MB)'

,        sum(total_pages) * 8 / 1024 as 'TotalAllocation(MB)', sum(used_pages) * 8 / 1024 as 'UsedAllocation(MB)'

,        sum(data_pages) * 8 / 1024 as 'DataSize(MB)', sum(total_pages - used_pages) * 8 / 1024 as 'FreeSize(MB)'

from              sys.filegroups a with(nolock)

    inner join (  select data_space_id, sum(size) as 'Size'

                    from sys.database_files with(nolock)

                    group by data_space_id ) b                on a.data_space_id = b.data_space_id

    inner join    sys.allocation_units c with(nolock)         on a.data_space_id = c.data_space_id

group by a.name, b.size

order by 1, 2

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

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

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