Occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.
CXPACKET 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.
CXCONSUMER waits are seen when the threads are communicating.
How to reduce this wait
- 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.
- 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 - further reading is advised.
- Microsoft Docs - sys.dm_os_wait_stats (Transact-SQL)
- sqlskills.com - CXPACKET
- Knee-jerk Wait Statistics - CXPACKET
If this article doesn't have the information you need you can try searching online. Remember, you can contribute suggestions to this page.
- Google Search for sqlserver - CXPACKET
- Ecosia Search for sqlserver - CXPACKET
- Bing Search for sqlserver - CXPACKET