Practical SQL Server
Common Misconceptions about CXPACKET Waits

Common Misconceptions about CXPACKET Waits

A number of things can contribute to CXPACKET waits within SQL Server. But when I'm tuning servers for clients who don't (or haven’t) had DBAs on hand for quite a while and start to see high numbers of these waits (i.e., up in the top 5 or 10 adjusted waits) coupled with a decent number of SOS_SCHEDULER_YIELD waits, I usually take this as a good sign—as it usually means that they're probably missing a lot of important or needed indexes.

Common Recommendation to Ignore

Bumping into high amounts of these waits on most OLTP workloads, however, is NOT an indication that I should take away SQL Server's ability to run ugly queries (or queries with missing indexes) against multiple threads of execution. Or, in other words, high numbers of these waits aren't an indicator that I should go into sp_configure and 'cripple' server-wide execution by means of cobbling the 'max degree of parallelism' setting.

Related: Dissecting SQL Server's Top Waits

Yet, unfortunately, this is exactly what I see as a common recommendation on large numbers of internet forums and elsewhere. (In fact, a few years ago I actually ran into an environment where all sorts of larger queries were taking 'forever' because a support engineer at Microsoft had (amazingly) recommended setting max degree of parallelism down to 1 because some bigger, more unruly, operations were capitalizing resources and blocking smaller operations.)

Don’t Throw the Baby out With the Bath Water

Stated differently: Setting the 'max degree of parallelism' at a low level to simply get rid of CXPACKET waits is like throwing the baby out with the bathwater. It's terribly bad form—as it can drastically restrict SQL Server’s ability to parallelize operations and 'speed them through the system.' Yet, as with the system I alluded to above, I occasionally see very expensive systems ‘cobbled’ such that while they may have gobs of expensive processors/cores on-hand, SQL Server has been, effectively, prevented from using them as efficiently as possible.

And that’s not to say that there’s NEVER a reason to set 'max degree of parallelism' at the server level.

And it’s also worth noting, as a bit of an aside, that if a DBA HAS set 'max degree of parallelism' at the server level down to something like 1, developers can still BYPASS that with something like OPTION (MAXDOP = 8) in their code. Consequently, since the MAXDOP hint can/does override server-level settings, that means that it’s commonly going to be a MUCH better alternative for targeting the occasional unruly, problematic, or stubborn query than merely taking some of the bad advice available on the internet and trying to reduce your entire system down to the lowest common denominator as a means of dealing (typically) either some missing indexes or a couple of unruly/problematic operations.

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish