Why Does My Serial Query Plan Have Parallel Wait Stats Like CXPACKET And CXCONSUMER?

Last Updated on May 16, 2022 by Erik Darling

Harkening

In dramatic fashion, I’m revisiting something from this post about stats updates.

It’s a quick post, because uh… Well. Pick a reason.

Get In Gear

Follow along as I repeat all the steps in the linked post to:

  • Load > 2 billion rows into a table
  • Create a stats object on every column
  • Load enough new data to trigger a stats refresh
  • Query the table to trigger the stats refresh

Except this time, I’m adding a mAxDoP 1 hint to it:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT COUNT(*)
FROM dbo.Vetos
WHERE UserId = 138
AND PostId = 138
AND BountyAmount = 138
AND VoteTypeId = 138
AND CreationDate = 138
OPTION(MAXDOP 1);
SELECT COUNT(*) FROM dbo.Vetos WHERE UserId = 138 AND PostId = 138 AND BountyAmount = 138 AND VoteTypeId = 138 AND CreationDate = 138 OPTION(MAXDOP 1);
SELECT COUNT(*)
FROM dbo.Vetos
WHERE UserId = 138
AND   PostId = 138
AND   BountyAmount = 138
AND   VoteTypeId = 138
AND   CreationDate = 138
OPTION(MAXDOP 1);

Here’s Where Things Get Interesting

SQL Server Wait Stats
Bothsies

Our MaXdOp 1 query registers nearly the same amount of time on stats updates and parallelism.

SQL Server Query Plan
If this is madness…

But our plan is indeed serial. Because we told it to be.

By setting maxDOP to 1.

Not Alone

So, if you’re out there in the world wondering why this crazy kinda thing goes down, here’s one explanation.

Are there others? Probably.

But you’ll have to find out by setting MAXdop to 1 on your own.

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.

Related Posts