SqlServer CPU篇及常用性能阈值

一、 常见的CPU报错

遇到这些错误时，SqlServer并发响应速度会受到影响，甚至整个服务响应都非常慢，影响业务。

 

1. 17883报错——某个scheduler可能有问题

如果sqlserver检查发现某个scheduler上有个task运行超过60s都没有做过Yielding，sqlserver就会在错误日志中打印有个17883错误。如果是启动以来第一次遇到17883错误，sqlserver还会生成一个mini-dump。

 

2. 17884和17888报错——所有scheduler都可能有问题

可能原因：

• 所有scheduler都遇到17883错误
• 所有worker都被某个关键资源阻塞
• 所有worker都在运行一个很长时间才能返回的语句

由于17884和17888报错是多个scheduler上发生的问题，对sqlserver影响会比17883更大，通常可以从sys.processes视图看出端倪。

 

3. 内存转储文件

当sqlserver发生重大意外时（服务无响应、崩溃等），会自动产生一个或几个内存转储文件（dump），通常需要安装WinDbg工具进行分析。

所谓的dump就是当时内存状态的一个快照。如果不做任何配置，里面就是一些0101的二进制值，为了能将某个内存地址和某个函数或变量对应起来，还需要一个叫做符号表的东西。

分析过程非常复杂，这里先不记录了。

 

二、 服务器CPU高问题分析

 

1. 消耗CPU资源较多的操作

• 编译和重编译
• 排序和聚合操作
• 表连接操作
• 加解密
• 压缩解压缩

 

2. CPU相关参数（sp_configure）

英文版

中文版

1）Boost sqlserver Priority

默认为0，设置为1可提高sqlserver进程在windows上的优先级，但并不推荐改，因为这会打乱windows正常进程调度，当sqlserver出现cpu 100%时会影响windows上其他进程。

 

2）Lightweight Pooling

设置sqlserver是否使用纤程（Fiber）技术，默认关闭。开启后sqlserver会在要使用线程时使用纤程。纤程调度技术能降低系统context switch数目，对一些应用来讲，能够提高性能。

但是sqlserver本身已使用scheduler技术在sqlserver级降低了context switch需求，在实际应用中，很少遇到使用纤程提高系统性能的案例。纤程调度技术毕竟使得管理更加复杂，因此该设置一般很少修改。

 

3）Affinity Mask

设置sqlserver固定使用某几个CPU。当服务器上除了sqlserver还要运行其他重要应用服务时，可以设置该参数限定sqlserver只使用指定CPU，防止sqlserver占用过量CPU资源影响其他应用。

还有一种情况是，当DBA怀疑sqlserver的scheduler经常在不同CPU间切换导致影响性能时，也可以设置该参数，让sqlserver的每个scheduler固定在CPU上，看能否提升性能。

 

4）Max Worker Threads 

sqlserver进程最大线程数，默认是0，即允许sqlserver在启动时自动配置工作线程数。对大多数系统而言，该设置为最佳实践，不需修改。

 

5）Cost Threshold of Parallelism

当一个sql串行执行的cost超过该值设置时，sqlserver会使用并行执行。默认是5，一般不需修改。这个值越大，sqlserver越倾向于串行执行计划。

 

6）Max Degree of Parallelism

当需要使用并行执行时，该参数决定sqlserver最多使用几个线程来并行执行一条指令。默认是0，即无限制；设置为1，则始终只能串行。建议设置值如下：

 

3. CPU高问题分析思路

• 确实服务器CPU使用率是多少，其中sqlserver占了多少
• 检查错误日志中是否有17883、17884、17888报错或访问越界报错
• 找出此时sqlserver在运行的最耗CPU的sql

SELECT TOP 50
total_worker_time/execution_count AS [Avg CPU 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 total_worker_time DESC;

• 降低系统负载或升级硬件

 

三、 OLTP及OLAP常用性能阈值

 

1. OLTP

 

2. OLAP

参考：《sqlserver 2012 实施与管理实战指南》