with QS as(
select cp.objtype as object_type
,db_name(st.dbid)as [database]
,object_schema_name(st.objectid,st.dbid)as [schema]
,object_name(st.objectid,st.dbid)as [object]
,convert(char(16),qs.creation_time,120)as plan_creation
,convert(char(16),qs.last_execution_time,120)as last_execution
,qs.plan_generation_num
,qs.execution_count
,qs.total_worker_time
,qs.total_physical_reads
,qs.total_logical_writes
,qs.total_logical_reads
,qs.total_elapsed_time/(1000000*qs.execution_count)as avg_elapesd_seconds
,qs.total_worker_time/qs.execution_count as avg_cpu_cost
,qs.total_logical_reads/qs.execution_count as avg_logical_reads
,qs.total_logical_writes/qs.execution_count as avg_logical_writes
,qs.total_physical_reads/qs.execution_count as avg_physical_reads
,st.text
,qp.query_plan
from sys.dm_exec_query_stats qs
join sys.dm_exec_cached_plans cp on cp.plan_handle=qs.plan_handle
cross apply sys.dm_exec_sql_text(sql_handle)as st
cross apply sys.dm_exec_query_plan(qs.plan_handle)as qp
where 1=1
--and cp.objtype='Proc' --对象类型
--and db_name(st.dbid)='GprsRun' --数据库
--and st.text not like '%时间%' and st.text not like '%@queryStr%' --查询字符串
--and qs.execution_count>100 --执行次数
--and qs.total_worker_time>100 --cpu总时间
--and qs.total_physical_reads>100 --物理读次数
--and qs.total_logical_writes>100 --逻辑写次数
--and qs.total_logical_reads>100 --逻辑读次数
)
select *, '执行次数最多的' type from (select top 5 * from QS order by execution_count desc)a --执行次数最多的
union all
select *, '执行时间最长的' type from (select top 5 * from QS order by total_worker_time desc)a --执行时间最长的
union all
select *, '物理读次数最多的' type from (select top 5 * from QS order by total_physical_reads desc)a --物理读次数最多的
union all
select *, '逻辑写次数最多的' type from (select top 5 * from QS order by total_logical_writes desc)a --逻辑写次数最多的
union all
select *, '逻辑读次数最多的' type from (select top 5 * from QS order by total_logical_reads desc)a --逻辑读次数最多的
union all
select *, '平均cpu时间最长的' type from (select top 5 * from QS order by avg_cpu_cost desc)a --平均cpu时间最长的
union all
select *, '平均逻辑读最多的' type from (select top 5 * from QS order by avg_logical_reads desc)a --平均逻辑读最多的
union all
select *, '平均逻辑写最多的' type from (select top 5 * from QS order by avg_logical_writes desc)a --平均逻辑写最多的
union all
select *, '平均物理写最多的' type from (select top 5 * from QS order by avg_physical_reads desc)a --平均物理写最多的
order by text