关于CPU的资源消耗基本来自于两个方面，首先是低效率的查询计划，再次是过度编译和重编译。
过度编译和重编译，SQL语句的编译和重编译都是CPU密集的活动，发生大量的重编译，则CPU利用率会增加。所以对SQL Server在CPU上的关注会集中在这些SQL统计的编译/重编译计数器上。
▲
下面语句。
SELECT top 25
qt.text,
qs.plan_generation_num,
qs.execution_count,
dbid,
objectid
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt
WHERE plan_generation_num >1
ORDER BY qs.plan_generation_num
qt.text,
qs.plan_generation_num,
qs.execution_count,
dbid,
objectid
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt
WHERE plan_generation_num >1
ORDER BY qs.plan_generation_num
SQL Server中，我们也可以确定平均或累计占用CPU时间最多的查询，SQL Server优化器是基于成本的，通过该查询语句的定位，再进一步分析，是统计信息问题还是索引和连接难题。
SELECT top 50
qt.text AS SQL_text ,
SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
SUM(qs.total_worker_time)/SUM(qs.execution_count) AS avg_cpu_time,
COUNT(*) AS number_of_statements
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY qt.text
ORDER BY total_cpu_time DESC --统计总的CPU时间
--ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间
qt.text AS SQL_text ,
SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
SUM(qs.total_worker_time)/SUM(qs.execution_count) AS avg_cpu_time,
COUNT(*) AS number_of_statements
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY qt.text
ORDER BY total_cpu_time DESC --统计总的CPU时间
--ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间
以下为SQL Server 2008关于CPU的一些系统视图。
▲
dm_os_nodes提供了一个名为 SQL OS 的内部组件可创建模拟硬件处理器位置的节点结构。
dm_os_schedulers 对于 SQL Server(每个计划程序都映射到其中的单个处理器)中的每个计划程序，相应地返回一行。使用此视图可以监视计划程序的情况或标识失控任务
dm_os_workers 则对于系统中的每个工作线程，相应地返回一行。
dm_os_threads 对于系统中的所有SQLOS工作线程，相应地返回一行。
sys.dm_os_tasks 为 SQL Server 实例中的每个活动任务返回一行。
计算可运行状态下的工作进程数量，来观察CPU压力
SELECT COUNT(*) as workers_waiting_for_cpu,s.scheduler_id
FROM sys.dm_os_workers AS o
INNER JOIN sys.dm_os_schedulers AS s
ON o.scheduler_address=s.scheduler_address
AND s.scheduler_id<255
WHERE o.state='RUNNABLE'
GROUP BY s.scheduler_id
FROM sys.dm_os_workers AS o
INNER JOIN sys.dm_os_schedulers AS s
ON o.scheduler_address=s.scheduler_address
AND s.scheduler_id<255
WHERE o.state='RUNNABLE'
GROUP BY s.scheduler_id
也可以查找用户会话和操作系统线程的对照关系
SELECT STasks.session_id, SThreads.os_thread_id
FROM sys.dm_os_tasks AS STasks
INNER JOIN sys.dm_os_threads AS SThreads
ON STasks.worker_address = SThreads.worker_address
WHERE STasks.session_id IS NOT NULL
ORDER BY STasks.session_id;
FROM sys.dm_os_tasks AS STasks
INNER JOIN sys.dm_os_threads AS SThreads
ON STasks.worker_address = SThreads.worker_address
WHERE STasks.session_id IS NOT NULL
ORDER BY STasks.session_id;
