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