(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)
CXCONSUMER
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. Basically, benign CXPACKET waits now show up as CXCONSUMER waits and these 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 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 benign CXCONSUMER waits, the remaining CXPACKET waits from the producer threads are what indicate a performance problem.
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 waits for a producer thread to send rows. 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. Now, 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’d treat that the same way as for CXPACKET and look for skewed work distribution or unwanted parallelism. 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
