sys.dm_os_sys_memory (Transact-SQL)

 

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

Returns memory information from the operating system.

SQL Server is bounded by, and responds to, external memory conditions at the operating system level and the physical limits of the underlying hardware. Determining the overall system state is an important part of evaluating SQL Server memory usage.

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_sys_memory.

Column nameData typeDescription
total_physical_memory_kbbigintTotal size of physical memory available to the operating system, in kilobytes (KB).
available_physical_memory_kbbigintSize of physical memory available, in KB.
total_page_file_kbbigintSize of the commit limit reported by the operating system in KB
available_page_file_kbbigintTotal amount of page file thatis not being used, in KB.
system_cache_kbbigintTotal amount of system cache memory, in KB.
kernel_paged_pool_kbbigintTotal amount of the paged kernel pool, in KB.
kernel_nonpaged_pool_kbbigintTotal amount of the nonpaged kernel pool, in KB.
system_high_memory_signal_statebitState of the system high memory resource notification. A value of 1 indicates the high memory signal has been set by Windows. For more information, see CreateMemoryResourceNotification in the MSDN library.
system_low_memory_signal_statebitState of the system low memory resource notification. A value of 1 indicates the low memory signal has been set by Windows. For more information, see CreateMemoryResourceNotification in the MSDN library.
system_memory_state_descnvarchar(256)Description of the memory state. See the table below.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.
ConditionValue
system_high_memory_signal_state = 1

and

system_low_memory_signal_state = 0
Available physical memory is high
system_high_memory_signal_state = 0

and

system_low_memory_signal_state = 1
Available physical memory is low
system_high_memory_signal_state = 0

and

system_low_memory_signal_state = 0
Physical memory usage is steady
system_high_memory_signal_state = 1

and

system_low_memory_signal_state = 1
Physical memory state is transitioning

The high and low signal should never be on at the same time. However, rapid changes at the operating system level can cause both values to appear to be on to a user mode application. The appearance of both signals being on will be interpreted as a transition state.

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: