SQL Server Wait Type

 SQL Server Wait Type

CXPACKET 與 CXCONSUMER

情況 1.Producer Consumer Issue under Parallelism, where Consumer threads report CXPACKET wait type while waiting for data from Producer threads. This is something which is non-actionable because this happen because of parallelism. 

當消耗者等待生產者執行序的資料時

SQL Server 2016 SP2 and SQL Server 2017 CU3以前

會出現 CXPACKET 等待

SQL Server 2016 SP2 and SQL Server 2017 CU3之後

改出現 CXCONSUMER 等待

情況 1 :管理員無法做任何事來改善

情況 2.Excessive Parallelism for small Queries which can be control by changing MAXDOP and /or Cost Threshold for Parallelism values. This is something which is actionable that can be control by changing these values. 

當有過多的平行小查詢，也會出現CXPACKET等待，可以透過設定或調整MAXDOP或Cost Threshold for Parallelism測試後來改善

情況 3.Uneven task distribution between parallel threads. This situation may occur when some of the parallel threads will complete assigned task prior to other tasks. These threads will report CXPACKET wait type until other threads will complete assigned task as well. This is something which generally occurs because of outdated statistics. We can reduce this situation by updating statistics. This is again something which is actionable. 

當查詢在平行處理時，執行序處理不平均的資料，導致已完成執行序等待就會出現CXPACKET，可以透過更新統計資訊來改善