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

本系列属于 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%部分。





  • 1
    点赞
  • 7
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页
评论

打赏作者

發糞塗牆

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值