sqlserver对cpu主频要求_记一次SQLServer服务器CPU飙升100%的处理

某集团一台sqlserver服务器,据系统管理员反应,经常会出现CPU飙到100%而且高居不下的情况,然后应用层会出现各种等待超时。

服务器配置:windows

server 2008R2 x64 + 32GB内存 +6核CPU,业务量不算特别繁忙,按理CPU使用率不会很高。

首先确认CPU高是否确实是由SQLSERVER导致的,添加几个windows CPU计数器:Procesor:% Privileged

Time(kernel mode值),Procesor: %user time (user mode值),Procesor:

%processor time ,Process:Processor time(添加所有实例,查看sqlserver

cpu占用率)。结果%processor time 很高(正常平均应低于50%),且sqlserver使用率占用绝大部分。

一般来说,sqlserver对

cpu的使用相对于内存、I/O等资源来说,应该算是较少的。CPU的使用主要是用于编译、重编译、sort、aggregation、表join(nested

loop,hash

match等)。可以通过profiler抓取一段trace,基本没有多少编译和重编译存在(profiler里cache

insert和cache miss基本没有),那么很有可能是某些sql语句导致CPU消耗很高。再通过profiler抓取一些CPU

time比较高的sql语句,有时候CPU使用率到达90%时,使用profiler会使服务器负载更高,我们可以用下面sql抓取一段时间内cpu消耗比较高的语句:

SELECT TOP 10

execution_count as [Number Of Executions],

total_worker_time/execution_count as [Average CPU Time],

total_elapsed_time/execution_count as [Average Elapsed Time],

(

SELECT

SUBSTRING(text,statement_start_offset/2,

(CASE WHEN

statement_end_offset = -1

THEN LEN(CONVERT(nvarchar(max),text)) *2

ELSE statement_end_offset END -

statement_start_offset) /2)

FROM

sys.dm_exec_sql_text(sql_handle)

) as

query_text

FROM

sys.dm_exec_query_stats

ORDER BY [Average

CPU Time] DESC;

抓取到的语句结构基本一致,在业务非繁忙期把sql单独拉出来测一下,看一下cpu时间和io情况:

cpu时间很高,实际数据获取不到32s,cpu时间占用91s(触发了并行运算),同时光某一张表的扫描达到400W次,其余的扫描我在此没截图;

再看看执行计划

cost最大的是在11行的clustered

scan,结合抓取到的CPU高的sql语句来看,发现cost最大的消耗全部都是基于某个筛选条件 ——summary like

'%某某某%',问题原因找到了,那么为什么会出现以及怎么解决就是后面要做的了。

sql

server基于like的模糊查询很特别,像上述那种like'%%',前后都有通配符的情况,不管该字段有没有索引,走的都是table

scan或clustered index

scan,效率非常低(该生产环境表数据量在1000W行左右,全表扫描性能消耗很大)!如果是只是后面通配符如 like

'abc%',且有合适索引是可以走index

seek的(实际生产环境会更复杂,有基于cost的算法有时也会做全表扫描)。解决办法只能从两个方面入手:1.创建合适的索引,更改模糊查询匹配为后匹配(经测试cpu时间会降低10-20倍);2.创建全文索引,全文索引类似于搜索引擎功能,它不同于基于B+树或hash索引的普通索引,性能上优于模糊查询,不做详细介绍,下面是一个1万行小数据量利用模糊查询和全文索引的一个小测试,数据量越大性能差别越大:

模糊查询:

利用全文索引:

当然上述两种方式是比较理想的情况,比如说建立全文索引的表必须要有唯一键,否则全文索引也不可行;又比如应用层就是想用到前后置的模糊查询‘%***%’,那想做优化也无能为力。换个思路,从另一面也能达到同样的目的。

上述我们所说的几个昂贵的查询语句,都同时用到了并行查询,且在排查出这些情况的同时,发现系统等待事件上有大量的CXPACKET

CXPACKET等待产生机制:当为SQL查询创建一个并行操作时,会有多个线程去执行这个查询。每个查询处理不同的数据集或行集。因为某些原因,一个或多个线程滞后,而产生了CXPACKET等待状态。组织线程必须等待所有线程完成处理才能进行下一步。由于组织线程等待缓慢的线程完成处理所产生的等待,就叫CXPACKET等待。CXPACKET等待事件要辩证来看,并不是所有的CXPACKET等待类型都是不好的事情。

如果你在任何查询上禁止此种等待,那么查询也许会变慢,因为不能为它执行并行操作。一般来说:

OLTP:

在纯OLTP系统上,它的事务较短,查询也不长,但是通常很快速。设置“Maximum degree of

Parallelism”(MAXDOP)为1,禁用并行查询,降低数据库引擎开销;

OLAP或DataWarehouse:在纯OLAP系统上,因为查询执行时间一般较长,建议设置“Maximum degree of

Parallelism”(MAXDOP)为0,由SQL server自动配置最大并行度,

这样大多数查询将会利用并行处理,执行时间较长的查询也会受益于多处理器并发而提高性能。

OLAP&OLTP混合系统,这样的环境比较复杂,就像上面讲的那个案例,也是基于这种系统,我们必须找到“并行查询阀值”和“最大并行度”之间的平衡点。测试下来发现把并行查询阀值提升到18,且最大并行度设置为3,系统运行很好,既不影响运行速度又不会使CPU过高。基于这种设置,那么比较简单的查询不会启用并行查询,降低CPU开销,当预估cost大于18的比较复杂报表查询,那么会启用并行查询,提升查询速度,同时这意味着只会使用cpu的3个核(6核)来做并行运算,不会使cpu使用过高。这么一修改下来,cpu终于降下来了,没报表在跑的话基本在10%左右,有报表在跑也不会很高,在40%~50%左右徘徊。

EXEC sys.sp_configure N'show advanced options', N'1'

GO

RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure N'cost threshold for parallelism', N'18'

GO

EXEC sys.sp_configure N'max degree of parallelism', N'3'

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'show advanced options', N'0'

GO

RECONFIGURE WITH OVERRIDE

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

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

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

打赏作者

鸳鸯蝴蝶派

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

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

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

打赏作者

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

抵扣说明:

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

余额充值