Contents
Where to find Numa configuration information in SQL Server?
When SQL Server starts up is outputs the NUMA information in the error log detailing its view of NUMA and provides DMV outputs to show the information as well. Here is the output from my 2 Processor Group system. A NUMA node can’t span a processor group so I am assured to have 2 NUMA nodes with CPUs associated with each node.
When to create soft NUMA nodes in SQL Server?
With SQL Server 2016 (13.x), whenever the SQL Server Database Engine detects more than eight physical cores per NUMA node or socket at startup, soft-NUMA nodes are created automatically by default. Hyper-threaded processor cores are not differentiated when counting physical cores in a node.
How many cores does a soft NUMA have?
When the detected number of physical cores is more than eight per socket, the SQL Server Database Engine creates soft-NUMA nodes that ideally contain eight cores, but can go down to five or up to nine logical cores per node.
Why is one NUMA node more idle than the other?
It seems that I have triggered with that workload a NUMA Node Imbalance problem, where only 1 NUMA handles the whole SQL Server workload, and the other NUMA Node is just idle.
Why are NUMA nodes swapped in SQL Server?
Since the system usually allocates things on NUMA node 0 during boot, SQL Server tries to move its node 0 to a subsequent node to avoid contention points. Thus, in my case I see the groups appear to be swapped but that was really just a node swap and instead of SQL Server node 0 being associated with group 0 it is associated with group 1.
What does numanode0 mean in SQL Server configuration?
Notice that ‘NumaNode0’ is shown. It is just a poor choice of wording and is exposing the same node 0 that SQL Server is using when you look at the DMVs for tracking the CPUs on the system within the scheduling node. Everything is technically correct that we have a single (1) memory bank associated with all the CPUs on the system.
How are memory nodes created in SQL Server?
Nodes are created per physical NUMA memory nodes. These might be different from the CPU nodes in sys.dm_os_nodes. No allocations done directly through Windows memory allocations routines are tracked. The following table provides information about memory allocations done only by using SQL Server memory manager interfaces.