SQL Server runtime consumes high CPU problem queries

Often in the forum to see the question of high CPU, Shenyang 463 plastic Surgery Hospital Procedures Simple summary.
1, first of all you have to confirm that the high CPU is not caused by the SQL Server process or other processes caused, this is easy to see the task manager directly.
2, if you see from Task Manager that the high CPU is actually caused by SQL Server.
3, if it is caused by SQL Server.

A bad execution plan for 3.1SQL server, such as missing the necessary index, caused a hash join or something. This is also divided into 2 types:
1, the statement that causes the high CPU has already executed the end, this time can use the following statement to examine.

Select
Highest_cpu_queries.plan_handle,
Highest_cpu_queries.total_worker_time,
Q.dbid,
Q.objectid,
Q.number,
Q.encrypted,
Q.[text]
From
(SELECT Top 50
Qs.plan_handle,
Qs.total_worker_time
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

Explain sys.dm_exec_query_stats:
Sys.dm_exec_query_stats returns aggregate performance statistics for the cached query plan. Each query plan corresponds to a row in that view, and the lifetime of the row is associated with the plan itself. When you delete a plan from the cache, the corresponding row is also removed from the view. http://www.hengnay.com/
C.v9/ms. Sqlsvr.v9.zh-chs/tsqlref9/html/eb7b58b8-3508-4114-97c2-d877bcb12964.htm

3.1.2, the statement that causes the high CPU is running, this time can be checked with the following statement.

SELECT St.text, Qp.query_plan, rq.*
From sys.dm_exec_requests RQ cross APPLY sys.dm_exec_sql_text (Rq.sql_handle) as St
Cross APPLY sys.dm_exec_query_plan (rq.plan_handle) as QP ORDER by RQ. Cpu_time desc

3.2 Opens the SQL Profiler.
You can see sp_trace_getdata this SP is running through 3.1.2.

3.3 Excessive compilation and re-compilation.
The following SQL can be used to check

Select Top 25
Sql_text.text,
Sql_handle,
Plan_generation_num,
Execution_count,
dbid
Objectid
From
Sys.dm_exec_query_stats A
Cross apply Sys.dm_exec_sql_text (sql_handle) as Sql_text
where
Plan_generation_num >1
ORDER BY plan_generation_num Desc

3.4 A system process caused by, say, Ghost cleanup, Lazy writer and so on. Can be checked through the select * from sys.sysprocesses where spid<51.

Cause the specific cause of the high CPU exactly what is caused, and then the remedy.

SQL Server runtime consumes high CPU problem queries