What is the num of NUMA nodes for MAXDOP calculation when there is discrepancy between various ways to count the NUMA nodes?

by

The question:

Querying the dm_os_performance_counters shows that I have 2 NUMA nodes:

select *
from sys.dm_os_performance_counters
where object_name = 'SQLServer:Buffer Node'
and counter_name = 'Page life expectancy'

Querying the sys.dm_os_sys_info shows:

Numa node count 4
CPU count 24
Softnumae_configuration 1
Socket_count 24
Cores per socket 1

What is the num of NUMA nodes for MAXDOP calculation when there is discrepancy between various ways to count the NUMA nodes?

Where as when I right click on SQL server properties, click on Processors it shows 2 NUMA nodes, each with 12 CPUs.

What is the num of NUMA nodes for MAXDOP calculation when there is discrepancy between various ways to count the NUMA nodes?

What is the num of NUMA nodes for MAXDOP calculation when there is discrepancy between various ways to count the NUMA nodes?

SQL error log:

SQL Server detected 24 sockets with 1 cores per socket and 1 logical processors 
per socket. 24 total logical processors.

Automatic soft-NUMA was enabled because SQL server has detected hardware NUMA nodes 
with greater than 8 physical cores.

What is the num of NUMA nodes for MAXDOP calculation when there is discrepancy between various ways to count the NUMA nodes?

What is the num of NUMA nodes for MAXDOP calculation when there is discrepancy between various ways to count the NUMA nodes?

So what is the final value of NUMA and CPUs?

I am asking because it is recommended to have the MAX DOP configured based on the number of NUMA nodes and the processors per node (https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15#Recommendations). How to decide on the MAX DOP when there is discrepancy between processor count reported in sys.dm_os_sys_info vs server properties vs SQL error logs?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

You’re seeing soft-NUMA counts.

As mentioned in this MS Doc…

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 places 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.

In your case, it looks like you have 2 CPUs, each with 12 cores. With those being greater than 8, SQL Server is breaking them up into 4 soft-NUMA nodes at startup, each with 6 cores. With this configuration, you’d want to set MAXDOP to 6.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment