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