SQL Server 2022 Finally Adds Actual Non Parallel Plan Reasons To Query Plans

Wayback Machine

---

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:

NonParallelPlanReason="NonParallelizableIntrinsicFunction"

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);

And obviously:

NonParallelPlanReason="MaxDOPSetToOne"

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:

NonParallelPlanReason="TableVariableTransactionsDoNotSupportParallelNestedTransaction"

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:

NonParallelPlanReason="NoParallelFastForwardCursor"

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:

NonParallelPlanReason="TSQLUserDefinedFunctionsNotParallelizable"

Groovy

---

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!

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.