官方文件
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 並行值)來解決
![](http://img.blog.itpub.net/blog/2022/10/31/c5abe60e66d8aca8.jpeg?x-oss-process=style/bb)

**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）執行緒。在並行過程中，生產者執行緒會實際影響效能，導致消耗者執行緒只能等待生產者提供資料
![](http://img.blog.itpub.net/blog/2022/10/31/b53dbb1132a69581.jpeg?x-oss-process=style/bb)

工作中遇到的問題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條件的情況，新增索引後問題解決。

Sqlserver並行等待CXPACKET、CXCONSUMER問題的解決思路和案例

相關文章