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

-- 前20条查询次数最多的sql语句
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