Skip to content

Latest commit

 

History

History
62 lines (52 loc) · 4.55 KB

sys-dm-os-sys-memory-transact-sql.md

File metadata and controls

62 lines (52 loc) · 4.55 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
sys.dm_os_sys_memory (Transact-SQL)
sys.dm_os_sys_memory (Transact-SQL)
rwestMSFT
randolphwest
02/27/2023
sql
system-objects
reference
dm_os_sys_memory
sys.dm_os_sys_memory_TSQL
sys.dm_os_sys_memory
dm_os_sys_memory_TSQL
sys.dm_os_sys_memory dynamic management view
TSQL
>=aps-pdw-2016||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

sys.dm_os_sys_memory (Transact-SQL)

[!INCLUDE sql-asdbmi-asa-pdw]

Returns memory information from the operating system.

[!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] memory usage.

Note

To call this from [!INCLUDEssazuresynapse-md] or [!INCLUDEssPDW], use the name sys.dm_pdw_nodes_os_sys_memory. [!INCLUDEsynapse-analytics-od-unsupported-syntax]

Column name Data type Description
total_physical_memory_kb bigint Total size of physical memory available to the operating system, in kilobytes (KB).
available_physical_memory_kb bigint Size of physical memory available, in KB.
total_page_file_kb bigint Size of the commit limit reported by the operating system in KB
available_page_file_kb bigint Total amount of page file that is not being used, in KB.
system_cache_kb bigint Total amount of system cache memory, in KB.
kernel_paged_pool_kb bigint Total amount of the paged kernel pool, in KB.
kernel_nonpaged_pool_kb bigint Total amount of the nonpaged kernel pool, in KB.
system_high_memory_signal_state bit State 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_state bit State 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_desc nvarchar(256) Description of the memory state. See the table below.
pdw_node_id int Applies to: [!INCLUDEssazuresynapse-md], [!INCLUDEssPDW]

The identifier for the node that this distribution is on.
Condition Value
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.

Permissions

Requires VIEW SERVER STATE permission on the server.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

See also

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