A discussion started today on Twitter about CXPacket waits. There had been a heated debate on the subject on Twitter the previous night (wow, does that sound geeky), so many of us got looped into today’s discussion. Someone had experienced a problem with a delete query on a large table causing all CPUs to nearly max out (96% average). In his investigations, he looked at the total waits on the server and saw that the second highest waits were CXPacket waits (parallelism). As many people do (and even recommend to others), he changed MAX DOP (maximum degree of paralellism) from 0 to 1. In his observation, total CPU utilization went from 96% to 28% (see image below).
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.
*Reposted with permission from SQLSoldier.com.