Please Stop Ignoring CXCONSUMER Waits In SQL Server

I’m Begging You, Here

Ever since SSMS started collecting wait stats in query plans, I’ve been mad about a couple things that get filtered out:

  • Lock waits
  • CXCONSUMER waits

Lock waits are particularly annoying. Imagine (I know, this might be difficult) that you have a friend who is puzzled by why a query is sometimes slow.

They send you an actual plan for when it’s fast, and an actual plan for when it’s slow. You compare them in every which way, and everything except duration is identical.

It’d be a whole lot easier to answer them if LCK waits were collected, but hey. Let’s just make them jump through another hoop to figure out what’s going on.

CXCONSUMER has a similar problem — and here’s the thing — if people are going through the trouble of collecting this information, give’em what they ask for. Don’t just give them what you think is a good idea.

Highly Affected People

Let’s look at a query where parallelism all gets skewed to one thread.

FROM dbo.Users AS u
        SELECT *, 
              OVER( PARTITION BY vs.Id 
                    ORDER BY vs.Id DESC ) AS whatever
        FROM dbo.VotesSkewed AS vs
        WHERE vs.UserId = u.Id
        AND   vs.VoteTypeId BETWEEN 1 AND 4
    ) AS ca
WHERE ca.whatever = 0;

It runs for 42 seconds!

SQL Server Query Plan

But the only wait collected is SOS_SCHEDULER_YIELD. For 392 ms.

SQL Server Query Plan
o okay

Different Angle

If we watch the server’s wait stats while the query runs, we see a totally different story.

EXEC sp_BlitzFirst @Seconds = 50, @ExpertMode = 1;
SQL Server Query plan
uh huh

We had four waits on CXCONSUMER that all lasted nearly 11 seconds. Which of course happens because all the rows end up on one thread.

SQL Server Query Plan

Sure, that’s easy enough to spot if you go looking, but having CXCONSUMER waits collected would make it a whole lot easier to know what to look for.

That is surely a significant enough wait to include. And don’t tell me it’s not actionable, because I spend a good chunk of time fixing problems like this.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

11 thoughts on “Please Stop Ignoring CXCONSUMER Waits In SQL Server

  1. Hi Erik,

    First, thanks for the great posts. I was not aware that we were filtering out CXConsumer and Locks. Can you point me to documentation on this? Since that information is being collected at the DMV level (you’re not going to pull the rug out from under me again are you?) I cannot understand why it would not be in the execution plan too.

    1. With regards to the pingback below, what I’m looking for is Microsoft documentation about this.


Comments are closed.