sys.dm_os_memory_nodes (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Allocations that are internal to SQL Server use the SQL Server memory manager. Tracking the difference between process memory counters from sys.dm_os_process_memory and internal counters can indicate memory use from external components in the SQL Server memory space.

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.

System_CAPS_ICON_note.jpg Note


To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_os_memory_nodes.

Column nameData typeDescription
memory_node_idsmallintSpecifies the ID of the memory node. Related to memory_node_id of sys.dm_os_memory_clerks. Not nullable.
virtual_address_space_reserved_kbbigintIndicates the number of virtual address reservations, in kilobytes (KB), which are neither committed nor mapped to physical pages. Not nullable.
virtual_address_space_committed_kbbigintSpecifies the amount of virtual address, in KB, that has been committed or mapped to physical pages. Not nullable.
locked_page_allocations_kbbigintSpecifies the amount of physical memory, in KB, that has been locked by SQL Server. Not nullable.
single_pages_kbbigintApplies to: SQL Server 2008 through SQL Server 2008 R2.

Amount of committed memory, in KB, that is allocated by using the single page allocator by threads running on this node. This memory is allocated from the buffer pool. This value indicates the node where allocations request occurred, not the physical location where the allocation request was satisfied.
pages_kbbigintApplies to: SQL Server 2012 through SQL Server 2016.

Specifies the amount of committed memory, in KB, which is allocated from this NUMA node by Memory Manager Page Allocator. Not nullable.
multi_pages_kbbigintApplies to: SQL Server 2008 through SQL Server 2008 R2.

Amount of committed memory, in KB, that is allocated by using the multipage allocator by threads running on this node. This memory is from outside the buffer pool. This value indicates the node where the allocation requests occurred, not the physical location where the allocation request was satisfied.
shared_memory_reserved_kbbigintSpecifies the amount of shared memory, in KB, that has been reserved from this node. Not nullable.
shared_memory_committed_kbbigintSpecifies the amount of shared memory, in KB, that has been committed on this node. Not nullable.
cpu_affinity_maskbigintApplies to: SQL Server 2012 through SQL Server 2016.

Internal use only. Not nullable.
online_scheduler_maskbigintApplies to: SQL Server 2012 through SQL Server 2016.

Internal use only. Not nullable.
processor_groupsmallintApplies to: SQL Server 2012 through SQL Server 2016.

Internal use only. Not nullable.
foreign_committed_kbbigintApplies to: SQL Server 2012 through SQL Server 2016.

Specifies the amount of committed memory, in KB, from other memory nodes. Not nullable.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

Requires VIEW SERVER STATE permission on the server.

Dynamic Management Views and Functions (Transact-SQL)
SQL Server Operating System Related Dynamic Management Views (Transact-SQL)

Community Additions

ADD
Show: