SQL SERVER CPU性能分析

什么原因会导致CPU性能问题？

（1）大量消耗CPU的运算，如哈希运算，排序运算。

（2）执行计划的编译和重编译。

（3）缺乏索引的大量IO操作也会引起额外的CPU消耗。

（4）并行操作。

等等。。。。

具体手段

(1)几个重要的等待，先确定服务器级别的等待。

SELECT wait_type,
       Sum(signal_wait_time_ms) AS TotalSignalWaitTime
FROM   sys.dm_os_wait_stats
GROUP  BY wait_type
ORDER  BY 2 DESC

CXPACKET等待:等待多个线程同步，会出现此等待。

SOS_SCHEDULER_YIELD：放弃CPU,并等待继续执行。

如果发现大量CXPACKET等待，可能由于并行造成，在OLTP系统，可以考虑降低并行度。

如果发现大量SOS_SCHEDULER_YIELD等待，可能说明你的CPU存在瓶颈。

（2）几个重要的计数器

sql statistics: sql compilations/sec     每秒的查询编译次数

sql statistics: sql recompilations/sec  每秒的查询重编译次数

如果发现以上两个性能计数器的值非常高，可以CPU性能问题有编译造成。可以采用存储过程封装T-SQL语句，参数化查询，紧急时可以开启数据强制参数化。

（3）语句级别处理

SELECT TOP 10 total_worker_time / execution_count  AS [Avg CPU Time],
              (SELECT Substring(text, statement_start_offset / 2, (
                              CASE  WHEN statement_end_offset = -1 THEN Len(
                              CONVERT(NVARCHAR(max), text)) *  2
                              ELSE statement_end_offset
                              END
                              - statement_start_offset ) / 2
               )
               FROM   sys.Dm_exec_sql_text(sql_handle)) AS query_text, *
FROM   sys.dm_exec_query_stats
ORDER  BY [Avg CPU Time] DESC

select top 10 
    (total_logical_reads/execution_count) as avg_logical_reads,
    (total_logical_writes/execution_count) as avg_logical_writes,
    (total_physical_reads/execution_count) as avg_phys_reads,
     Execution_count, 
    statement_start_offset as stmt_start_offset, 
    sql_handle, 
    plan_handle
from sys.dm_exec_query_stats  
order by  (total_logical_reads + total_logical_writes) Desc

 

使用该查询可以查询CPU和IO消耗最高的10个语句，可以逐一分析，是否可以更改写法或者适当添加索引，降低CPU使用率（一般高CPU和高IO关联，所以可以想办法先降低IO，直观的处理，就是减少语句的逻辑读）。

（四）总结

CPU优化是一个长期的过程，需要定义将当前的性能计数器和基线对比，尽早的发现问题，CPU出现性能问题，有时候也不一定代表就是CPU出现瓶颈了，可能预示其他硬件出现故障了，或者索引被意外删除了，网站受到攻击等等，所以CPU监控至关重要。