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

This post has been republished via RSS; it originally appeared at: SQL Server Support articles.

First published on MSDN on Apr 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:

• 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