Sql-server – NUMA config – sp_blitz output

numasp-blitzsql server

I'm trying to learn more about how SQL Server works with NUMA nodes as I noticed something in the output of sp_Blitz that I didn't understand:

Node: 0 State: ONLINE Online schedulers: 8 Offline schedulers: 0 Processor Group: 0 Memory node: 0 Memory VAS Reserved GB: 1343

Node: 1 State: ONLINE Online schedulers: 8 Offline schedulers: 0 Processor Group: 0 Memory node: 0 Memory VAS Reserved GB: 1343

Node: 2 State: ONLINE Online schedulers: 8 Offline schedulers: 0 Processor Group: 0 Memory node: 1 Memory VAS Reserved GB: 0

Node: 3 State: ONLINE Online schedulers: 8 Offline schedulers: 0 Processor Group: 0 Memory node: 1 Memory VAS Reserved GB: 0

The server has 32 logical processors so the numbers above make sense. We're running SQL Server 2016 SP2 Enterprise Edition

I have a conceptual understanding of NUMA from online reading and need to do much more research, however in the short term, I'd like to understand the meaning/importance of VAS Reserved, why it differs between the nodes above, and whether the difference is anything to be concerned about.

This is the query (taken from sp_Blitz):

SELECT  'Node: ' + CAST(n.node_id AS NVARCHAR(10)) + ' State: ' + node_state_desc
        + ' Online schedulers: ' + CAST(n.online_scheduler_count AS NVARCHAR(10)) + ' Offline schedulers: ' + CAST(oac.offline_schedulers AS VARCHAR(100)) + ' Processor Group: ' + CAST(n.processor_group AS NVARCHAR(10))
        + ' Memory node: ' + CAST(n.memory_node_id AS NVARCHAR(10)) + ' Memory VAS Reserved GB: ' + CAST(CAST((m.virtual_address_space_reserved_kb / 1024.0 / 1024) AS INT) AS NVARCHAR(100))
FROM sys.dm_os_nodes n
INNER JOIN sys.dm_os_memory_nodes m ON n.memory_node_id = m.memory_node_id
OUTER APPLY (SELECT
COUNT(*) AS [offline_schedulers]
FROM sys.dm_os_schedulers dos
WHERE n.node_id = dos.parent_node_id
AND dos.status = 'VISIBLE OFFLINE'
) oac

WHERE n.node_state_desc NOT LIKE '%DAC%'
ORDER BY n.node_id OPTION (RECOMPILE);

Thanks.

Best Answer

You have two physical NUMA nodes (Memory Nodes), with Automatic Soft-Numa dividing each one into two NUMA nodes in SQL Server.

I'd like to understand the meaning/importance of VAS Reserved

Virtual Address Space (VAS) is reserved as a first step to allocating memory, see: Reserving and Committing Memory. For 64bit processes the VAS is immense, and so it's common practice to reserve a huge block of it for later use. This doesn't allocate any physical memory, it just reserves a contiguous range of VAS. Later the application will "Commit" chunks of this address space to map the VAS to either physical RAM or the page file.

And in a NUMA system the memory allocation happens on the same node that the code is running on. So that query just shows that a large VAS range was reserved on Node 0, probably at startup.

But SQL Server also supports a secondary method of memory allocation using the Address Windowing Extensions APIs originally added to Windows to allow 32bit applications to address more than 4GB of ram. AWE allocations have the benefit of being exempt from the Virtual Memory Manager paging. SQL Server calls memory allocated this way Locked Pages, and will use them if it has the Lock Pages In Memory permission. AWE allocations won't show up under Reserved or Committed VAS, and are tracked separately in SQL Server's DMVs.

To to see the total amount of memory in use by a node you need to add virtual_address_space_committed_kb and locked_page_allocations_kb

sys.dm_os_memory_nodes

And, no I have no idea why sp_Blitz reports on VAS reservations instead of real memory allocations.

Related Solutions

Sql-server – SQL Server Virtualization best practices – NUMA, HT, Cores vs Speed

The best thing you could do is read the VMWare guide "SQL Server on VMWare Best Practices Guide". This holds all the information you need. However...

Unless the instance is configured as a "wide VM", taking vCores from each socket, then you won't see 2 NUMA nodes. With Hyperthreading enabled, then you're only using the cores on one socket. (3.3.4 NUMA Consideration).
From the best practices guide: "VMware recommends setting the number of cores per socket to one when allocating virtual CPUs to VMs on the vSphere platform. That means that a VM that requires 8 CPUs will have 8 vCPUs with 1 core each." (3.3.5 Cores per Socket). Test this yourself though.
Where I'm working, the answer is "it depends". Some instances we leave it enabled, and some we don't. However... "VMware recommends enabling Hyper-threading in the BIOS so that ESXi can take advantage of this technology." (3.3.3 Hyper-threading)
If there are going to be no other instances on the host, then it's not as big a factor as if there's going to be more than one instance. It would be of benefit to have those allocated to separate sockets if you're looking to maximise performance. Unfortunately, you'd need to do quite a lot of testing and data capture to make an objective determination - I haven't found anything from VMWare specifically relating to this.

Go through the entire guide and read it: http://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf.

Other considerations are checking power schemes, because these can throttle CPUs so low that you get very poor performance, even from the best CPUs you can afford.

SQL Server – Not Using All NUMA Memory with 20 Core Limit and Affinity Mask

In general, SQL Server grows used memory only as needed. You cannot conclude that memory local to a NUMA node is not available to SQL Server because each NUMA node has 128 GB of memory and a query suggests that only 90 GB of memory is in use. If you'd like to validate your configuration I know of a number of ways to do that, but not all of them are production friendly. In all cases, you can compare the results that you get now with what you had with the default affinity mask.

1) Look at the results of sys.dm_os_memory_nodes. If you configured SQL Server correctly you should see rows for memory_node_id 0 and 1. That means that SQL Server has access to all of the memory on your server.

2) Look at the results of sys.dm_os_memory_clerks for your favorite memory clerk that is NUMA aware. For example, you can see if the buffer pool is spread over multiple NUMA nodes:

SELECT memory_node_id, pages_kb
FROM sys.dm_os_memory_clerks
WHERE [type] = N'MEMORYCLERK_SQLBUFFERPOOL';

You are likely to see pages for both memory node 0 and 1. It's possible that you won't, and that doesn't necessarily represent a problem.

3) Look at the results of sys.dm_exec_query_resource_semaphores. Target_memory_kb should be around 70-80% of max server memory. If it's closer to 96 GB than 192 GB then you might have a problem.

4) Restart SQL Server after enabling TF 834 as a startup parameter. With trace flag 834, SQL Server upon startup grows the buffer pool using large pages to the maximum size that it is able to do. You might not see max server memory if the memory within the guest OS is fragmented. Enabling this trace flag will have some effect on your workload, so I can only recommend running a test like this in a non-production scenario. However, it might be a good option if you truly need to see SQL Server using all of the memory that it can with your own eyes.

Best Answer

Related Solutions

Sql-server – SQL Server Virtualization best practices – NUMA, HT, Cores vs Speed

SQL Server – Not Using All NUMA Memory with 20 Core Limit and Affinity Mask

Related Question