关系型数据库严重依赖底层的硬件资源, CPU 是服务器的大脑, 当 CPU 开销很高时, 内存和硬盘系统都会产生不必需要的压力. CPU 的性能问题, 直观来看, 就是任务管理器中看到的 CPU 利用率始终处于 100%, 而侦测 CPU 压力的工具, 最精确的就是性能监控器.
一, 使用性能监控器侦测 CPU 压力
性能监控器 (PerfMon) 是侦测 CPU 压力的首选工具, 对于 CPU 高利用率, 在使用性能监控器时可以重点关注下面的 3 个计数器:
Processor/ %Privileged Time: 花费在执行 Winidows 内核命令上的处理器时间的百分比
Processor/ %User Time: 花费在处理应用程序上的处理器时间的百分比
Process(sqlserver.exe)/ % Processor Time: 每个处理器所有进程的总处理时间
除了上面这 3 给计数器之外, 还可以使用 SQL Statistics 计数器来监控:
- SQL Server:SQL Statistics/Auto-Param Attempts/sec
- SQL Server:SQL Statistics/Failed Auto-params/sec
- SQL Server:SQL Statistics/Batch Requests/sec
- SQL Server:SQL Statistics/SQL Compilations/sec
- SQL Server:SQL Statistics/SQL Re-Compilations/sec
- SQL Server:Plan Cache/Cache Hit Ratio
二, 使用 DMV 侦测 CPU 压力
使用 DMV 来侦测当前系统 CPU 的压力, 常规的步骤是:
step1: 使用 sys.dm_os_wait_stats 检查等待, 查看是否存在 CPU 压力
step2: 根据等待类型, 通过 sys.dm_os_wait_stats 和 sys.dm_os_schedulers 确定 CPU 问题的种类
step3: 通过 sys.dm_exec_query_stats 和 sys.dm_exec_sql_text 找出计划缓存中 CPU 消耗最高的查询
step4: 通过 sys.dm_os_waiting_tasks 找到当前任务中 CPU 相关的等待类型中 CPU 消耗最高的任务
step5: 从 sys.dm_exec_requests 中找到当前查询中 CPU 资源使用最高的查询.
三, CPU 相关的等待
从 sys.dm_os_wait_stats 中检查等待, 对于 CPU 压力, 通常相关的等待类型是: SOS_SCHEDULER_YIELD 和 CXPACKET
1,CXPACKET
CXPACKET 是最常见的并行等待, 如果一个查询由多个线程组成, 那么只有在最慢的那个线程完成之后, 整个查询才会完成. 这就是并行查询的木桶效应, 一个木桶的容量取决于组成木桶最短的那块木条的长度.
在多 CPU 的环境中, 一个单独的查询可以使用多个线程来共同完成, 每个线程单独处理数据集的一部分. 在并行处理的过程中, 如果某个线程处于落后状态, CXPACKET 等待就会产生. 但是, 应该注意, CXPACKET 等待并不总是表示系统存在性能问题. 需要测试, 合理设置并行度阈值 (Cost Threshold for Parallelism,CTP) 和最大并发度(Max Degree of Parallelism,MDP), 这两个配置项的用途是:
CTP 是指只有查询的开销超过一定的阈值之后, 才会使用并发操作
MDP 应设置为 CPU 的内核数量, 表示最多使用多少个线程同时处理任务
出现 CXPACKET 等待的原因是:
在可变类型中, 数据的分布存在严重的倾斜, 比如某列 nvarchar 类型的数据, 有些数据的长度是几个字符, 有些的几千个字符, 对这样的数据进行查询时, 会导致某些线程执行很快, 但另一个线程执行很慢.
查询所需要的数据存放在不同的 IO 子系统中, 而这些子系统的性能又存在差异
查询所需要的数据中, 不同部分的碎片不同, 所需的 IO 也不同. IO 数量直接影响运行速度和资源开销, 从而导致查询过程中不同线程的运行速度不同.
2,SOS_SCHEDULER_YIELD
多任务等待, 多任务是指服务器同时处理多个任务, SOS_SCHEDULER_YIELD 等待类型就发生在一个任务资源放弃当前占用的资源, 让其他任务使用资源执行下去.
SQL Server 以协同模式运行, 在必要的时候, SQL Server 会让出资源给其他线程, 通常来说, 这种让步是临时的, 但是, 当长期, 大量出现这种等待的时候, 有可能意味着 CPU 存在压力, 这个时候, 可以检查 sys.dm_os_schedulers, 看看当前有多少个 runnable 的任务在运行,
- select schedule_id, current_tasks_count, runnable_task_count, work_queue_count, pending_disk_io_count
- from sys.dm_os_schedulers
- where schedule_id<255
通常情况下, 如果 runnable_task_count 字段长时间存在两位数的数值, 就意味着 CPU 可能存在压力, 无法应对当前的工作负载.
四, 常见的高 CPU 利用率的原因
下面总结了 7 个常见的高 CPU 利用率的情况.
1, 缺失索引
当没有合适的索引用于支持查询时, 一般只能通过大面积表扫描来获取所需要的信息, 这会导致 SQL Server 需要处理很多非必要的数据, 由于需要加载很多非必要的数据到内存, 这些 IO 操作需要消耗 CPU 资源, 大量数据被加载到内存也会引起内存压力, 导致计划缓存被移除, 使得 SQL Server 必须重新编译执行计划, 编译和生成执行计划也是高 CPU 开销操作.
2, 统计信息过时
SQL Server 优化器借助统计信息来预估查询开销, 如果统计信息过时, 不准确, 会导致优化器产生不合适的执行计划.
可以检查一下图形执行计划, 如果预估行数和实际行数的的差异很大, 就说明统计信息过时, 需要更新.
3, 非 SARG 查询
SARG 是 Search Argumeng 的缩写, 简单来说, 如果一个查询条件 (where,on) 能用到索引查找操作(seek index), 那么该表达式就是 SARG.
通常情况下, 对索引列使用了计算式或函数, 或者使用了 like '%str'等都会导致索引失效, 这类查询都属于非 SARG 查询.
4, 隐式转换
由于 SQL Server 无法匹配不同类型的数据, 所以需要先把数据转换为相同的类型, 才能进行匹配.
如果在实际的执行计划中出现 CONVERT_IMPLICIT 操作符, 就说明出现了类型的隐式转换.
5, 参数嗅探
参数嗅探是指在创建存储过程, 或者参数化查询的执行计划时, 根据传入的参数进行预估并生成执行计划. SQL Server 生成的执行计划对当前参数来说是最优的, 而对其他大多数参数来说, 是非常低效的. 有些时候, 针对一个查询的第一次传参, 已经产生了一个执行计划, 当后续传参时, 由于存在对应参数的数据分布等问题, 导致原有的执行计划无法高效地响应查询请求, 这就出现参数嗅探问题.
对于参数嗅探问题, 可以使用语句重编译, 编译提示 (optimize for) 等功能来避免.
6, 非参数 Ad-Hoc 查询
非参数 Ad-Hoc 查询, 是指 SQL Server 缓存了大量的只用一次的计划缓存, 造成内存资源和 CPU 资源的浪费, 可以使用存储过程, 参数化的 Ad-Hoc 查询或启用 "Optimize for Ad Hoc Workloads" 来避免.
参数化的 Ad-Hoc 查询通常是指使用 sp_executesql 来执行一段 TSQL 代码.
"针对即席工作负载进行优化" 是一个 Server 级别的性能优化选项, 用于提高包含许多临时批处理的工作负载的计划缓存的效率, 如果把该选项设置为 True, 则数据库引擎在首次编译批处理时只保留计划缓存中的一个存根, 而不是存储整个执行计划. 当再次调用该批处理时, 数据库引擎识别出该批处理在之前被执行过, 进而从计划缓存中删除该执行计划的存根, 并把完全编译的执行计划添加到计划缓存中. 当非参数化的 Ad-Hoc 查询较多时, 可以避免计划缓存存储过多的不会被复用的执行计划.
7, 压缩操作
压缩和解压缩都是 CPU 高开销的操作, 数据压缩, 备份压缩和日志流压缩通过增加 CPU 的利用率来降低 IO 子系统压力和硬盘空间压力. 数据压缩的优点是降低 IO 子系统的压力, 提高查询的性能, 其缺点是消耗 CPU 资源, 对数据的插入和更新操作有负面影响.
参考文档:
来源: https://www.cnblogs.com/ljhdo/p/5075781.html