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:
-
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...
”.
-
Resource Governor’s MAX_DOP is documented in
CREATE WORKLOAD GROUP
-
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:
-
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.
-
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).
-
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