SQL Server性能优化案例分享（1）——CPU持续过高——CPU高使用率的常见原因及处理方向

https://blog.csdn.net/DBA_Huangzj/article/details/79517925

 

本系列属于 SQL Server性能优化案例分享 专题

 

    部分内容借用《SQL Server 2012实施与管理实战指南》P592，如果SQL Server错误日志里面并没有17883/17884这类错误，但是SQL Server CPU很高，那一般就是工作负载太高导致的。意味着SQL Server并没有什么大问题，但是很辛苦地在完成用户发过来的请求。

    由于CPU较于内存、阻塞、磁盘等资源瓶颈而言相对较后，所以SQL Server的CPU使用率一般不会太高，如果60~70%就已经算是很高了。那么一般集中使用CPU的资源有那些？

 

1. 编译和重编译
2. 排序和聚合计算：order by 、distinct、AVG、SUM、MAX、MIN等。
3. 表关联，特别是选择了错误的JOIN算法。

    一般要在SQL Server配置上能做手脚的根据个人经验，主要是Max Degree of Parallelism和Cost Threshold of Parallelism。

 

处理CPU高的常规思路

 

1. 确定CPU使用率到底多少，其中多少是SQL Server导致的

    可以使用计数器，也可以使用代码做一个粗略的计算获取实例性能概要信息。计数器可以考虑：

 

• Processor: % Processor Time
• Processor: % Privileged Time (Kernel Mode)
• Processor: % User Time (User Mode)
• System: Processor queue length
• Context switches/sec
• Process: % Processor time
• Process: % Privileged time
• Process: % User time

2. 检查SQL Server错误日志是否有17883/17884之类的错误

       健康的SQL Server其错误日志应该比较干净。

 

3. 找出CPU 100%时，SQL Server 正在运行什么

    这个其实有点难度，除非周期性发生。虽然有一系列的DMV可以使用，但是由于驻留内存的特性，可能会在动手之前已经重启服务器导致信息丢失。而开启sql trace又容易加大负担。这个时候，可以考虑使用扩展事件配合周期性运行查询DMV的语句并存储在实体表中的方式。后者使用类似Zabbix这类第三方监控软件。下面一些语句可供参考：

    扩展事件：GetWasteQueries

    DMV：DMVs_CpuRelated/RunningSessionInfos

4. 升级硬件

    如果在优化工作之后，确定是硬件瓶颈，那升级硬件也是不错的选择。

  

常规处理手段

1. 过度编译和重编译

过度编译和重编译通常可以对语句进行参数化，使用下面语句检查是否适合进行参数化，

 

SELECT q.query_hash
	,q.number_of_entries
	,q.distinct_plans
	,t.TEXT AS sample_query
	,p.query_plan AS sample_plan
FROM (
	SELECT TOP 20 query_hash
		,count(*) AS number_of_entries
		,count(DISTINCT query_plan_hash) AS distinct_plans
		,min(sql_handle) AS sample_sql_handle
		,min(plan_handle) AS sample_plan_handle
	FROM sys.dm_exec_query_stats
	GROUP BY query_hash
	HAVING count(*) > 1
	ORDER BY count(*) DESC
	) AS q
CROSS APPLY sys.dm_exec_sql_text(q.sample_sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(q.sample_plan_handle) AS p
GO

结果类似下图：

如果number_of_entries量很大，同时distinct_plans为1，那么这些很适合进行参数化。另外需要注意的是最佳的参数化是在客户端。

2. 非必要的重编译

    通常关注下面的计数器：一般来说，对于OLTP而言， SQL Recompilations/sec 相对于Batch Requests/sec应该很小。

     SQL Server: SQL Statistics: Batch Requests/sec
     SQL Server: SQL Statistics: SQL Compilations/sec

     SQL Server: SQL Statistics: SQL Recompilations/sec

    另外使用下面语句也可以查到缓存中前25个被重编译的语句。plan_generation_num代表查询发生重编译的次数。

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

结果类似：

    如上图的最后一行就是业务语句，而IndexOptimize是运维脚本。那么不妨检查最后一行。对于重编译，如果真有问题，那么我们通常要做的是：

 

• 检查语句内部是否有SET选项变更，可以用Profiler来检查。
• 临时表的重编译阈值会较低，检查是否使用了临时表。
• 检查是否因为统计信息的变动导致

3. 低效执行计划

    常见的CPU消耗操作有Hash和Sort操作。可以参考：SQL Server Table Spool优化 和 SQL Server Hash Warning 优化。另外可以使用下面DMV来查询累计消耗CPU最多的查询：

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

    可以改写查询，添加sys.dm_exec_cached_plans用于过滤'%Hash Match%'和'%sort%'来精确定位。也可以使用这里的代码来持续监控：HashandSortWarningwithxEvents

    当找到问题之后，一般需要做的操作有：

 

• 是否存在索引问题：不足？过多？未覆盖查询？没有用到预期的索引？
• 检查是否统计信息出现问题。
• WHERE条件是否可以改写？重点检查非SARG写法。
• 重编译语句如果无效，尝试更新统计信息。
• 如果不便于修改，可以考虑使用OPTION (OPTIMIZE FOR)提示。
• 从SQL 2008开始，提供计划冻结（plan freezing）功能，强制使用指定执行计划。

4. 查询内并行运行

    语句中的并行操作很多时候也会导致CPU过高，如果没有控制，而某个语句可能运行时占据所有CPU，这时候其他操作全部等待这个语句运行完毕才能继续操作，影响整个系统的响应速度。对于正在运行的服务器，使用下面语句来查看是否有正在进行并行操作的语句：

SELECT r.session_id
	,r.request_id
	,max(isnull(exec_context_id, 0)) AS number_of_workers
	,r.sql_handle
	,r.statement_start_offset
	,r.statement_end_offset
	,r.plan_handle
FROM sys.dm_exec_requests r
JOIN sys.dm_os_tasks t ON r.session_id = t.session_id
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE s.is_user_process = 0x1
GROUP BY r.session_id
	,r.request_id
	,r.sql_handle
	,r.plan_handle
	,r.statement_start_offset
	,r.statement_end_offset
HAVING max(isnull(exec_context_id, 0)) > 0

还可以查看符合并行操作的语句：

SELECT p.*
	,q.*
	,cp.plan_handle
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS q
WHERE cp.cacheobjtype = 'Compiled Plan'
	AND p.query_plan.value('declare  namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";max(//p:RelOp/@Parallel)','float') > 0

如果发现存在，可以考虑如下应对措施：

 

• 并行执行通常意味着运行时间过久（默认为5秒执行时间），可以对此做针对性优化
• 检查执行计划中的预估行数和实际行数的差异。重点检查是否有确实统计信息、过久没更新统计信息、非SARG写法等。

 

总结

    前面是几十年来大量专家总结的经验，在没有头绪的时候不妨参考一下。但是我建议大家一定要有几个潜意识：

 

1. 一切相关：系统是整体的，CPU高很多时候并不真的是CPU的问题，可能是内存、I/O、阻塞等导致
2. 大胆假设小心求证，验证过程要细心，尽可能排除各种干扰项，同时做好记录。
3. 2/8原则：很多因素都会引起问题的表现，应该优先处理影响80%性能的20%部分