sql server 根据执行计划查询耗时操作
时间：2021-07-01 10:21:17
帮助过：0人阅读
with QS
as(
2 select cp.objtype
as object_type,
/*类型*/
3 db_name(st.dbid)
as [database],
/*数据库*/
4 object_schema_name(st.objectid,st.dbid)
as [schema],
/*架构*/
5 object_name(st.objectid,st.dbid)
as [object],
/*对象名*/
6 convert(
char(
16),qs.creation_time,
120)
as plan_creation,
/*计划生成时间*/
7 convert(
char(
16),qs.last_execution_time,
120)
as last_execution,
/*最后执行时间*/
8 qs.plan_generation_num,
9 qs.execution_count,
/*执行次数*/
10 qs.total_elapsed_time
/(
1000*qs.execution_count)
as avg_elapesd_seconds,
/*总花费时间ms*/
11 qs.total_worker_time
/(
1000*qs.execution_count)
as avg_cpu_cost,
/*平均cpu耗时ms*/
12 qs.total_logical_reads
/qs.execution_count
as avg_logical_reads,
/*平均逻辑读*/
13 qs.total_logical_writes
/qs.execution_count
as avg_logical_writes,
/*平均逻辑写*/
14 qs.total_physical_reads
/qs.execution_count
as avg_physical_reads,
/*平均屋里读*/
15 st.
text,
/*执行文本*/
16 qp.query_plan
/*执行计划*/
17 from sys.dm_exec_query_stats qs
18 join sys.dm_exec_cached_plans cp
on cp.plan_handle
=qs.plan_handle
19 cross apply sys.dm_exec_sql_text(sql_handle)
as st
20 cross apply sys.dm_exec_query_plan(qs.plan_handle)
as qp
21 )
select top 20 * from QS
22 where text like ‘%%‘
23 --and object_type=‘Proc‘
24 --and avg_logical_reads>200
25 and execution_count
>100 /*执行次数*/
26 --and last_execution_time>dateadd(mi,-10,getdate())
27 and last_execution
>= ‘2016-05-01 00:00:00.000‘ /*最后执行时间*/
28 order by avg_cpu_cost
desc
29
sql server 根据执行计划查询耗时操作
标签：
