This post has been republished via RSS; it originally appeared at: Premier Field Engineering articles.
You can now set MaxDOP during SQL 2019 CTP3.0 setup. Max Degree Of Parallelism is the sledge hammer to Cost Threshold's tack hammer (also MaxDOP database scoped configuration in SQL 2017+) and getting MaxDOP set correctly can be an art that is highly dependent on your workload. However, there are some best practice guidelines we can follow. Remember that best practices are just a starting place and further tuning may be needed.
Best Practice
The general best practice for MaxDOP is to stay within a NUMA node. This allows us to avoid using remote memory and use memory that is local to that physical processor. This is pretty easy to figure out with just hardware based NUMA, but in SQL 2016 software based NUMA was added. The same rule applies though, we need to set MaxDOP to stay within a soft NUMA node.
Guidelines
What are the defaults for MaxDOP during SQL 2019 CTP3.0 setup? Below is the quick guide matrix, but you should really read our Guidelines for Setting MaxDOP.
SQL Server 2016+
|
Server with single NUMA node |
Less than or equal to 8 logical processors |
Keep MAXDOP at or below # of logical processors |
|
Server with single NUMA node |
Greater than 8 logical processors |
Keep MAXDOP at 8 |
|
Server with multiple NUMA nodes |
Less than or equal to 16 logical processors per NUMA node |
Keep MAXDOP at or below # of logical processors per NUMA node |
|
Server with multiple NUMA nodes |
Greater than 16 logical processors per NUMA node |
Keep MAXDOP at half the number of logical processors per NUMA node with a MAX value of 16 |
SQL Server 2008 to 2014
|
Server with single NUMA node |
Less than or equal to 8 logical processes |
Keep MAXDOP at or below # of logical processors |
|
Server with single NUMA node |
Greater than 8 logical processors |
Keep MAXDOP at 8 |
|
Server with multiple NUMA nodes |
Less than or equal to 8 logical processors per NUMA node |
Keep MAXDOP at or below # of logical processors per NUMA node |
|
Server with multiple NUMA nodes |
Greater than 8 logical processors per NUMA node |
Keep MAXDOP at 8 |
Examples
Although these examples are all from the GUI to show the chosen defaults, note that you can also change the defaults for command line and unattended installs using the /SQLMAXDOP= parameter.
