6

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

3

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

5
  • Thank you nbk. I brought this up with the team that is responsible for handling this particular production server and they asked a good question: "What is the best practice in reality? Have DBAs set this value to 1 because of a flaw? Is a value of > 1 ubiquitous - tried and true?" I answered with: "I would think this is a tried and true setting with rigorous testing and real world usage... but I will research." What would you say to their concern?
    – D-K
    Jul 12 at 16:23
  • a higher number will increase the amount of energy consumption and has so so a drastic influence on the cooling system. As long as you are fully updated, there should be no problem, but i used only SQL2017 and now also 2019 as i encountered some bugs in 2016 back in the day. Also see if you can get hold of the perosn who installed it and ask why he did it, this should normally be documented.
    – nbk
    Jul 12 at 16:46
  • 1
    Just to comment, I can only think of one scenario where MaxDop is supposed to be set to 1 - and that is if the sql instance is running SharePoint.
    – rvsc48
    Jul 12 at 19:41
  • @rvcs48 I talked with a full time DBA in the company who has experience with these servers but more so with a legacy product line. He told me that they found setting it to 1 allowed the 4 core servers to work more harmoniously within the environment at the time. So it appears I am dealing with legacy information. Perhaps they had something like SharePoint. I'm not going to press the issue at this time as what I have read tells me the gains are not always that apparent. If you or anyone disagrees - I welcome the information.
    – D-K
    Jul 12 at 21:15
  • Adding to @rvsc48's comment, Biztalk required MaxDop 1 also. Don't know if that's changed in recent versions. Jul 13 at 17:15
5

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.

4
  • Thank you for taking the time to answer.
    – D-K
    Jul 12 at 16:17
  • I'd like to ask you the same question. Is a setting of 'max-degree-of-parallelism > 1' ubiquitous? The team that is in charge of this particular production server is concerned that the value being set to 1 - was on purpose because of a flaw in SQL server.
    – D-K
    Jul 12 at 16:39
  • 1
    @D-K I'd consider setting maxdop to 1 a worst practice. If the team responsible for the server doesn't understand the setting, or why it's set the way it is, I'd have real questions about why they're managing the server at all. They don't seem qualified. Jul 12 at 17:15
  • My text does leave out some important details. The team recently lost their dedicated DBA and I am backfilling, attempting to dig into some recent issues. Thank you for the information. I will work with the team and plan for setting it to an appropriate value.
    – D-K
    Jul 12 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.