Why Does My Serial Query Have Parallel Waits?

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:

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


Bothsies

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

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!



Leave a Reply

Your email address will not be published. Required fields are marked *