Is the CXPACKET WAITS results ( msec/sec ) aggregated average for all processes over all server instance uptime ?

Question

Just to clarify my understanding of this wait's result's measure, msec/sec, this is an averaging over the total instance uptime ?

Answer 1

Hello Sir, Never a need to be sorry, but it is the results of wait duration divided by events. (not max duration) for cxpacket waits. Is this what you mean ? Thank you.

Answer 2

Hi @Willemain, Richard (Contractor) ,

Welcome to Microsoft Q&A!
Please refer to this blog: Troubleshooting the CXPACKET wait type in SQL Server
The CXPACKET term came from Class Exchange Packet, and in its essence, this can be described as data rows exchanged among two parallel threads that are the part of a single process. One thread is the “producer thread” and another thread is the “consumer thread”. This wait type is directly related to parallelism and it occurs in SQL Server whenever SQL Server executes a query using the parallel plan.

There are different scenarios that can cause excessive CXPACKET waits can be encountered:

• The Consumer cannot read the packets because the buffer (queue) is empty – meaning that the Producer threads do not supply or supply slow data into the buffer. This means that some Producer threads are working slowly due to waiting for a resource such as CPU, memory grants, I/O, etc., or some Producer threads are simply blocked
• The producer threads cannot store the packets into a buffer as the buffer is full. This means that Consumer threads cannot process the data fast enough, causing a situation where the Producer threads must wait to store the data in the buffer, once the buffer gets full
• Excessive parallelism for small queries, were creating the parallel plan and parallel execution could be costlier and slower than serialized plan
• The uneven balance of packets across the parallel threads could cause that some threads complete work faster than the others, and then they are waiting for other packets to complete their works

Finally, these are the steps that are recommended in diagnosing the cause of high CXPACKET wait stats values (before making any knee-jerk reaction and changing something on SQL Server):

• Do not set MAXDOP to 1, as this is never the solution
• Investigate the query and CXPACKET history to understand and determine whether it is something that occurred just once or twice, as it could be just the exception in the system that is normally working correctly
• Check the indexes and statistics on tables used by the query and make sure they are up to date
• Check the Cost Threshold for Parallelism (CTFP) and make sure that the value used is appropriate for your system
• Check whether the CXPACKET is accompanied with a LATCH_XX (possibly with PAGEIOLATCH_XX or SOS_SCHEDULER_YIELD as well). If this is the case than the MAXDOP value should be lowered to fit your hardware
• Check whether the CXPACKET is accompanied with a LCK_M_XX (usually accompanied with IO_COMPLETION and ASYNC_IO_COMPLETION). If this is the case, then parallelism is not the bottleneck. Troubleshoot those wait stats to find the root cause of the problem and solution

Here is another link you can refer to: https://www.sqlskills.com/help/waits/cxpacket/
This link contains two blogs, both of which you can read.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.