4.3 CPU性能侦测

4.3 CPU性能侦测
CPU的性能问题在日常使用中很常见，但是表现形式几乎只有一种，就是在任务管理
器中看到CPU的使用率居高不下。这时候需要侦测问题的根源并选择对应的处理方式。
4 .3 .1 侦测CPU压力
侦测CPU问题通常可以使用性能监视器、SQL Trace和DMVs等。下面简要介绍一下。
1 .性能监视器
性能监视器（PerfMon)可能是侦测问题的第一个工具（任务管理器不算，因为它不能 找到存在什么问题)。这是一个Windows上的工具，可用于分离问题，找到究竟是由操作系 统本身导致的还是由SQL Server导致的问题。比如对于CPU高利用率，在使用性能监视器 时可以重点关注下面的计数器。
□ Processor/ %Privileged Time: 花费在执行Windows内核命令上的处理器时间的百分比。 □ Processor/ %User Time：花费在处理应用程序如SQL Server上的处理器时间百分比。 □ Process (sqlservr.exe)/ %Processor Time：每个处理器上所有进程的总处理时间。
然后，把上面的计数器加人监视器并进行监控，如图4-2所示。

 

除了上面3 个计数器以外，还可以用SQL Statistics计数器来监控。
□ SQL Server:SQL Statistics/Auto-Param Attempts/sec □ SQL Server:SQL Statistics/Failed Auto-params/sec □ SQL Server:SQL Statistics/Batch Requests/sec □ SQL Server:SQL Statistics/SQL Compilations/sec □ SQL Server:SQL Statistics/SQL Re-Compilations/sec □ SQL Server:Plan Cache/Cache hit Ratio
这 4 种计数器本身并没有好与坏的区别，但是不同情况下会有一些建议值或者提醒相
对明显的异常情况。

2. SQL Trace
可以用Profiler来生成脚本并用于SQL Trace。对于周期性发生的问题，SQL Trace非
常有用，因为作为图形化操作的Profiler—直开着并不合理，有时候反而会给已经处于压力 底下的服务器带来新一轮压力。使用SQL Trace最重要的目的是获取消耗最多CPU的查询， 这部分稍后演示。
3. DMV
相比前面两种工具，DMV更加快速和便捷，不需要进行过多的预备工作。下面是常规
步骤：
1 ) 使用sys.dm_os_wait_stats检査信号等待（在第7 章介绍)，检查是否存在CPU压力。 2 ) 根据等待类型，通过 sys.dm_os_wait_stats 和 sys.dm_os_schedulers 确定 CPU 问题
的种类。
3 ) 通过 sys.dm_exec_query_stats 和 sys.dm_exec_sql_text 找出计划缓存中 CPU 消耗最
高的查询。
4 ) 通过sys.dm os waiting tasks找到当前等待任务中CPU相关的等待类型最髙的任务。 5 ) 从 sys.dm exec requests中找到当前査询中资源使用最高的查询。

4 .3 .2 研究CPU相关的等待信息
当一个请求由于某些原因产生等待时，SQL Server会把相关信息存放在sys.dm_os_ wait_stats这个DMV中。很多第三方工具都是通过分析这个DMV中的信息进行问题侦测 的，下面来看个例子。
比如可以用下面语句检查等待类型中等待时间最长的10个类型。

SELECT TOP ( 10 )
wait_type ,
waiting_tasks_count ,
( wait_time_ms - signal_wait_time_ms ) AS resource_wait_time ,
max_wait_time_ms ,
CASE waiting_tasks_count
WHEN 0 THEN 0
ELSE wait_time_ms / waiting_tasks_count
END AS avg_wait_time
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%' -- 去除不相关的等待类型
AND wait_type NOT LIKE 'XE%'
AND wait_type NOT IN -- 去除系统类型
( 'KSOURCE_WAKEUP', 'BROKER_TASK_STOP', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',
'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE',
'DBMIRROR_EVENTS_QUEUE', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',
'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE',
'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS',
'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH' )
ORDER BY wait_time_ms DESC

通过上面的査询，可以看到自上一次SQL Server启动之后，所有非系统等待信息中总 等待时间排名最久的10个等待类型。根据这些等待类型，可以粗略地找到一个进一步查看
问题的切人点。
对于CPU压力，通常相关的等待类型有SOS_SCHEDULER_Y1ELD、CXPACKET和
CMEMTHREAD。这部分将在第7 章中详细介绍

1. SOS_SCHEDULER_YIELD
如果在sys.dm_exec_requests或者sys.dm_os_waiting_tasks中看到这个等待类型的等待
时间很长，意味着这个查询消耗了很多CPU,首要任务就是优化这个查询。
2. CXPACKET
当查询出现并行操作时，这种等待类型可能就会出现。在OLTP系统中，并行执行往
往意味着查询不够优化，需要进一步的优化。
3. CMEMTHREAD
表示等待着同步内存中的对象，一些内存对象会同时被多个线程使用，当多线程访问
时，有些对象会因为某些原因无法响应请求，导致产生这种类型的等待，不过这种类型相
对较少。