sql语句大全之CPU高使用率的常见原因及处理方向

当前位置:

首页 > 数据库 > sql数据库 >

sql语句大全之CPU高使用率的常见原因及处理方向

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

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

编译和重编译

排序和聚合计算：order by 、distinct、AVG、SUM、MAX、MIN等。

表关联，特别是选择了错误的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

结果类似下图：

如果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写法等。

总结

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

一切相关：系统是整体的，CPU高很多时候并不真的是CPU的问题，可能是内存、I/O、阻塞等导致

大胆假设小心求证，验证过程要细心，尽可能排除各种干扰项，同时做好记录。

2/8原则：很多因素都会引起问题的表现，应该优先处理影响80%性能的20%部分。

---------------------

作者：發糞塗牆

来源：CSDN

原文：https://blog.csdn.net/DBA_Huangzj/article/details/79517925

栏目列表