If you already know about Automatic Soft-NUMA in SQL Server 2016, then you probably already read the blog post SQL 2016 – It Just Runs Faster: Automatic Soft NUMA by Robert Dorr, who makes up one half of “the Bobs” that run the BobSQL blog. The other Bob, Bob Ward (@bobwardms), followed that up with How It Works (It Just Runs Faster): Auto Soft NUMA…. Lots of great info there.

So having read up on automatic soft-NUMA, I was eager to see what it did with my main production servers when I upgraded them. My main pair of production servers (they are paired into an Availability Group) have 4 NUMA nodes with 16 physical cores per node and hyperthreading for a total of 32 logical cores per node with 1.5 TB of RAM. Obviously, we are using core-based Enterprise Edition for these servers. I thought I knew what automatic soft-NUMA would do, and wanted to confirm if my expectations were right.

After completing the upgrade (side-by-side migration, not in-place upgrade), I decided to take a quick look at the GUI to see how it was configured. I saw no changes in the GUI at all. 4 NUMA nodes with 32 logical cores in each

So where is my soft-NUMA? Well according to Bob Ward’s post, I should see it in the log file. And I do:

Automatic soft-NUMA was enabled because SQL Server has detected hardware NUMA nodes with greater than 8 physical cores.
...
Node configuration: node 0: CPU mask: 0x0000000055555555:0 Active CPU mask: 0x0000000055555555:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
Node configuration: node 1: CPU mask: 0x00000000aaaaaaaa:0 Active CPU mask: 0x00000000aaaaaaaa:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
Node configuration: node 2: CPU mask: 0x0000000055555555:1 Active CPU mask: 0x0000000055555555:1. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
Node configuration: node 3: CPU mask: 0x00000000aaaaaaaa:1 Active CPU mask: 0x00000000aaaaaaaa:1. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
Node configuration: node 4: CPU mask: 0x0000000055555555:2 Active CPU mask: 0x0000000055555555:2. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
Node configuration: node 5: CPU mask: 0x00000000aaaaaaaa:2 Active CPU mask: 0x00000000aaaaaaaa:2. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
Node configuration: node 6: CPU mask: 0x0000000055555555:3 Active CPU mask: 0x0000000055555555:3. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
Node configuration: node 7: CPU mask: 0x00000000aaaaaaaa:3 Active CPU mask: 0x00000000aaaaaaaa:3. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

What else can I see about soft-NUMA?

I wanted to know what else I could see about my soft-NUMA so I checked the DMVs. According to Books Online, sys.dm_os_sys_info contains info about soft-NUMA. It has 2 columns softnuma_configuration and softnuma_configuration_desc. I wouldn’t get too excited though. The possible values are 0, 1, 2 and off, on, manual.

I would expect to see some better info in sys.dm_os_nodes. Sure enough, the Books Online page for Soft NUMA states that in sys.dm_os_nodes “the node_state_desc column for each NUMA node will show whether the node was created by soft-NUMA or not”. Then in the Books online page for sys.dm_os_nodes, it states that node_state_desc can have 5 modifiers listed including “AUTOSOFT: This node is created by automatic soft-NUMA” and “MANUAL SOFT: This node is created by soft-NUMA with customer defined node size”.

Cool beans! Right? Let’s try it.

Select node_id, node_state_desc, memory_node_id 
From sys.dm_os_nodes;

node_id	node_state_desc	memory_node_id
0	ONLINE	0
1	ONLINE	0
2	ONLINE	1
3	ONLINE	1
4	ONLINE	2
5	ONLINE	2
6	ONLINE	3
7	ONLINE	3
64	ONLINE DAC	64

Well, that was anti-climatic. So the info according to Books Online isn’t there in sys.dm_os_nodes. I emailed the product team about this but it’s the holidays so I haven’t gotten an official response yet. Not sure yet if it is a bug or if the docs need updating; however, I included the column memory_node on purpose. From the info that was returned, I can see that there are now 2 NUMA nodes per physical node (the memory nodes do not get partitioned). I can at least see the number of logical nodes per NUMA node.