MaxDOP Added to SQL 2019 CTP3.0 Setup

You can now set MaxDOP during SQL 2019 CTP3.0 setup. MaxDOP 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. Click the image to enlarge it.