softnuma_configuration_desc value doesnt reflect errorlog softnuma enabled configuration

Question

Hi,

I ran below query in CMS to get info about all the Sql Servers which have soft NUMA enabled and it doesn't help.

select softnuma_configuration_desc from sys.dm_os_sys_info

SQL Server error log says "Automatic soft-NUMA was enabled because SQL Server has detected hardware NUMA nodes with greater than 8 physical cores." But softnuma_configuration_desc value in sys.dm_os_sys_info says "OFF". I noticed this behavior for Sql 2019 in both VM (INTEL ESX host- 2 P, 8 cores) and stand-alone physical server. The NUMA node value does show NODE0 and NODE1 in SSMS so it does have NUMA enabled but why softnuma_configuration_desc doesnt reflect that correctly?

On the other side, I also have seen few Sql 2019 which have ERROLOG and softnuma_configuration_desc says soft-numa enabled but in SSMS its not! These all are VMs and underlying ESX host is AMD with higher CPU counts (1P, 64 cores) but Sql Server even with more than 8vCPUS in SSMS shows NODE0.

Below query looks like gets the right info for soft numa enabled,

select count(counter_name) numa_Count

from sys.dm_os_performance_counters

where object_name = 'SQLServer:Buffer Node'

and counter_name = 'Page life expectancy'
having count(counter_name) > 1

I am trying to come up with right MAXDOP setting for the Sql Servers based on NUMA configuration hence doing the research. New Sql 2019 silent install automatically picks and sets MAXDOP but some of our legacy maintenance scripts reset MAXDOP settings to default 0 which is not ideal as per Microsoft recommendation.

Thanks,
-SreejitG

Answer 1

Hi Sreejitg-8603,
What the edition of SQL Server are you using?
Could you please try to enable soft NUMA again? The following is the recommended sequence of operations:

1. Stop the instance of SQL Server Agent.
2. Execute ALTER SERVER CONFIGURATION SET SOFTNUMA ON; Go
3. Re-start the SQL Server instance.
4. Start the instance of SQL Server Agent.

In addition, you can run the following Query to check how many CPUs SQL Server is able to use:

select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers

And use Task Manager to check the CPU information.

Best Regards,
Amelia