HiveBrain v1.2.0
Get Started
← Back to all entries
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'?

Submitted by: @import:stackexchange-dba··
0
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:

'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:

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.