Sqlserver并行等待CXPACKET、CXCONSUMER问题的解决思路和案例

官方文档
https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql?view=sql-server-ver16
https://learn.microsoft.com/zh-cn/sql/relational-databases/diagnose-resolve-latch-contention?view=sql-server-ver16

总结
1、Server 在执行查询时如果使用并行(parallel)，这通常会比在序列化进程(serialized process)中执行的查询要快。使用并行计划时，查询在多个线程中执行，只有在所有并行线程都完成后才能继续查询。这意味着整个查询的快慢取决于最慢的那个线程。并行时SQL Server将一个线程指定为调度线程，这个调度的线程会将并行工作负载的一部分分配给工作线程，并行性就像生产者(工作线程CXPACKET)将数据推送给消费者(调度线程CXCONSUMER)，消费者可能不得不等待生产者的数据。因此，需要注意生产者等待，而消费者等待是不可避免的，因为生产者运行时间较长，则消费者等待是被动的结果。当出现CXPACKET等待，表示工作线程CXPACKET出现性能问题，调度线程CXCONSUMER等待工作线程CXPACKET提供数据；当经常出现CXCONSUMER等待时，就表示调度线程CXCONSUMER出现性能问题，调度线程CXCONSUMER无法及时把数据处理完成。

2、SQL Server为了加快执行速度，当语句消耗超过Cost Threshold for Parallelism设置时就会采用多线程并行的方式进行，每个线程处理一部分数据，最后再进行结果合并，在等待一个或多个线程返回结果时就会产生CXPACKET、CXCONSUMER等待。对于复杂的SQL语句，并行的方式可以大幅的降低执行时间。如果简单的的SQL语句也出现CXPACKET、CXCONSUMER等待。那么在高并发的情况下，这样的简单语句也会产生严重的性能问题。

3、Cost Threshold for Parallelism 值以秒为单位，这意味着对于 SQL Server 估计运行时间将超过 5 秒的每个查询，将创建一个并行计划。

4、通过查询sys.dm_exec_session_wait_stats可以看到某个会话中哪个等待消耗多少时间

5、如果select和DML语句使用了并行并导致严重等待，那么思路就是去检查执行计划，如果DDL语句使用了并行并导致了严重等待，那么可能要考虑增加内存和cpu资源。

CXPACKET
Occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.
Occurs with parallel query plans when waiting to synchronize the Query Processor Exchange Iterator, and when producing and consuming rows.
当等待同步查询处理器Exchange迭代器以及生成和使用行时，与并行查询计划一起发生。 如果等待过多且无法通过优化查询 (（例如添加索引) ）来减少，请考虑调整并行度的成本阈值或降低 MaxDOP (MaxDOP) 的最大并行度。这是可操作的等待，一般可以通过修改Max Degree of Parallelism、Cost Threshold for Parallelism参数或在语句后面加上OPTION (MAXDOP 并行值)来解决

CXCONSUMER
Occurs with parallel query plans when a consumer thread (parent) waits for a producer thread to send rows. CXCONSUMER waits are caused by an Exchange Iterator that runs out of rows from its producer thread. This is a normal part of parallel query execution.
当使用者线程 (父线程) 等待生成线程发送行时，使用并行查询计划发生。 CXCONSUMER 等待是由一个Exchange迭代器引起的，迭代器从其生成线程中耗尽行。 这是并行查询执行的正常部分。
CXCONSUMER 发生在消费者线程等待生产者线程发送行时。这是一种等待类型，是并行查询执行的正常部分，不能直接通过更改Max Degree of Parallelism、Cost Threshold for Parallelism配置来影响。这就是为什么将其称为“可忽略不计”的原因。
CXCONSUMER是从SQL 2016 SP2开始引入的新等待状态。之前的CXPACKET等待被拆分成benign良性(CXCONSUMER)和actionable可操作(CXPACKET)的等待两种，对于执行计划操作符而言，有生产者（producer）和消耗者（consumer）线程。在并行过程中，生产者线程会实际影响性能，导致消耗者线程只能等待生产者提供数据

工作中遇到的问题1：服务器20核CPU，48GB内存，创建聚集索引时遇到LATCH_EX大量的等待，且该会话同一个spid在sys.sysprocesses中出现20行(其实就是这个spid有20个子线程)都是等待CXCONSUMER，大家都被这个spid会话自己堵塞了。
解决方法：考虑这个会话居然用了服务器上所有的20个CPU，考虑降低并行度试试效果，于是在代码里面的create cluster index 后面加上WITH (MAXDOP = 10)，MXADOP其实就是Max Degree of Parallelism，运行后发现没有效果。最后对服务器升级cpu到32核，内存升级到64GB，该会话的运行时间从7小时降低到4小时。

工作中遇到的问题2：服务器20核CPU，48GB内存，delete和insert遇到大量CXPACKET的等待，且该会话同一个spid在sys.sysprocesses中出现20行(其实就是这个spid有20个子线程)都是等待CXPACKET，大家都被这个spid会话自己堵塞了。
解决方法：查看了delete和insert后面的执行计划，没有什么问题，最后对服务器升级cpu到32核，内存升级到64GB，该会话的运行时间从2小时降低到1小时。

工作中遇到的问题3：服务器20核CPU，48GB内存，select遇到大量CXCONSUMER的等待，且该会话同一个spid在sys.sysprocesses中出现20行(其实就是这个spid有20个子线程)都是等待CXCONSUMER，大家都被这个spid会话自己堵塞了。
解决方法：查看了这些select会话，发现该select运行时间太长，执行计划不对，缺少索引，而且居然还有一个select * from table不加任何where条件的情况，添加索引后问题解决。