什么原因会导致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监控至关重要。