CPU高时的处理

--使用CPU最多的前 50 条语句
SELECT q.dbid,
	DB_NAME(q.dbid) AS dbName,
	q.objectid, 
	OBJECT_NAME(q.objectid) AS objectName, 
	q.number, 
	q.encrypted, 
	q.[text], 
	highest_cpu_queries.*
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 DESC

--经常做重编译的存储过程
SELECT TOP 50 dbid
	, DB_NAME(dbid) AS dbName 
	, sql_text.objectid
	, OBJECT_NAME( sql_text.objectid ) AS objectName
	, sql_text.[text]
	, a.plan_generation_num 
	, a.execution_count 
	, a.sql_handle 
FROM sys.dm_exec_query_stats a
	CROSS APPLY sys.dm_exec_sql_text( sql_handle ) AS sql_text
WHERE a.plan_generation_num>1
ORDER BY a.plan_generation_num DESC

——《SQLServer2012实施与管理》