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.
I mean, unless your wait stats look like that.
Especially if they’re coming from a stored procedure like this:
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.
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.
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
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?