Share via

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

  • 04/28/2015

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

  • 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?