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

All we need is an easy explanation of the problem, so here it is.

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?

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

https://docs.microsoft.com/en-US/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15

Method 2

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply