Server’s “Max Degree of Parallelism” setting, Resource Governor’s MAX_DOP and query hint MAXDOP–which one should SQL Server use?

SQL Server allows a user to control max degree of parallelism of a query in three different ways.   Just for references, here is a list of documentation:

1. SQL Server wide “max degree of parallelism” configuration is documented in max degree of parallelism Option.   Microsoft Support has recommended guidelines on setting max degree of parallelism per KB “Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server”.
2. Resource Governor’s MAX_DOP is documented in CREATE WORKLOAD GROUP
3. MAXDOP query hint is documented in “Query Hints (Transact-SQL)”

What is effective setting if all or some of these settings are enabled?    Permutations of this can be confusing.   So I decided to do some code research and here is the table of all possible combinations of the settings:

Query Hint (QH)	Resource Governor (RG)	Sp_conifgure	Effective MAXDOP of a query
Not set	Not set	Not set	Server decides (max cpu count up to 64)
Not set	Not set	Set	Use sp_configure
Not set	Set	Not set	Use RG
Not set	Set	Set	Use RG
Set	Not set	Not set	Use QH
Set	Set	Not set	Use min(RG, QH)
Set	Set	set	Use min (RG, QH)
Set	Not set	Set	Use QH

When you reference the above table, please note the following:

1. 0 of any configure (Query hint, Resource governor, or sp_configure) means max dop is not set.  For example if you use option (MAXDOP 0) query hint, it is considered as MAXDOP hint is not set at query level.
2. A query can be set to use serial plan regardless of these settings.  Optimizer decides if a plan is serial plan based on cost and certain TSQL constructs (an example if SQL 2014 query use memory optimized table).
3. Actual DOP can be lower than MAXDOP due to memory or thread shortage. 

 

For reference, my colleague Bob Dorr has written a couple of blogs in this space:

• How It Works: Maximizing Max Degree Of Parallelism (MAXDOP)
• SQL Server MAX DOP Beyond 64 – Is That Possible?

Credits:  I’d like to thank Jay Choe  -- Sr. Software Engineer at Microsoft for reviewing my code research and confirming the findings, and Bob Ward -- CTO CSS AMERICAS at Microsoft for prompting the research on this topic.

 

Jack Li |Senior Escalation Engineer | Microsoft SQL Server

twitter| pssdiag |Sql Nexus

Comments

• Anonymous
  April 29, 2015
  The comment has been removed

• Anonymous
  April 30, 2015
  to answer Curtis's question, yes, you can actually use higher DOP (because of query hint or RG) than sp_configure value

• Anonymous
  May 03, 2015
  Sp_conifgure   is spelt incorrectly in the table.

• Anonymous
  June 02, 2015
  thanks for information

• Anonymous
  May 23, 2017
  JackLi .... Is this chart valid for the 2008R2 version?