SQL Server 2022 Tells You Why A Query Can’t Go Parallel.

Until 2022, when a query couldn’t go parallel, all we got was a really cryptic note in the execution plan properties saying NonParallelPlanReason = CouldNotGenerateValidParallelPlan.

But starting with SQL Server 2022, even when I’m running under older compatibility levels:

The execution plan gives me way more details:

Awww yeah! Here’s another example using a scalar user-defined function:

The actual plan now clearly explains that our query can’t go parallel because of the scalar function:

If our query uses two parallelism blockers at once:

The actual plan only shows one of the two reasons:

And the XML doesn’t show both reasons, either.

Still, that’s a really small complaint – at least SQL Server 2022 shows ANY of the reasons, which starts you down the road of performance tuning this query. I’ll take it!

SQL Server 2022 doesn’t remove those parallelism blockers, though.

If I change the compatibility level to 160 (2022), the table variable still goes single-threaded, as does the scalar function:

The execution plan still plays the sad trombone:

Because it doesn’t look like SQL Server 2022 is fixing the rampant issues with scalar function inlining.

Previous Post
[Video] Office Hours: Professional Development Edition
Next Post
[Video] Office Hours in Moab, Utah

10 Comments. Leave new

  • I suppose asking them to put spaces between the words would be a step too far, huh.

    Reply
    • Isn’t that weird? Note that other things in the screenshot (“Cached plan size”, “32 KB”) have spaces in between them, but… just not these. Odd.

      Reply
      • It’s almost — _almost_ — like they don’t have a unified naming convention, and we’re just seeing their enum identifiers in the end product.

        But realistically, it just goes to show that it doesn’t matter where you are, even if you’re actually working close to the SQL Server query optimiser; some people are terrified that a space will break something. It’s not like I go throwing whitespace into database or object names for a laugh, but in a UI/tooltip it’s nice to have consistency. Especially at Microsoft prices.

        That said, most times I’ve been forced into naming SomethingWithANeedlesslyLongNameLikeThis which could have been shorter, it’s been after a heated argument about naming conventions, so I hope the folx developing the query plan UI are doing okay and we haven’t reignited an ancient war *ducks*

        Reply
        • Here are all the non-parallel plan reasons I could find (SQL 2019 and 2022):

          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

          2 new ones for SQL 2022 are marked.

          The following are probably NOT valid reasons, but their location in the SQL binaries is adjacent or mixed with the above (– added by me):

          –Dynamic SQL
          –Excessive Grant
          –Grant Increase
          –UNUSED
          –Used More Than Granted

          Since they’ve been using the same names and naming scheme since 2012:

          https://www.sqlskills.com/blogs/joe/sql-server-2012-execution-plans-nonparallelplanreason/

          I’d say it would break something to add spaces to these values now. The one-word camel-case notation would allow them to become XML elements or attributes, and it’s really not that hard to read.

          Reply
        • Agreed, that’s great! It wasn’t the naming convention on the NonParallelPlanReason values that I was necessarily lamenting though, more the fact that the UI’s inconsistent as all get-out. It obviously works fine for day-to-day use, but there’s potential cognitive dissonance for people like me whose brains are getting slower/have mild OCD symptoms 😀

          Reply
  • I don’t have 2022 to work on but it sounds like the bottom line is that even though a scalar function may start being treated as “inline”, it STILL won’t go parallel… am I thinking right there?

    Reply
    • I have seen cases where an inlined scalar function does go parallel in 2019, and where the calling query goes parallel, but they’ve just been so incredibly unusual.

      Reply
  • Great stuff! Thanks for sharing.

    Reply

Leave a Reply

Your email address will not be published.

Fill out this field
Fill out this field
Please enter a valid email address.