The Microsoft SQL Server max degree of parallelism (MAXDOP) configuration option controls the number of processors that are used for the execution of a query in a parallel plan.
This topic has been discussed for many years, the best practices have changed more than once (see my old post about it – https://robertostefanettinavblog.com/2015/06/04/sql-server-maxdop-configuration-option-for-microsoft-dynamics-nav/)
Microsoft has now released a new table for setting this parameter with the recommended values for SQL Server 2016 or higher; it’s worth following these tips because they follow the evolution of the product.
Starting with SQL Server 2016 (13.x), during service startup if the Database Engine detects more than eight physical cores per NUMA node or socket at startup, soft-NUMA nodes are created automatically by default. The Database Engine takes care of placing logical processors from the same physical core into different soft-NUMA nodes. The recommendations in the table below are aimed at keeping all the worker threads of a parallel query within the same soft-NUMA node. This will improve the performance of the queries and distribution of worker threads across the NUMA nodes for the workload.
Starting with SQL Server 2016 (13.x), use the following guidelines when you configure the max degree of parallelism server configuration value:
Server with single NUMA node
Less than or equal to 8 logical processors
Keep MAXDOP at or below # of logical processors
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
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
Note: NUMA node in the above table refers to soft-NUMA nodes automatically created by SQL Server 2016 (13.x) and higher versions. Use these same guidelines when you set the max degree of parallelism option for Resource Governor Workload groups.
This is a best practice!