一.概述

　　 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秒。

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

   1.1   查询 CXPACKET的等待

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

　　

sqlserver 如何做负载均衡_性能调优

 1.2  模拟CXPACKET的并行处理 

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

　

sqlserver 如何做负载均衡_资源等待_02

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

sqlserver 如何做负载均衡_sql server_03

结果。下面一个举例中 会话session是SPID 56。 这里我们明显看到，SQL Server使用了5个线程kpid 来执行这个query。

　　　　

sqlserver 如何做负载均衡_CXPACKET_04

　1.3  分析CXPACKET的并行处理

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

 　　

sqlserver 如何做负载均衡_性能调优_05

　　

sqlserver 如何做负载均衡_sql server_06

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

　　

sqlserver 如何做负载均衡_资源等待_07

sqlserver 如何做负载均衡_CXPACKET_08

1.4  控制CXPACKET并行度

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

　　

sqlserver 如何做负载均衡_性能调优_09

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

　　　　

sqlserver 如何做负载均衡_资源等待_10

1.5  CXPACKET资源等待总结

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

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

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 )

sqlserver 如何做负载均衡_性能调优_11

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

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

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

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

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

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