Problem solved, investigation ended … right? Or do you see what I see? Two of the CPUs are still maxed out. The problem wasn’t solved, it was just restricted to fewer CPUs. Concurrency is improved, but the query will take much longer to complete now.
This is where we got involved. This is a very common mistake. CXPacket waits is a symptom, not the underlying problem. I’m not going to go into great detail here about how parallelism works or how to deal with CXPacket waits. That message has already been delivered in excellent detail. If you want that information, I suggest starting with Adam Machanic’s (blog|@AdamMachanic) series on parallelism that he did for SQL University: SQL University: Parallelism Week – Introduction. Then when you feel ready for more, move on to Paul White’s (blog|@SQL_Kiwi) post Understanding and Using Parallelism in SQL Server.
The Barking Dog Analogy
I’m not here for a heavy, in-depth discussion on parallelism. I shared my thoughts on disabling paralellism in the form of an analogy, and people seemed to like the analogy, so I’m posting it for everyone:
If my dog is barking at a burglar, and I tell her to be quiet and she does, then all I’ve done is stop the barking, not the burglar. CXPacket waits are like the barking dog. They’re warning you of a bigger problem. If you tell the dog to be quiet, the barking may stop, but the bigger problem is still there.