Home,   Parent
Contact: jchang6 at yahoo.com or jchang61 at outlook.com

System Architecture DMVs and NUMA

There are several DMVs that provide information on system architecture, particularly those NUMA related.

NUMA nodes are available from sys.dm_os_nodes and sys.dm_os_memory_nodes.

The memory_node_id rows, excluding value 64 is the number of NUMA nodes.
The online_scheduler_count in the first DMV is the number of logical processors.
Both have a column for processor_group, applicable to large systems having more than 64 logical processors.

The DMV sys.dm_os_schedulers. enumerates the individual scheduler_id's.
The relation between scheduler_id and cpu_id is not spelled out clearly, and may vary with SQL Server version.

In SQL Server 2016, on a system with 2 processor sockets (NUMA nodes), 18 cores per socket, Hyper-Threading enabled, 2 per core, 36 cores total, 72 logical processors total, the pattern is as follows.

Scheduler increments by for each of the cores in the first node from 0 to 17 (18 values), while cpu_id increments by 2 from 0, 2, ... 34 (also 18 values).

Then, still on the first node, for scheduler_id values 18 to 35 (second set of 18 values), cpu_id increments by 2 from 1, 3, ... 35.

The implication is that the operating system enumerates both logical processors with adjacent values, while SQL Server enumerates one LP per core in a node before

The pattern then repeats on the second node. Scheduler_id starts from 36, but cpu_id starts from 64.

In SQL Server 2017, on a 2S system with 8 core processors, HT enabled, scheduler_id and cpu_id are aligned from 0 to 31.

If there is situation where they are different, please advise.

The DMV sys.dm_os_memory_node_access_stats reports the local and remote node operations for each NUMA node. There is overhead for this DMV, and Trace flag 842 enables the DMV.

dm_os_memory_node_access_stats does not appear in SQL Server online documentation
(SQL Server Operating System Related Dynamic Management Views).

There are references, including Microsoft SQL Server 2012 Unleashed.

Also, see Aaron Morelli SQL Server Trace Flags SQLOS Memory and Buffer Pool.