Skip to main content

CXCONSUMER

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

Detailed Description​

CXCONSUMER waits occur when parallel query plans are running. When a parallel query is called for, the query is broken down into sub-queries and work assigned to each. These sub-queries run in pairs - Producers scan for data and Consumers pass the data on to further stages of the query. Hence if you request a parallelism of 4, it will require 8 threads plus a co-ordinator thread marshalling producers and consumers. CXPACKET and CXCONSUMER waits are seen when the threads are communicating.

How to reduce this wait​

  1. DO NOT OVERUSE PARALLEL QUERY BEHAVIOUR. It is rare that setting MAXDOP to 0 will provide best performance. Test queries, setting MAXDOP to values from 1 to your number of CPUs.
  2. In isolation for a single user, parallel query plans can yield fast results - but beware of flooding a server with producers and consumers if you build this into a system with a high number of concurrent users as the thread count can shoot through the roof.
  3. If waiting is excessive and cannot be reduced by tuning the query (such as adding indexes), consider adjusting the Cost Threshold for Parallelism. Popular advice says that you can lower the Max Degree of Parallelism (MaxDOP), but this should not be done in isolation - testing is advised.

Microsoft Docs - sys.dm_os_wait_stats (Transact-SQL)

sqlskills.com - CXCOMSUMER

Knee-jerk Wait Statistics - CXPACKET

Search online​

If this article doesn't have the information you need you can try searching online. Remember, you can contribute suggestions to this page.