[DMV] 실행 Statement 단위의 성능 순 조회

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