SQL Server World

NUMA is basically a memory and CPU architecture inside the computer machine. NUMA stands for Non-Uniform Memory Access. The purpose of NUMA is for Scheduler (CPU) to have faster access to memory. Each CPU talks to memory controller to get the memory. When there are more than 8 CPUs, single memory controller becomes  bottleneck. This became a scalability issue. NUMA is a hardware configuration which was built to scale beyond the processor and memory limitations of traditional Symmetric-Multi-Process (SMP) systems. If there are 16 CPUs, there will 2 NUMA nodes (node 0 and 1). Each node will have its own memory controller and serves up to 8 CPU. CPU and Memory are partitioned at the hardware level to improve the access to memory. SQL Server is NUMA aware application. You don’t have to enable it. Almost all modern computer hardware now has this hardware NUMA.

I would highly recommend anyone new to NUMA to read this article written by SQLSkills team.

Following is the image of SMP configuration where all processors have equal access to memory and I/O. As you can imagine, increasing the number of CPU in this architecture will create a bottleneck because there is only one memory controller and one I/O controller.

In Fig 2 below you are seeing 2 NUMA nodes and CPU and Memory are partitioned. Each NUMA node gets its own memory and I/O controller.

Automatic Soft NUMA:

Modern processors have multiple cores per socket. Each socket is represented, usually, as a single NUMA node. The SQL Server database engine partitions various internal structures and partitions service threads per NUMA node. With processors containing 10 or more cores per socket, using software NUMA to split hardware NUMA nodes generally increases scalability and performance. Prior to SQL Server 2014 (12.x) SP2, software-based NUMA (soft-NUMA) required you to edit the registry to add a node configuration affinity mask, and was configured at the host level, rather than per instance. Starting with SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x), soft-NUMA is configured automatically at the database-instance level when the SQL Server Database Engine service starts. Please read this  documentation and this documentation for more understanding.

Note 1: Hot-add processors are not supported by soft-NUMA

Note 2: Starting with SQL Server 2014 (12.x) SP2, use trace flag 8079 to allow SQL Server to use Automatic Soft-NUMA. Starting with SQL Server 2016 (13.x) this behavior is controlled by the engine and trace flag 8079 is not required.

The benefits of soft-NUMA include reducing I/O and lazy writer bottlenecks on computers with many CPUs. Soft NUMA allows you to further divide schedulers in smaller group. You can read a really good description about how NUMA works here. 

NUMA can match memory with CPUs through specialized hardware (hardware NUMA) or by configuring SQL Server memory (soft-NUMA). During startup, SQL Server configures itself based on underlying operating system and hardware configuration or the soft-NUMA setting. For both hardware and soft-NUMA, when SQL Server starts in a NUMA configuration, the SQL Server log records a multimode configuration message for each node, along with the CPU mask.

Here are couple of useful queries I use to find out about NUMA layout of my customer’s SQL Server. I am using Glenn’s Performance Queries modified on September 13, 2020.

— Get instance-level configuration values for instance  (Query 4) (Configuration Values)

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

In above query result,  focus on these settings:

1. automatic soft-NUMA disabled (should be 0 in most cases)
2. backup checksum default (should be 1)
3. backup compression default (should be 1 in most cases)
4. clr enabled (only enable if it is needed)
5. cost threshold for parallelism (depends on your workload)
6. lightweight pooling (should be zero)
7. max degree of parallelism (depends on your workload and hardware)
8. max server memory (MB) (set to an appropriate value, not the default)
9. optimize for ad hoc workloads (should be 1)
10. priority boost (should be zero)
11. remote admin connections (should be 1)
12. tempdb metadata memory-optimized (0 by default, some workloads may benefit by enabling)

Following query gives you some useful information about the composition and relative load on your NUMA nodes. You want to see an equal number of schedulers on each NUMA node.  Watch out if SQL Server 2019 Standard Edition has been installed on a physical or virtual machine with more than four sockets or more than 24 physical cores.

–SQL Server NUMA Node information  (Query 12) (SQL Server NUMA Info)

SELECT node_id, node_state_desc, memory_node_id, processor_group, cpu_count, online_scheduler_count,   idle_scheduler_count, active_worker_count, avg_load_balance, resource_monitor_state FROM sys.dm_os_nodes WITH (NOLOCK)WHERE node_state_desc <> N’ONLINE DAC’ OPTION (RECOMPILE);

Automatic Soft-NUMA is enabled by default. It is recommended best practice to leave it enabled.

To disable Automatic Soft-NUMA, run the following command.

USE master; 

GO 

EXEC sp_configure ‘automatic soft-NUMA disabled’, ‘1’; 

RECONFIGURE WITH OVERRIDE;