【等待优化】sql server CXPACKET 等待 导致 CPU飙高、CPU100%

CXPACKET 已经成为所有等待类型中最常见的一种了。我通常会在多CPU系统的前五位等待类型统计中看见。

【1】CXPACKET 的基本解决策略

  联机丛书:

    当尝试同步查询处理器交换迭代器时出现。如果针对该等待类型的争用成为问题时,可以考虑降低并行度。

【1.1】CXPACKET 解释

     当为SQL查询创建一个并行操作时,会有多个线程去执行这个查询。每个查询处理不同的数据集或行集。

      因为某些原因,一个或多个线程滞后,而产生了CXPACKET等待状态。

      有一个组织/协调(organizer/coordinator)线程(Thread 0),它需要等待所有线程完成并聚合数据来呈现给客户端。

      组织线程必须等待所有线程完成处理才能进行下一步。由于组织线程等待缓慢的线程完成处理所产生的等待,就叫CXPACKET等待。

      请注意,并不是所有的CXPACKET等待类型都是不好的事情。你也许会遇某个CXPACKET等待是完全有意义的案例,有时它也是不可避免的。

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

【1.2】在OLTP上解决CXPACKET的办法——调整并行度

 

CXPACKET 这个等待可以简单理解成CPU相关的等待,主要发生在并行计划中。由于并行计划需要协同多个task同时工作,那么“协同”分配等等操作的时候出现的就是这个等待。

如果 CXPACKET 在你系统中是最为严重的等待,这时候一般的表现是你的CPU很高。

  

 

解决方案:适当调整并行度

 

  

 

一般建议系统如果超过32个CPU 那么设置成8或者4,如果系统中都是特别短小且频繁的语句建议设置成1(取消语句并行,要慎重真的符合你的场景才好)

    并行开销的阀值(‘Cost Threshold for Parallelism),主要控制SQL优化器何时选用并行计划,建议默认值,此值设置的越小优化器越容易选择并行计划。

    并行度(Max Degree of Parallelism)的设置是针对实例级别的设置(2016中可以对单独数据库设置)

【1.3】Data-warehousing /Reporting server 上的CXPACKET

   Data-warehousing /Reporting server: 因为查询执行时间一般较长,建议设置“Maximum degree of Parallelism”(MAXDOP)为0。

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

 

【1.4】Mixed System (OLTP& OLAP)

这样环境会是一个挑战,必须找到正确的平衡点。我采取了非常简单的方法。

         我设置“Maximum degree of Parallelism”(MAXDOP)为2,这样意味着查询仍会使用并行操作但是仅利用2颗CPU。

       然而,我把“并行查询阀值”设置为较高的值,这样的话,不是所有的查询都有资格使用并行,除了那些查询成本较高的查询。

       在一个即有OLTP查询又有报表服务器的系统上,我发现这样做运行得很好。

         在这里我将会设置“‘Cost Threshold for Parallelism’”为25(如图)。你可以选择任何值。但你只能通过在系统上做实验来找到合适的值。

        在下面的脚本中,我设置“Max Degree of Parallelism”为2,这样的话,那些具有较高成本的查询(这里是25),将会在2颗CPU上执行并行查询。

        同时,不管服务器有多少颗CPU,查询只会选择两颗CPU来执行。  

【2】CPU飙高,但没有等待,大多因为语句造成的问题

大多是因为SQL语句引起的,比如频繁短快的表扫描

【2.1】当前进程信息

(1)常见DMV

  系统进程:select * from sys.sysprocesses

  用户请求:select * from sys.dm_exec_requests

  会话进程:select * from sys.dm_exec_sessions

  等待进程:select * from sys.dm_os_wait_stats

(2)详细语句

--包含批处理中当前运行到的SQL(child_Query)
select status,start_time,command,percent_complete,wait_type,text as parent_Query,
[child_Query] = SUBSTRING(qt.text,r.statement_start_offset / 2,
                                           ( CASE WHEN r.statement_end_offset = -1
                                                  THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))* 2
                                                  ELSE r.statement_end_offset
                                             END - r.statement_start_offset )
                                           / 2) ,
session_id,blocking_session_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) qt   

--详细版,查看CPU消耗最多的10个语句
SELECT TOP 10
[cpu_time],
[session_id],
[request_id],
[start_time] AS '开始时间',
[status] AS '状态',
[command] AS '命令',
dest.[text] AS 'sql语句', 
DB_NAME([database_id]) AS '数据库名',
[blocking_session_id] AS '正在阻塞其他会话的会话ID',
[wait_type] AS '等待资源类型',
[wait_time] AS '等待时间',
[wait_resource] AS '等待的资源',
[reads] AS '物理读次数',
[writes] AS '写次数',
[logical_reads] AS '逻辑读次数',
[row_count] AS '返回结果行数'
FROM sys.[dm_exec_requests] AS der 
CROSS APPLY 
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
ORDER BY [cpu_time] DESC

 

【2.2】查看CPU的调度核数与worker信息

--查看CPU数和user scheduler数目

    SELECT cpu_count,scheduler_count FROM sys.dm_os_sys_info

--查看最大工作线程数

    SELECT max_workers_count FROM sys.dm_os_sys_info

  查看机器上的所有schedulers包括user 和system
  通过下面语句可以看到worker是否用完,当达到最大线程数的时候就要检查blocking了

对照下面这个表
各种CPU和SQLSERVER版本组合自动配置的最大工作线程数
CPU数                 32位计算机                        64位计算机
<=4                     256                                   512
  8                        288                                   576
 16                       352                                   704
 32                       480                                   960

--查看每个CPU schedulers 的使用及任务分配情况
SELECT
scheduler_address,
scheduler_id,
cpu_id,
status,
current_tasks_count,
current_workers_count,active_workers_count
FROM sys.dm_os_schedulers

如下图,核数与查询信息是对应好的。

  

 

 

【2.3】查看等待信息

如果SQLSERVER存在要等待的资源,那么执行下面语句就会显示出会话中有多少个worker在等待

结合[sys].[dm_os_wait_stats]视图,如果当前SQLSERVER里面没有任何等待资源,那么下面的SQL语句不会显示任何结果

SELECT TOP 10
 [session_id],
 [request_id],
 [start_time] AS '开始时间',
 [status] AS '状态',
 [command] AS '命令',
 dest.[text] AS 'sql语句', 
 DB_NAME([database_id]) AS '数据库名',
 [blocking_session_id] AS '正在阻塞其他会话的会话ID',
 der.[wait_type] AS '等待资源类型',
 [wait_time] AS '等待时间',
 [wait_resource] AS '等待的资源',
 [dows].[waiting_tasks_count] AS '当前正在进行等待的任务数',
 [reads] AS '物理读次数',
 [writes] AS '写次数',
 [logical_reads] AS '逻辑读次数',
 [row_count] AS '返回结果行数'
 FROM sys.[dm_exec_requests] AS der 
 INNER JOIN [sys].[dm_os_wait_stats] AS dows 
 ON der.[wait_type]=[dows].[wait_type]
 CROSS APPLY 
 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
 WHERE [session_id]>50  
 ORDER BY [cpu_time] DESC

 

【3】总结

(1)从多次历史经验来看,如果CPU负载持续很高,但内存和IO都还好的话,这种情况下,首先想到的一定是索引问题,十有八九错不了。

(2)如果有众多 CXPACKET 等待,大多是并行度问题

 

参考:https://www.cnblogs.com/gered/p/9487793.html

posted @ 2020-03-21 14:54  郭大侠1  阅读(2374)  评论(0编辑  收藏  举报