Configure MAXDOP (max degree of parallelism) on SQL Server 2016… 2019)

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

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 

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!

Microsoft links

https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-2017

Comment List
Related
Recommended