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
Best Answer
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/