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.

SQL Server Query Plan Properties
not for you!

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.



2 thoughts on “SQL Server 2022 Finally Adds Actual Non Parallel Plan Reasons To Query Plans

  1. Here’s the list of all the reasons I could find:

    CLRUserDefinedFunctionRequiresDataAccess
    CouldNotGenerateValidParallelPlan
    DMLQueryReturnsOutputToClient
    EstimatedDOPIsOne
    MaxDOPSetToOne
    MixedSerialAndParallelOnlineIndexBuildNotSupported
    NonParallelizableIntrinsicFunction
    NoParallelCreateIndexInNonEnterpriseEdition
    NoParallelCursorFetchByBookmark
    NoParallelDynamicCursor
    NoParallelFastForwardCursor
    NoParallelForCloudDBReplication
    NoParallelForDmlOnMemoryOptimizedTable
    NoParallelForMemoryOptimizedTables
    NoParallelForNativelyCompiledModule
    NoParallelForPDWCompilation
    NoParallelPlansDuringUpgrade
    NoParallelPlansInDesktopOrExpressEdition
    NoParallelWithRemoteQuery (+2022)
    NoRangesResumableCreate (+2022)
    NoRemoteParallelismForMatrix
    ParallelismDisabledByTraceFlag
    TableVariableTransactionsDoNotSupportParallelNestedTransaction
    TSQLUserDefinedFunctionsNotParallelizable
    UpdatingWritebackVariable

    I marked 2 new ones in SQL 2022 (so far), the rest appear in SQL 2019 onward.

Comments are closed.