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

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?

Answer 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://learn.microsoft.com/en-US/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15

Answer 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.