SQL SERVER CXPACKET-Parallelism Wait Type的惯用解决方案

一.概述

 
最近我的两个库出现,出现较多的CXPACKET等待,在网上找了一下资料。其中有篇一个SQL
Server专栏作家的文章不错,也解决了我的一些疑问,就翻译在这里。

   CXPACKET是指:线程正在等待彼此完成并行处理。什么意思呢? 当sql
server发现一条指令复杂时,会决定用多个线程并行来执行,由于某些并行线程已完成工作,在等待其它并行线程来同步,这种等待就叫CXPACKET。

  翻译整理仅用于传播资讯之目的。

  为什么会有并行线程呢?  因为在sql server
里有个任务调度SCHEDULER是跟操作系统CPU个数 默认是一 一匹配的, 
我们也可能通过sp_configure来设置最大并行度,也就是Max Degree of Parallelism
(MAXDOP)。 关于调度可参考” sql server
任务调度与CPU”

  原文出处:

  并行处理的优势:
用多个线程来执行一个指令,当sql
server发现一条指令复杂时或语句中含有大数据量要处理,此时执行计划会决定用多个线程并行来执行,从而提高整体响应时间,例如一个指令读入100w条记录,
如果用一个线程做 可能需要10秒, 如果10个线程来做
可能只需要1秒,加上线程间同步时间也不过2秒。

  翻译整理:Joe.TJ

  并行处理的劣势:1是并行线程要等待同步。2是由于这10个线程全力以赴,就有10个对应的cpu,这样别的用户发过来的指令就会受到影响,甚至拿不到cpu来执行。所以对于并发度要求高的需要及时响应的,一般会建议手动设置每个指令的并行线程数。反之可以不设置Max
Degree of Parallelism由系统默认去并行或者设少一点并行度。

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

   1.1 
 查询 CXPACKET的等待

 

  借助上一次性能调优的资源等待统计图,会发现等待时间最长的就是CXPACKET类型。

  联机丛书:

  欧洲杯竞猜平台 1

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

 1.2  模拟CXPACKET的并行处理 

 CXPACKET 解释:

     下面是一个分组查询,在执行计划中看到,以采用了并行处理

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

 欧洲杯竞猜平台 2

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

  下面是通过sys.dm_os_waiting_tasks 来查看该语句的task任务。

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

欧洲杯竞猜平台 3

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

欧洲杯竞猜平台 , 或采用sys.sysprocesses查看结果。下面一个举例中
会话session是SPID 56。 这里我们明显看到,SQL Server使用了5个线程kpid
来执行这个query。

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

    欧洲杯竞猜平台 4

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

 1.3  分析CXPACKET的并行处理

 减少CXPACKET等待:

  由于并行的原因而从出现了Expacket
的等待。是否并行的执行,通过执行计划可以查看到,下面是查询大表中的数据,sql
server自动加启了并行执行。

 
  
我们不能抛开服务器负载类型来讨论减少CXPACKET等待。

   欧洲杯竞猜平台 5

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

  欧洲杯竞猜平台 6

 
       
这样做可以确保查询永远不必使用并行方式运行,并且不会导致更多的数据库引擎开销。 
       

  共调用了32个线程来并行查询

EXEC sys.sp_configure N'cost threshold for parallelism', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

  欧洲杯竞猜平台 7欧洲杯竞猜平台 8

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

1.4  控制CXPACKET并行度

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

   有时后台执行的sql, 对于并发度要求不高, 
不需要及时响应的,一般会建议手动设置每个指令的并行线程数。

EXEC sys.sp_configure N'cost threshold for parallelism', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

  欧洲杯竞猜平台 9

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

    设置可以发现并行度就二个线程。

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

    欧洲杯竞猜平台 10

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

1.5  CXPACKET资源等待总结

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

 (1)
通过实例级别查出CXPACKET的等待时间包括总等时间,平均等待时间,最大等待时间。

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

 (2) 查看并行的前十条语句
(这种查询不建议使用,因为条件是查找含有并行parallel的执行计划,查询响应很慢)。

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

SELECT TOP 10
        p.* ,
        q.* ,
        qs.* ,
        cp.plan_handle
FROM    sys.dm_exec_cached_plans cp
        CROSS APPLY sys.Dm_exec_query_plan(cp.plan_handle) p
        CROSS APPLY sys.Dm_exec_sql_text(cp.plan_handle) AS q
        JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handle
WHERE   cp.cacheobjtype = 'Compiled Plan'
        AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/SQL Server/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0
OPTION  ( MAXDOP 1 )

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

 (3) 找出cpu和i/o耗性能最高的sql语句, 查看执行计划是否有并行处理。

EXEC sys.sp_configure N'cost threshold for parallelism', N'25'
GO
EXEC sys.sp_configure N'max degree of parallelism', N'2'
GO
RECONFIGURE WITH OVERRIDE
GO

 (4)  找出程序中感觉复杂的sql语句,查看执行计划。

欧洲杯竞猜平台 11

 (5)  避免或减少白天执行频繁复杂sql,优化sql 建好索引。

 

 (6)  当执行计划发现并不需要用并行执行时,强制sql 使用OPTION ( MAXDOP x)
也不会采用并行执行。

最后考虑调整并行度的开销阈值或降低并行度。

  设置sql语句级的MAXDOP。如果MAXDOP=1的话,使得一个BATCH只对应一个TASK。如果没有设置MAXDOP,一个BATCH可能会产生多个TASKS,那么TASK之间的协调,等待等等,将是很大的开销。把MAXDOP设小,能同时减少WORKER的使用量。所以,如果我们看到等待类型为CXPACKET的话,那么我们可以设置MAXDOP,减少并行度。

相关文章