0

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

enter image description here

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

enter image description here

enter image description here

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. 

enter image description here

enter image description here

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?

1

1 Answer 1

3
+50

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.