patternsqlMinor
Can a server wide setting of 'Max Degree of Parallelism' = 1 cause Brent Ozar's sp_BlitzCache to flag execution plan as 'forced-serialization'?
Viewed 0 times
candegreecauseflagserializationozarwideexecutionsettingmax
Problem
I am using Brent Ozar's sp_BlitzCache store procedure and I'm attempting to nail down why it is reporting:
"Something in your plan is forcing a serial query. Further
investigation is needed if this is not by design."
Upon investigation I found that the server configuration has set:
(That is on my laundry list to configure correctly. It is a hold over from days of ignorance.)
Is that setting the cause of Brent to report forced serialization?
"Something in your plan is forcing a serial query. Further
investigation is needed if this is not by design."
Upon investigation I found that the server configuration has set:
'Max Degree of Parallelism = 1'(That is on my laundry list to configure correctly. It is a hold over from days of ignorance.)
Is that setting the cause of Brent to report forced serialization?
Solution
To add a little bit, that check will find any reason that a query is forced to run single threaded:
At one point Microsoft documented a list of reasons why a query couldn't go parallel, and how that would be represented in query plan XML, but in practice (with some recent changes in Azure SQL DB) a very limited list of reasons would ever be specifically bubbled up.
Most of the time, all you get for a NonParallelPlanReason is CouldNotGenerateValidParallelPlan. That's why I wrote the check to find any reason generically.
It could potentially be more expressive, or skipped in parallelism-restricted versions (like Express Edition), but for now it will just warn everywhere.
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE ##BlitzCacheProcs
SET is_forced_serial = 1
FROM #query_plan qp
WHERE qp.SqlHandle = ##BlitzCacheProcs.SqlHandle
AND SPID = @@SPID
AND query_plan.exist('/p:QueryPlan/@NonParallelPlanReason') = 1
AND (##BlitzCacheProcs.is_parallel = 0
OR ##BlitzCacheProcs.is_parallel IS NULL)
OPTION (RECOMPILE);At one point Microsoft documented a list of reasons why a query couldn't go parallel, and how that would be represented in query plan XML, but in practice (with some recent changes in Azure SQL DB) a very limited list of reasons would ever be specifically bubbled up.
Most of the time, all you get for a NonParallelPlanReason is CouldNotGenerateValidParallelPlan. That's why I wrote the check to find any reason generically.
It could potentially be more expressive, or skipped in parallelism-restricted versions (like Express Edition), but for now it will just warn everywhere.
Code Snippets
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
UPDATE ##BlitzCacheProcs
SET is_forced_serial = 1
FROM #query_plan qp
WHERE qp.SqlHandle = ##BlitzCacheProcs.SqlHandle
AND SPID = @@SPID
AND query_plan.exist('/p:QueryPlan/@NonParallelPlanReason') = 1
AND (##BlitzCacheProcs.is_parallel = 0
OR ##BlitzCacheProcs.is_parallel IS NULL)
OPTION (RECOMPILE);Context
StackExchange Database Administrators Q#295535, answer score: 5
Revisions (0)
No revisions yet.