(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)
Description:
The simplest explanation of this wait type is that there are parallel plans running. This wait type was added in 2016 SP2 and 2017 RTM CU3 (as a result of a Connect item I submitted in 2016) to reduce the number of actionable CXPACKET waits that occur. The idea is that benign CXPACKET waits now show up as CXCONSUMER waits and can usually be safely ignored (but see below…)
For query plan operators that have producer and consumer threads (e.g. a Repartition Streams), all threads traditionally could show CXPACKET waits. The actual performance issue is likely with the producer threads, as the consumer threads are just waiting for the producer threads to give them data. By making the consumer threads register CXCONSUMER waits, the remaining CXPACKET waits from the producer threads should be what indicate a performance problem. However, in some cases there may not be many CXPACKET waits but instead a few long CXCONSUMER waits or many thousands of short CXCONSUMER waits. Both of these can indicate skewed parallelism or something like a poor join condition causing producer threads to do lots of work without pushing many rows through to the consumer threads.
Do not just reduce the server MAXDOP to try to reduce or remove these! Filter out all CXCONSUMER waits and then troubleshoot the remaining CXPACKET waits.
(Books Online description: “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.”)
Questions/comments on this wait type? Click here to send Paul an email, especially if you have any information to add to this topic.
Added in SQL Server version:
2016 SP2 and 2017 RTM CU3.
Removed in SQL Server version:
N/A
Extended Events wait_type value:
The map_key value in sys.dm_xe_map_values is 192 in 2016 SP2. After 2016 SP2, you must check the DMV to get the latest value as some map_key values have changed in later builds.
Other information:
This wait type is one that I usually filter out as a benign wait when doing wait statistics analysis. BUT, if you have a long-running query and in the output from sys.dm_os_waiting_tasks you see parallel threads with long wait times for CXCONSUMER, I would stop filtering out CXCONSUMER and investigate. I’d treat that the same way as for CXPACKET and look for skewed work distribution, or unwanted parallelism, or something else causing the parallel threads to wait. Please see the CXPACKET wait for more information on how to understand and troubleshoot those waits.
The original announcement about the change to split some CXPACKET waits into CXCONSUMER waits is here. Be aware that there was a bug in 2017 RTM CU3 and the preview versions of 2016 SP2 where CXCONSUMER waits were reported incorrectly (KB article here) – this is fixed in 2016 SP2 and 2017 RTM CU4.
Known occurrences in SQL Server (list number matches call stack list):
The stack below is an example of a consumer thread waiting to get a row as part of a producer/consumer operator. There are many, many more occurrences with similar stacks.
Abbreviated call stacks (list number matches known occurrences list):
- SOS_Task::PostWait+0x6f
EventInternal<SuspendQueueSLock>::Wait+0x24c
EventInternal<SuspendQueueSLock>::WaitAllowPrematureWakeup+0x98
CXPacketList::RemoveHead+0xf5
CXPipe::GetRow+0x176
CXPipeMerge::GetRowFromPipe+0x74
CXPipeMerge::GetRow+0x70
CQScanExchangeNew::GetRowHelper+0x1ac
CQScanTopNew::GetRow+0x14e
CQueryScan::GetRow+0x81
CXStmtQuery::ErsqExecuteQuery+0x4dc
CXStmtSelect::XretExecute+0x322
CMsqlExecContext::ExecuteStmts<1,1>+0x40d
CMsqlExecContext::FExecute+0xa9e
CSQLSource::Execute+0x983
process_request+0xea6