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:

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 (4)

  1. Curtis Krumel says:

    The only line in the chart above that isn't using the minimum setting is the line where RG and sp_config are both set. The chart says that in this case use RG. So the optimizer could actually use a higher MaxDOP than is set in sp_config if the RG sets it higher?

  2. JackLi says:

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

  3. rob says:

    Sp_conifgure   is spelt incorrectly in the table.

  4. Shanky_621 says:

    thanks for information

Skip to main content