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=-1then len(convert(nvarchar(max),text))*2else statement_end_offsetend - statement_start_offset)/2)from sys.dm_exec_sql_text(sql_handle)) as query_textfrom sys.dm_exec_query_statsorder 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_textfrom sys.dm_exec_query_statswhere plan_generation_num>1order by plan_generation_num desc
来源:https://blog.csdn.net/easyboot/article/details/7623746