What is the correct setting for (Max Degree of Parallelism)

Question

Hi,

We've set it to 8 in (Max Degree of Parallelism) because we used another version of SQL and had a lower amount of vCPU than we currently have.

Microsoft's best practice documentation for SQL 2016 says that if I have above 16 logical processors I should configure 16.

Today we have 20 logical processors.

Thanks.

Answer 1

Hi @Sandro Alves ,

＞＞Shouldn't the correct one be (num_node_count) should it be 2, following the operating system's configuration? If the answer is no, what explains this value of 4 that SQL has set?

No, the two NUMAs are different.
NUMA Nodes: 2=>This is physical NUMA, each NUMA has its own memory.
num_node_count: 4=>SQL server soft NUMA only has the CPU architecture and does not have its own memory.

> Talking to our DBA team they said why I need to reduce from 8 to 5, that it will slow down.

5 is the recommend value, it is not a fixed value. It depends on your environment and the workload.
The MAXDOP option to limit the number of processors to use in parallel plan execution. It decides how many threads in SQL run a transaction.

For example, if you have a statement that spends 0.000000000000000001ms, the SQL server only use one thread for this statement, even if you set MAXDOP to 20.

SQL server will judge for itself that this statement is time-consuming and resource-intensive. When it thinks it's more, it will open multiple threads (according to MAXDOP) to run this statement as soon as possible. So it is dynamic. There is no fixed setting. It depends on your environment and queries that you will execute.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Hi @Sandro Alves ,

According to this MS document, if your server with multiple NUMA nodes, and processors is more than 16 logical processors per NUMA node, please keep MAXDOP at half the number of logical processors per NUMA node with a MAX value of 16. But how many NUMA nodes do you have in your server? If your Server with single NUMA node, your processor is 20, you need to keep MAXDOP at 8. If you has 2 NUMA nodes, then processors is less than 16 logical processors per NUMA node, please keep MAXDOP at or below # of logical processors per NUMA node (MAXDOP <=10).

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:
o0 (OFF): default
o1 (automated): soft-NUMA
o2 (manual): soft-NUMA
•softnuma_configuration_desc is OFF, ON, or MANUAL:
oOFF indicates that the soft-NUMA feature is off.
oON indicates that SQL Server automatically decides the NUMA node sizes.
oMANUAL 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.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

First, information that is missing, how many NUMA nodes do you have in your server?
Based on the configuration of the virtual machine, the underlying hypervisor, and how SQL Server handles vNUMA.

More Information needed?
https://techcommunity.microsoft.com/t5/sql-server-support-blog/sql-server-clarifying-the-numa-configuration-information/ba-p/316753
https://blogs.vmware.com/performance/2017/03/virtual-machine-vcpu-and-vnuma-rightsizing-rules-of-thumb.html

Assuming or as an example that two sockets are identical with 2 NUMA nodes....

You have 2 NUMA nodes with each 10 cores = "Less than or equal to 16 virtual processors per NUMA node" => Keep at or below 10 => 8 is ok but can be raised to 10.

But first, you have to check how many (v)NUMA nodes SQL Server is presented.