Ask Learn
Preview
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign inThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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:
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:
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
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?
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign in