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:
|
1 2 3 4 5 |
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140; /* 2017 */ GO DECLARE @TableVariable TABLE(Total BIGINT); INSERT INTO @TableVariable (Total) SELECT COUNT(*) FROM dbo.Votes; |
The execution plan gives me way more details:
Awww yeah! Here’s another example using a scalar user-defined function:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE OR ALTER FUNCTION dbo.RightHereRightNow ( @Meaningless INT ) RETURNS DATETIME2 WITH RETURNS NULL ON NULL INPUT, SCHEMABINDING AS BEGIN DECLARE @Current DATETIME2; SELECT @Current = GETDATE(); RETURN @Current; END; GO SELECT COUNT(*) FROM dbo.Votes WHERE dbo.RightHereRightNow(0) > '1900-01-01'; |
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:
|
1 2 3 4 |
DECLARE @TableVariable TABLE(Total BIGINT); INSERT INTO @TableVariable (Total) SELECT COUNT(*) FROM dbo.Votes WHERE dbo.RightHereRightNow(0) > '1900-01-01'; |
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:
|
1 2 3 4 |
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 160; /* 2022 */ GO SELECT COUNT(*) FROM dbo.Votes WHERE dbo.RightHereRightNow(0) > '1900-01-01'; |
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.
10 Comments. Leave new
I suppose asking them to put spaces between the words would be a step too far, huh.
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.
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*
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.
Very nice! That’s a great list!
Awesome list, Rob. Thanks for digging it out and posting it.
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 😀
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?
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.
Great stuff! Thanks for sharing.