SQL Server 2017’s new CXCONSUMER wait type was originally announced by Microsoft’s Pedro Lopes, and now it’s out. Here’s what it means for performance tuners.

According to Pedro, this wait is the “safe” type of parallelism wait, as opposed to the CXPACKET wait type, which means work isn’t evenly balanced across all of our cores. Pedro blogged about how CXCONSUMER makes parallelism waits actionable.

Where does CXCONSUMER 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.

Or something.

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

CXCONSUMER isn’t actually ignorable.

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?

What To Learn Next

• How to Do a Performance Health Check
• How to Measure Your Server’s Top Wait Types with sp_BlitzFirst
• How to Fix CXCONSUMER and CXPACKET (from our Mastering Server Tuning class)