SQL Server Wait Type – CXCONSUMER

Hello Friends,

CXPACKET (Class Exchange Packet) is a wait type that generally reported by SQL Server when SQL Server execute queries using Parallelism. This is the statement about CXPACKET wait type that we know since many years. CXPACKET can also be reported due to uneven wok distribution between threads to execute queries using parallelism. This makes difficult to decide when we must act and when not to take any action. To make it simple, Microsoft has introduced a new wait i.e. CXCONSUMER. This wait type has been introduced in SQL Server 2016 SP2 and SQL Server 2017 CU3.

Prior CXCONSUMER wait type, CXPACKET wait type reported for below conditions:

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.
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.
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.

After CXCONSUMER wait type, above specified Producer Consumer Issue will report CXCONSUMER wait type. As this is non-actionable, so no action is required from our side. We can ignore this wait type.

CXPACKET wait type will still be reported for second and third situation and both are actionable. In short, we can say by introducing CXCONSUMER wait type, Microsoft has removed the non-actionable part from CXPACKET wait type. This way now CXPACKET is much simpler as compare to earlier. Let’s see this wait type practically now. Execute below select query with tracking session level wait stats:

USE AdventureWorks;
GO
SELECT PRDT.Name AS Product_Name, 
NonDiscountSales = (OrderQty * UnitPrice),
Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Production.Product AS PRDT 
INNER JOIN Sales.SalesOrderDetail AS SOD
ON PRDT.ProductID = SOD.ProductID 
ORDER BY PRDT.Name DESC;
GO
Select * From sys.dm_exec_session_wait_stats 
where session_id = @@SPID
GO

In my case this query was executed using Parallelism as shown in below execution plan.

If you will check the result of second query, then you will see the CXCONSUMER wait type in the result set.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook