CXCONSUMER Is Harmless? Not So Fast, Tiger.

In Theory

Let’s say you’ve got a query, and the point of that query is to take your largest customer/user/whatever and compare their activity to smaller whatevers.

You may also stumble upon this issue accidentally if your query happens to capture populations like that by accident.

For our purposes, let’s look at a query that compares Jon Skeet to 1000 users with the lowest activity. To make this easier, I’m not going to go through with the comparison, I’m just going to set up the aggregations we’d need to do that.

I’m going to do this on the smaller 10GB version of the Stack Overflow database, and running the query on my beefy desktop, in case you’re wondering. I need a few indexes first:

MAXDOP is set to four, and CTFP is set to 50. Now for the query:

In Parallel

This query hits a dead end really quickly. If I sample wait stats with sp_BlitzFirst, they tell an interesting story:

At the query level (yes, that’s 26 minutes in), we’ve been waiting on CXCONSUMER nearly the whole time.

You have died of consumption

The wait stats generated for a 30 second window are no less appealing. Nine total waits, each lasting 136 SECONDS on average.

Just Ignore Me

In this sample there are absolutely no waits whatsoever on CXPACKET.

They are nonexistent.

If you were hoping to find out that they were way crazy out of control call a priest and ditch your bar tab we’re driving on the train tracks, you’ll be awfully disappointed.

There just aren’t any.

There’s only one core in use for nearly the entire duration, aside from some blips.

Jammin On The Ten

Here’s the plan for it, so far. This is only an estimated plan.

In Serial

Adding a MAXDOP 1 hint to that query reduces query time to about 55 seconds.

It’s also possible to get faster queries by supplying join hints, though not faster than limiting DOP to 1.

Here’s the plan for it.

The wait stats for it are pretty boring. Some SOS_SCHEDULER_YIELD, some MEMORY_ALLOCATION_EXT.

Stuff you’d expect, for amounts of time you’d expect (lots of very short waits).

In Closing

This isn’t a call to set MAXDOP to 1, or tell you that parallelism is bad.

Most of the time, I feel the opposite way. I think it’s a wonderful thing.

However, not every plan benefits from parallelism. Parallel plans can suffer from skewed row distribution, exchange spills, and certain spooling operators.

Today, it’s hard to track stuff like this down without capturing the actual plan or specifically monitoring for it. This information isn’t available in cached plans.

This also isn’t something that’s getting corrected automatically by any of SQL Server’s cool new robots. This requires a person to do all the legwork on.

One other way is to use sp_BlitzFirst/sp_BlitzWho to look at wait stats. If you see queries running that are spending long periods of time waiting on CXCONSUMER, you just might have a thread skew issue.

If you blindly follow random internet advice to ignore this wait, you might be missing a pretty big performance problem.

In Updating

This query is now about 17 hours into running. Through the magic of live query plans, I can see that it’s stuck in one particular nut:

Look at you with your problems

I got paranoid about missing cumulative wait stats, so I started logging sp_WhoIsActive to a table.

Here’s what that looks like, for the most recent rows.

Into The Future

Right now it’s July 20th. This post is scheduled to go live on the 24th.

Will it finish before then?!

In Updating More

This query ran for a over 45 hours.

Squinting

The full plan for it is here.

Somewhat laughably, the wait stats for this query show up like this:

The CPU time for it looks like this:

And the last few minutes of CXCONSUMER waits look like this:

Consumed

I missed the last 30 seconds or so of the query running, which is why the CXCONSUMER waits here don’t quite line up with the total query CPU time, but they’re very close. Why doesn’t that wait show up in the query plan? I have no idea.

What really gummed things up was the final Nested Loops Join to Posts.

That’s a 13 digit number of rows for a database that doesn’t even have 50 million total rows in it.

Insert comma here

Bottom Line: Don’t go ignoring those CXCONSUMER waits just yet.

Thanks for reading!

Brent says: we were writing new parallelism demos for our PASS Summit pre-con and our Mastering Server Tuning class to show EXECSYNC, and we kept coming across wait stats results that just didn’t line up with what Microsoft has reported about “harmless” waits. This is going to be a really fun set of demos to share in class.

Previous Post
Building SQL ConstantCare®: What Mentoring Is (and Isn’t)
Next Post
Does your backup strategy achieve RPO and RTO goals of the business?

6 Comments. Leave new

  • Jon Szewczak
    July 24, 2018 10:39 am

    I have run into this several times recently on SQL Server 2017 Enterprise. It’s highly annoying, and it’s certainly not a “harmless” wait stat. Has anyone come across a good method of troubleshooting for the solution? Our solution was re-engineer the entire query from the ground up. It seems to have fixed it, but it’s a lot of work, and we still don’t know what the cause of the query wait was. And yes, I looked at the plan, and it didn’t tell me anything concrete.

    • Erik Darling
      July 24, 2018 3:20 pm

      Jon – by troubleshooting, what do you mean? It comes down to monitoring wait stats at this point, unfortunately.

      I agree that deeming it harmless was premature. If you have a specific scenario, leave a comment here.

      Thanks,
      Erik

  • Not really on a subject, but: I remember when CXCONSUMER wait type was announced to be added, many people believed (including me) that it will be used just to recognize control thread (ID = 0) and make CXPACKET waits more meaningful. Unfortunately, control thread can register CXCONSUMER or CXPACKET so I think there is still no 100% sure method to exclude control thread waits. Or am I wrong?

    • Erik Darling
      July 25, 2018 9:33 am

      Piotr — yeah, I’m not exactly sure what to make of it anymore. It seems like something I’d keep an eye on.

  • […] hitting more of those harmless, silly little CXCONSUMER […]

  • John "Z-Bo" Zabroski
    July 12, 2019 9:35 am

    I ran into this CXCONSUMER wait type today. Things I found:

    1. Queries suddenly reverting to a full table scan / using a bad index to seek for data
    2. Distributed transactions managed by MSDTC timing out.

    Took me about 2 hours to figure out the MSDTC timing out was a red herring and caused by the large IO reads (page reads per second, page writes per second stats shot through the roof), which in turn caused buffer cache hit ratio to drop, which in turn caused page life expectancy to plummet 75%. I found the offending queries with the table scans and started work on a fix.

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