Set MAXDOP for best performance

The maximum degree of parallelism (MAXDOP) is a server configuration option for running SQL Server on multiple CPUs. It controls the number of processors used to run a single statement in parallel plan execution. The default value is 0, which enables SQL Server to use all available processors. This can affect performance, and isn’t optimal for most use cases.

Use the following guidelines when you configure the MAXDOP value for SQL Server.

NUMA nodes	Logical processors	MAXDOP value
Single	≤ 8	4, 2, or number of cores (for one or two cores)
Single	> 8	8, 4, or 2
Multiple	≤ 16	8, 4, or 2
Multiple	> 16	16, 8, 4, or 2

Note

Setting MAXDOP to 2, 4, or 8 generally provides the best results in most use cases. We recommend that you test your workload and monitor for any parallelism-related wait types such as CXPACKET.

You can use the following query to gather the current NUMA configuration for SQL Server 2016 and later versions:

select @@SERVERNAME,
SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),
cpu_count, 
hyperthread_ratio, 
softnuma_configuration, 
softnuma_configuration_desc, 
socket_count, 
numa_node_count 
from 
sys.dm_os_sys_info

where:

• cpu_count refers to the number of logical CPUs in the system.

• hyperthread_ratio is the ratio of the number of cores that are exposed by one physical processor.

• softnuma_configuration is 0, 1, or 2:

  • 0 (OFF): default

  • 1 (automated): soft-NUMA

  • 2 (manual): soft-NUMA

• softnuma_configuration_desc is OFF, ON, or MANUAL:

  • OFF indicates that the soft-NUMA feature is off.

  • ON indicates that SQL Server automatically decides the NUMA node sizes.

  • MANUAL indicates that soft-NUMA is manually configured.

• socket_count is the number of processor sockets.

• numa_node_count is the number of NUMA nodes available in the system.

To check the current MAXDOP value, use:

$ sp_configure 'max_degree_of_parallelism'

For more information about MAXDOP, see the Microsoft SQL Server documentation.