SQL执行中占CPU资源最多的前10条查询

select top 20
 total_worker_time/execution_count as avg_cpu_cost,plan_handle,
execution_count,
(select substring(text,statement_start_offset/2+1,
(case when statement_end_offset=-1
then len(convert(nvarchar(max),text))*2
else statement_end_offset
end - statement_start_offset)/2)
from sys.dm_exec_sql_text(sql_handle)) as query_text
from sys.dm_exec_query_stats
order by [avg_cpu_cost] desc

找出工作负荷中运行最频繁的查询
select top 10 total_worker_time,plan_handle,execution_count,
   (select substring(text,statement_start_offset /2 +1,
       (case when statement_end_offset = -1
          then len(convert(nvarchar(max),text))*2
          else statement_end_offset
        end - statement_start_offset)/2)
      from sys.dm_exec_sql_text(sql_handle)) as query_text
   from sys.dm_exec_query_stats
   order by execution_count desc

找到被编译得最多的前10位查询计划
select top 10 plan_generation_num,execution_count,
  (select substring(text,statement_start_offset /2 +1,
     (case when statement_end_offset = -1
       then len(convert(nvarchar(max),text))*2
       else statement_end_offset
      end - statement_start_offset)/2)
    from sys.dm_exec_sql_text(sql_handle)) as query_text
from sys.dm_exec_query_stats
where plan_generation_num>1
order by plan_generation_num desc