Often Database Administrators would like to check memory usage on operating system level. Of course they can use many tools that works on operating system level. However Sql Server 2008 introduced the new dynamic management view sys.dm_os_sys_memory that enables DBA to use simple query to get those statistics.

The dynamic management view sys.dm_os_sys_memory returns returns a complete picture of memory on operating system level, including information about total and available physical memory, total and available page memory, system cache, kernel space and others.

Below is small example

select total_physical_memory_kb, available_physical_memory_kb 
from sys.dm_os_sys_memory
 
total_physical_memory_kb
available_physical_memory_kb
1572408
119200
 

The above query shows total operating system memory in column total_physical_memory_kb and available memory in column available_physical_memory_kb

Information about page file memory can be retrieved from total_page_file_kb and available_page_file_kb columns as ilustrated below

select total_page_file_kb, available_page_file_kb 
from sys.dm_os_sys_memory
 
total_page_file_kb
available_page_file_kb
3849328
332252
 

The sys.dm_os_sys_memory also contains information about system cache memory, and kernel pool. The example how to retrieve those information is provided below.

select system_cache_kb, kernel_paged_pool_kb, kernel_nonpaged_pool_kb 
from sys.dm_os_sys_memory
 
system_cache_kb
kernel_paged_pool_kb
kernel_nonpaged_pool_kb
5837708
179232
35848
 

The last part of information that can be retrieved from sys.dm_os_sys_memory is the state of memory on operating system level. Below columns describe if the amount of memory at operating system level is low or high.

select system_high_memory_signal_state, system_low_memory_signal_state,
system_memory_state_desc as system_memory_state
from sys.dm_os_sys_memory
 
system_high_memory_signal_state
system_low_memory_signal_state
system_memory_state
1
0
Available physical memory is high