SQL Server 2017 CU3 adds CXCONSUMER waits, doesn’t mention it

The revolution will not be documented

At the PASS 2017, Pedro Lopes (don’t call him low-pez) from Microsoft mentioned that a new parallelism wait was getting added soon.

That wait, if you’re too darn tootin’ lazy to click, is called CXCONSUMER.

According to Pedro’s slide, but not the ENTIRELY MISSING DOCUMENTATION, this wait is the “safe” type of parallelism wait.

It’s a good thing Pedro is a dutiful blogger, so we don’t have to pull our hair out while unfurling these mysteries.

Speaking of documentation, our new CXCONSUMER friend isn’t mentioned in Query Store Wait Stats, either.

Say, maybe we really shouldn’t care about these things.

Where does it show up?

Well, it should show up in parallel queries. Every producer needs consumers.

Without them, they go out of business faster than Northwind and AdventureWorks.

You don’t have to actively do anything other than ignore it.

Careless whisper

I mean, unless your wait stats look like that.

Especially if they’re coming from a stored procedure like this:

BEWARE THIS PROCEDURE

Why do I bring up this semi-relevant kind of point?

Well, Sorts and Hashes spill to tempdb, and they’re logged in a few new DMVs.

Exchange spills, which occur in parallel queries, are not tracked by these counters. There’s an XE session for it, but no DMV information.

WEALTH OF INFORMATION

If you ever run into one, you may end up on this page, which has helpful advice:

There are several ways to avoid exchange spill events:

  • Omit the ORDER BY clause if you do not need the result set to be ordered.
  • If ORDER BY is required, eliminate the column that participates in the multiple range scans (T.a in the example above) from the ORDER BY clause.
  • Using an index hint, force the optimizer to use a different access path on the table in question.
  • Rewrite the query to produce a different query execution plan.
  • Force serial execution of the query by adding the MAXDOP = 1 option to the end of the query or index operation.

See? Ordering is the devil. Parallel ordering is the devil’s father.

Or something.

JUST STOP WRITING QUERIES AND ALL THE PROBLEMS WILL GO AWAY!

What’s the point?

Well, when everyone says “ignore it”, I like to figure out when you should pay attention to it.

If your CPUs are waiting dozens of milliseconds or more on CXPACKET or CXCONSUMER, you should absolutely pay attention.

This can be caused by a few things

  • Exchange spills
  • Parallel thread imbalances
  • Underpowered CPUs
  • Overloaded CPUs

Just rewrite the query.

I’m glad that this was introduced, because it gives us as query tuners better insight into potential issues with parallelism.

Thanks for reading!

Brent says: I’m really surprised this didn’t make the list of things fixed in CU3 because I naively assumed that the documentation was built with some kind of tool that automatically listed out the fixes. This improvement is a really, really big deal – kinda like the Meltdown/Spectre attacks that CU3 supposedly mitigates. The top of the CU3 release notes mention that it was pushed out as an urgent security update – yet there’s nothing security-related in the release notes, either. Which leads me to ask…are the release notes just that sloppy? And how is that supposed to inspire confidence in the update process? Surely Microsoft wouldn’t push out an update as an urgent security fix if it didn’t fix something, so…what’s really in this Cumulative Updates?

Previous Post
First Responder Kit Release: It’s Too Cold Not To Do A Release
Next Post
SQL Server 2017 CU3 adds tempdb spill diagnostics in DMVs and Extended Events

13 Comments. Leave new

  • Is an “exchange pill” more like the red pill or the blue pill? 🙂 I’m guessing its more like the red pill because it leads down a rabbit hole.

    Reply
  • Agree with Brent. Have tweeted my disgust at the level of documentation on the CU’s that appears to be falling. How are we supposed to “trust” CU’s that apparently are supposed to have the same “rigor” attached to them as the Service packs? if you cant adequately document, then what is it you are hiding? I understand that some things are best left unsaid. But not talking about functionality changes and certainly a security fix that is the “fix-that-cant-be-named” is boarding on laziness/incompetence.

    Reply
  • Razvan Zoitanu
    January 11, 2018 3:45 am

    “This change will be effective starting with SQL Server 2017 CU3 and upcoming SQL Server 2016 SP2”. Can’t find a roadmap for SQL Server 2016 SP2. Any hints on a release date ?

    Reply
  • Hey Erik, FYI it’s been in the docs page since 1/4 (when the page was last edited): https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql

    Reply
    • I saw, I just liked your blog post better! Surprised it wasn’t in the release notes as a change, is all.

      Thanks!

      Reply
  • Just installed SQL 2016 SP2 over the weekend, started getting alerts for CXCONSUMER waits, and your blog post is the first search result. Thanks for the info on when to be concerned, and the link to Pedro’s announcement blog post.

    Reply
  • Eric Russell
    May 8, 2018 12:03 pm

    I’m glad that CXPACKET now measures only the “bad” type of CX wait events: those due to skewed parallelism or blocking, while CXCONSUMER means the query is actually busy exchanging packets. Kind of like wrapping my head around those good versus bad cholesterol numbers.

    Reply
  • Alex Bransky
    July 27, 2018 11:28 am

    I just had a stored proc run all night in the dev environment with CXCONSUMER waits (I eventually killed it) and finish in the test and production environments in just a few minutes. All are running 2017 CU7 and have the same CPU and RAM specs.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}