sql查看cpu使用最多的和經常重編譯的

---使用cpu最多的前50名
select  highest_cpu_queries.*,q.dbid,
q.objectid,q.number,q.encrypted,q.text
from (
select top 50 qs.* from sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time

---找到最經常做重編譯的存儲過程
select top 25 sql_text.text,sql_handle,plan_generation_num,execution_count,dbid,objectid
from sys.dm_exec_query_stats as a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num>1
order by plan_generation_num desc