Join Us!

Spaces are allowed; punctuation is not allowed except for periods, hyphens, and underscores.
A valid e-mail address. All e-mails from the system will be sent to this address. The e-mail address is not made public and will only be used if you wish to receive a new password or wish to receive certain news or notifications by e-mail.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.

Login

Enter your Project Envision username.
Enter the password that accompanies your username.
Request new password

Sql Server Get Operating System Memory Statistics

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_kbavailable_physical_memory_kb
1572408119200

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_kbavailable_page_file_kb
3849328332252

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_kbkernel_paged_pool_kbkernel_nonpaged_pool_kb
583770817923235848

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_statesystem_low_memory_signal_statesystem_memory_state
10Available physical memory is high