참조 쿼리 하단 참조.
Top 20 Executed Queries
;with frequent_queries as
(
select top 20
query_hash,
sum(execution_count) executions
from sys.dm_exec_query_stats
where query_hash <> 0x0
group by query_hash
order by sum(execution_count) desc
)
select @@servername as server_name,
coalesce(db_name(st.dbid), db_name(cast(pa.value AS INT)), 'Resource') AS [DatabaseName],
coalesce(object_name(ST.objectid, ST.dbid), '') as [object_name],
qs.query_hash,
qs.execution_count,
executions as total_executions_for_query,
SUBSTRING(ST.TEXT,(QS.statement_start_offset + 2) / 2,
(CASE
WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),ST.text)) * 2
ELSE QS.statement_end_offset
END - QS.statement_start_offset) / 2) as sql_text,
qp.query_plan
from sys.dm_exec_query_stats qs
join frequent_queries fq
on fq.query_hash = qs.query_hash
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
cross apply sys.dm_exec_query_plan (qs.plan_handle) qp
outer apply sys.dm_exec_plan_attributes(qs.plan_handle) pa
where pa.attribute = 'dbid'
order by fq.executions desc,
fq.query_hash,
qs.execution_count desc
option (recompile)
Top 20 I/O Consumers
;with high_io_queries as
(
select top 20
query_hash,
sum(total_logical_reads + total_logical_writes) io
from sys.dm_exec_query_stats
where query_hash <> 0x0
group by query_hash
order by sum(total_logical_reads + total_logical_writes) desc
)
select @@servername as servername,
coalesce(db_name(st.dbid), db_name(cast(pa.value AS INT)), 'Resource') AS [DatabaseName],
coalesce(object_name(ST.objectid, ST.dbid), '') as [object_name],
qs.query_hash,
qs.total_logical_reads + total_logical_writes as total_io,
qs.execution_count,
cast((total_logical_reads + total_logical_writes) / (execution_count + 0.0) as money) as average_io,
io as total_io_for_query,
SUBSTRING(ST.TEXT,(QS.statement_start_offset + 2) / 2,
(CASE
WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),ST.text)) * 2
ELSE QS.statement_end_offset
END - QS.statement_start_offset) / 2) as sql_text,
qp.query_plan
from sys.dm_exec_query_stats qs
join high_io_queries fq
on fq.query_hash = qs.query_hash
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
cross apply sys.dm_exec_query_plan (qs.plan_handle) qp
outer apply sys.dm_exec_plan_attributes(qs.plan_handle) pa
where pa.attribute = 'dbid'
order by fq.io desc,
fq.query_hash,
qs.total_logical_reads + total_logical_writes desc
option (recompile)
Top 20 CPU Consumers
;with high_cpu_queries as
(
select top 20
query_hash,
sum(total_worker_time) cpuTime
from sys.dm_exec_query_stats
where query_hash <> 0x0
group by query_hash
order by sum(total_worker_time) desc
)
select @@servername as server_name,
coalesce(db_name(st.dbid), db_name(cast(pa.value AS INT)), 'Resource') AS [DatabaseName],
coalesce(object_name(ST.objectid, ST.dbid), '') as [object_name],
qs.query_hash,
qs.total_worker_time as cpu_time,
qs.execution_count,
cast(total_worker_time / (execution_count + 0.0) as money) as average_CPU_in_microseconds,
cpuTime as total_cpu_for_query,
SUBSTRING(ST.TEXT,(QS.statement_start_offset + 2) / 2,
(CASE
WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),ST.text)) * 2
ELSE QS.statement_end_offset
END - QS.statement_start_offset) / 2) as sql_text,
qp.query_plan
from sys.dm_exec_query_stats qs
join high_cpu_queries hcq
on hcq.query_hash = qs.query_hash
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
cross apply sys.dm_exec_query_plan (qs.plan_handle) qp
outer apply sys.dm_exec_plan_attributes(qs.plan_handle) pa
where pa.attribute = 'dbid'
order by hcq.cpuTime desc,
hcq.query_hash,
qs.total_worker_time desc
option (recompile)
Top 20 Queries By Elapsed Time
;with long_queries as
(
select top 20
query_hash,
sum(total_elapsed_time) elapsed_time
from sys.dm_exec_query_stats
where query_hash <> 0x0
group by query_hash
order by sum(total_elapsed_time) desc
)
select @@servername as server_name,
coalesce(db_name(st.dbid), db_name(cast(pa.value AS INT)), 'Resource') AS [DatabaseName],
coalesce(object_name(ST.objectid, ST.dbid), '') as [object_name],
qs.query_hash,
qs.total_elapsed_time,
qs.execution_count,
cast(total_elapsed_time / (execution_count + 0.0) as money) as average_duration_in_ms,
elapsed_time as total_elapsed_time_for_query,
SUBSTRING(ST.TEXT,(QS.statement_start_offset + 2) / 2,
(CASE
WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),ST.text)) * 2
ELSE QS.statement_end_offset
END - QS.statement_start_offset) / 2) as sql_text,
qp.query_plan
from sys.dm_exec_query_stats qs
join long_queries lq
on lq.query_hash = qs.query_hash
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
cross apply sys.dm_exec_query_plan (qs.plan_handle) qp
outer apply sys.dm_exec_plan_attributes(qs.plan_handle) pa
where pa.attribute = 'dbid'
order by lq.elapsed_time desc,
lq.query_hash,
qs.total_elapsed_time desc
option (recompile)
출처: http://michaeljswart.com/2013/09/my-queries-for-top-20-resource-hogs/
