1

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:

Improve this question
0

1 Answer 1

Reset to default
1

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

The description above is from Microsoft's trace flag documentation. DBCC TRACEON - Trace Flags (Transact-SQL)

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

Errorlog

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

Improve this answer
3
  • Excellent. So autosoftNUMA is not disabled via configuration in SQL Server. But it is not taking action to modify the SQLOS node configuration, because it is detecting 8 cores per socket rather than more than 8 per socket. You're good to go. Commented May 23, 2022 at 12:14
  • so i need to configure numa as described there? - learn.microsoft.com/en-us/sql/database-engine/configure-windows/… Commented May 23, 2022 at 12:17
  • 1
    You can manually configure autosoftNUMA, yes. i personally wouldn't unless there was a solid reason to do so. Commented May 23, 2022 at 12:30

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.