3

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.

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.