Can a server wide setting of ‘Max Degree of Parallelism’ = 1 cause Brent Ozar’s sp_BlitzCache to flag execution plan as ‘forced-serialization’?

Posted on

Question :

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?

enter image description here

Answer :

Yes, this would cause that problem.

if nothing stands against it (other services, vms etc) use as values the number of cpus and with hyperthreading the number of all virtual cpus.

For more information and recommendation you can see at

To add a little bit, that check will find any reason that a query is forced to run single threaded:

UPDATE  ##BlitzCacheProcs
    SET is_forced_serial = 1
FROM    #query_plan qp
WHERE   qp.SqlHandle = ##BlitzCacheProcs.SqlHandle
AND     query_plan.exist('/p:QueryPlan/@NonParallelPlanReason') = 1
AND     (##BlitzCacheProcs.is_parallel = 0 
           OR ##BlitzCacheProcs.is_parallel IS NULL)

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.

Leave a Reply

Your email address will not be published. Required fields are marked *