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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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 Reply