Check auto-numa is enabled

[jonetta]

I run query

select cpu_count, hyperthread_ratio, softnuma_configuration, softnuma_configuration_desc
from sys.dm_os_sys_info

and got results, where said, that numa is off:

cpu_count   hyperthread_ratio   softnuma_configuration  softnuma_configuration_desc
32          16                  0                       OFF

Then I run another query

SELECT name, value, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced 
FROM sys.configurations WITH (NOLOCK) 
ORDER BY name OPTION (RECOMPILE);

and got result, where said, that auto-numa is not disabled

name                          value value_in_use    
automatic soft-NUMA disabled    0            0      

Where is the right info?

In Task Manager I see node0 and node1.

We use Microsoft SQL Server 2017 (RTM-GDR) (KB4583456) - 14.0.2037.2 (X64) Nov 2 2020 19:19:59 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: )

I have this message in the log - does this mean NUMA is being used correctly and that I don't need to do anything else?

Shows 8 cores:

[Demir]

Just enough information for me to guess what is going on, not quite enough for me to be certain my guess is correct

Here's my guess at a few details, then an explanation of the outcome based on those details. I think you are using a bare metal Windows server, not a VM. I think the server has two sockets, two processors, and two NUMA nodes. Each processor has 8 physical cores. Hyperthreading is enabled, so each NUMA node has 16 logical processors.

I'm guessing this system is SQL Server 2016 or later. SQL Server starts up, detects 8 physical cores per socket on each of two NUMA nodes (even though hyperthreading means each physical core is presented as two logical processors to Windows, and each logical processor is given a SQLOS scheduler).

Even though auto-softNUMA is not disabled, autosoftNUMA does not intervene and create softNUMA nodes on this system since the number of cores per NUMA node is not greater than 8. Thus the result is somewhat confusing. Auto-softNUMA is not disabled, as value = 0 in sys.configurations for 'automatic soft-NUMA disabled' indicates.

But auto-softNUMA has not intervened in the SQLOS configuration at startup, as softnuma_configuration_desc = 'OFF' in sys.dm_os_sys_info indicates.

So the feature is available but didn't do anything because the qualifier of > 8 physical cores per NUMA node wasn't met.

Auto-softNUMA was introduced as an "on by default" feature in SQL Server 2016 (but it only does anything if enough physical cores per socket are detected). The feature was also included in SQL Server 2014 SP2; in that version autosoftnuma is only available when startup trace flag 8079 is enabled and more than 8 physical cores per NUMA node are detected. I don't know if in SQL Server 2014 a setting in sys.configurations for autoSoftNUMA or a column in sys.dm_os_system_info is available - that's why i'm guessing (and hoping) the system the question is based on is SQL Server 2016 or later.

The description above is from Microsoft's trace flag documentation. https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15

The SQL Server log will display startup entries including the processor, NUMA node, core and logical processor information from interrogating the hardware.

Information from the log at startup will agree with the nodes displayed in sys.dm_os_nodes (this dmv includes the dac; other nodes will be soft nodes if set up or SQLOS memory nodes otherwise) and in sys.dm_os_memory_nodes (this dmv will include a SQLOS memory node for the dac; other memory nodes are each expected to have their own "home" NUMA node).