SQL Server query plans have had, forever, pretty generic reasons embedded in the XML for why your query was prevented from going parallel.
Not for things like it not breaking the Cost Threshold For Parallelism barrier, which should be obvious to the end user, but certainly for things like scalar UDFs, etc.
The thing is, the reason always seemed to be “Could Not Generate Valid Parallel Plan” for most of them, even though more explicit reasons were available.
They started cropping up, as things do, in Azure SQL DB, and have finally made it to the box product that we all know and mostly love.
Let’s explore some of them! Because that’s what we do.
Generate Valid Blog Post
First, some intrinsic functions prevent a parallel plan. You can always see the reason (if one exists) if you look in the properties of the root operator in the query plan.
Some Intrinsic Functions
An easy one to validate this with is OBJECT_NAME
SELECT c = OBJECT_NAME(COUNT_BIG(*)) FROM dbo.Posts AS p;
For this, we’ll see this show up:
Setting MAXDOP To One
This was one of the few reasons that was generally available going back to SQL Server 2012:
SELECT c = COUNT_BIG(*) FROM dbo.Posts AS p OPTION(MAXDOP 1);
Table Variable Modifications
Here’s a sneaky one that I’ve been trying to warn folks about forever:
DECLARE @c table ( c bigint ); INSERT @c ( c ) SELECT c = COUNT_BIG(*) FROM dbo.Posts AS p;
Bit of a mouthful, but hopefully this makes things quite perfectly clear:
Fast Forward Cursors
There’s always some hijinks about with cursors, but here you go:
DECLARE @c bigint; DECLARE c CURSOR FAST_FORWARD FOR SELECT c = COUNT_BIG(*) FROM dbo.Posts AS p; OPEN c; FETCH NEXT FROM c INTO @c; CLOSE c; DEALLOCATE c; GO
This is another reason that I have seen around for a while too, but we may as well be thorough:
Scalar Functions That Can’t Be Inlined
Shocking, I know! I know.
CREATE OR ALTER FUNCTION dbo.c ( @c bigint ) RETURNS bigint WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT AS BEGIN DECLARE /*Ruin UDF inlining with one weird trick*/ @gd datetime = GETDATE(); RETURN @c; END; GO SELECT c = dbo.c(COUNT_BIG(*)) FROM dbo.Posts AS p;
And from the XML:
Fun stuff is always in XML. Just think about all the best times in your life. I bet XML was involved.
Now when you look at it, it will tell you what’s screwing up parallel plan generation in your SQL Server queries.
J’accuse, as they say.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on 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.
2 thoughts on “SQL Server 2022 Finally Adds Actual Non Parallel Plan Reasons To Query Plans”
Here’s the list of all the reasons I could find:
I marked 2 new ones in SQL 2022 (so far), the rest appear in SQL 2019 onward.
Comments are closed.