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?
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.
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:
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 🙂