专栏首页物流IT圈性能调优之CPU

性能调优之CPU

关系型数据库严重依赖底层的硬件资源,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的任务在运行,

通常情况下,如果 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资源,对数据的插入和更新操作有负面影响。

本文分享自微信公众号 - 物流IT圈(exiter18)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-01-08

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 软件的复杂性与构造定律

    复杂性是被低估的。复杂越高,开发人员会感到不安。对其的理解认知负荷代价就越高,我们就更不快乐。真正的挑战是在构建我们的系统时要保持其有序以及工程师的生产方式。对...

    物流IT圈
  • MySQL数据库设计规范

    MySQL数据库与 Oracle、 SQL Server 等数据库相比,有其内核上的优势与劣势。我们在使用MySQL数据库的时候需要遵循一定规范,扬长避短。本规...

    物流IT圈
  • 最终一致性其实比MVCC简单

    关于NoSQL有这样一个误解: NoSQL最大的谎言是其简单,其实不是,简单意味着开发人员和运营人员需要做很多难且复杂的事情,它们最终得重复实现数据库已经实现的...

    物流IT圈
  • 云数据库MySQL CPU飙升排查流程

    在日常使用MySQL的过程中,会遇到 CPU 使用率过高甚至达到 100% 的情况。CPU飙升会导致数据库无法连接,事务无法提交等一系列问题。本文基于日常问题处...

    苏欣
  • 数据的力量,苹果教你用数据鄙视竞争对手

    鄙视竞争对手的方法有很多种,其中,使用数据无疑是比较高端大气有内涵的一种。好数据的威慑力是十分可观的,能起到“不是噱头胜似噱头”的效果——看看各知名厂商发布会后...

    小莹莹
  • 数据的力量,苹果教你用数据鄙视竞争对手

    鄙视竞争对手的方法有很多种,其中,使用数据无疑是比较高端大气有内涵的一种。好数据的威慑力是十分可观的,能起到“不是噱头胜似噱头”的效果——看看各知名厂商发布会后...

    CDA数据分析师
  • AI智能分析视频结构化平台EasyCVR在Linux版本下出现CPU暴涨后问题定位方法

    TSINGSEE青犀视频开发的视频平台都具备Windows和Linux的运行版本,可以根据需求自由选择。当遇到项目现场有流量较大的情况,大多数用户都比较担心对C...

    TSINGSEE青犀视频
  • T-SQL进阶:超越基础 Level 9:动态T-SQL代码

    Woodson
  • 主板上来了一个新邻居,CPU慌了!

    “阿Q,快别忙了,马上去一趟会议室,领导有重要事情开会”,一大早,咱们CPU厂里的总线主任就挨个到8个车间通知大家开会,神色有些凝重。

    轩辕之风
  • Scala的自定义类型标记

    Scala中有很多千奇百怪的符号标记,看起来是那么的独特,就像是一杯dry martini...好像黑夜中的萤火虫,那么耀眼,那么出众。

    程序那些事

扫码关注云+社区

领取腾讯云代金券