3

I was was running the query in this article:

http://sqlity.net/en/708/why-cxpacket-waits-are-not-your-performance-problem/

To see what my threads were waiting on in regards to a suspended query with a wait type of CXPACKET.

However for the SPID in question threads that are running were showing wait types of NULL with every other thread in a SUSPENDED state with a wait type of CXPACKET.

I was expecting to one of the threads having some kind of wait type other than CXPACKET, can anyone explain to me what is happening in this situation?

Thanks

Improve this question

2 Answers 2

Reset to default
6

What you're seeing is the threads with CXPACKET waits are actually done with whatever work they had to do and are now waiting for the other active threads (the ones with NULL as the wait type) to complete.

Brent O had a great analogy using a classroom. The teacher hands out a bunch of different stacks of paper to the class and has them find a word on them. What we need to take into consideration is the fact that 1) the stacks of paper could be different sizes 2) different students read faster/slower than others 3) one student may find the word 1 time on the first page, while the next could find it 400 times across 3000 pages.

You're seeing a natural and intended behavior when you deal with parralelism, some threads finish faster than others and are forced to wait until the other threads complete, regather all the threads and give you an output.

http://www.brentozar.com/archive/2013/08/what-is-the-cxpacket-wait-type-and-how-do-you-reduce-it/

Improve this answer
4
  • I see, I still would have expected a wait type of some sort to have appeared in the thread doing some work though? Also whats the best way to balance the workload then, by just ensuring that you have a good indexing strategy?
    – Tom
    Commented Mar 27, 2015 at 13:11
  • Well, think about it. There's not a wait type because that thread is actually doing something, it's not waiting on anything, it's working. :) Remember, CXPACKET waits are not inherently bad. Now, if they comprise over 50% of your total waits, then you may want to consider adjusting your MAXDOP and Cost Threshold for parallelism. Number one rule is don't troubleshoot something that isn't a problem. That article can help give you guidance on starting points for those settings. It's very much trial and error, took me 2 weeks to get our settings dialed in. We use CTP=40, MAXDOP=4.
    – Kris Gruttemeyer
    Commented Mar 27, 2015 at 13:14
  • You want to find the right balance so your big donkey queries get to spread out and use parallelism, while your smaller queries stay single-threaded and leave more threads for the big queries.
    – Kris Gruttemeyer
    Commented Mar 27, 2015 at 13:17
  • That article (Sebastian's) is good, but an easier way to see it is Adam Machanic's sp_WhoIsActive @get_task_info = 2. That shows you what each individual task in the parallel query is waiting on - meaning, in the classroom analogy, what the slow students are doing.
    – Brent Ozar
    Commented Jul 7, 2016 at 11:09
5

The CXPACKET was always the confusing wait type for younger DBAs and some predictably wrong reactions are generally expected. There are multiple acpects of the CXPACKLET wait type and I've tried in Troubleshooting the CXPACKET wait type in SQL Server article to bring on the table most of the reasons for high CXPACKET but also to explain the background of the CXPACKET, as the proper understanding of parallelism in SQL Server is the key for understanding

So for those who don't want to get in details, I will post the summary of the article here (but I would definitely suggest reading of the article for complete info about CXPACKET wait type):

  • 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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.