You just got a call from an application owner telling you his application isn’t running as fast as normal, and can you check the SQL instance his database is sitting on to see what’s going on?  You first want to check what’s going on in terms of memory being used, at least I do, but you just want something simple that returns a quick message that says if the instance is under any memory pressure.  The question is which of the numerous memory based DMVs do you use?  For me, if I want a quick check of the memory being used I want to look at sys.dm_os_sys_memory.  This DMV was introduced in SQL 2008 and returns memory information from the operating system.

Here is the syntax:

select

total_physical_memory_kb / 1024 as phys_mem_mb,

available_physical_memory_kb / 1024 as avail_phys_mem_mb,

system_cache_kb /1024 as sys_cache_mb,

(kernel_paged_pool_kb+kernel_nonpaged_pool_kb) / 1024

as kernel_pool_mb,

total_page_file_kb / 1024 as total_page_file_mb,

available_page_file_kb / 1024 as available_page_file_mb,

system_memory_state_desc

from sys.dm_os_sys_memory

Here is the result:

What is this telling me?

phys_mem_mb – Total size of physical memory available to the operating system (in MB)

available_physical_memory_kb – Size of physical memory available (in MB)

system_cache_kb – Total amount of system cache memory (in MB)

kernel_paged_pool_kb – Total amount of the paged kernel pool

kernel_nonpaged_pool_kb – Total amount of the nonpaged kernel pool (Combined with the above result in MB)

total_page_file_kb – Size of the commit limit reported by the operating system (in MB)

available_page_file_kb – Total amount of page file that is not being used (in MB)

system_memory_state_desc – Description of the memory state.  This entry comes from a API fIunction described here – https://msdn.microsoft.com/en-us/library/aa366541(VS.85).aspx

The entire list of available values for the sys.dm_os_sys_memory DMV can be found here – https://msdn.microsoft.com/en-us/library/bb510493.aspx

Now, thanks to that last column, I know my memory is ok, I can look into something else (which we will go into on a later post).

One of the things I’ve done at my company is create a SSRS report based on this, and have the report emailed to me every morning.  This way each morning I can check on my instances memory right from my email.  Also, if I do get any calls regarding performance issues I can simply click on a bookmark on my browser and bring up the report right away.  This saves me having to log into a jump server, and then into my SQL instance to run this query.