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 

查询sql执行速度