sys.dm_os_process_memory – Day 19 – One DMV a Day

Hello Geeks and welcome to the Day 19 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

We have so far covered the DMVs related to SQL OS and waits. Today I will be starting with the memory related DMVs. A very grey and confused area for most of the DBAs. Let us start with sys.dm_os_process_memory.

Sys.dm_os_process_memory is the DMV which tells you what is the memory usage by the SQL Server process. Whenever there is a high memory usage it help you understand if the problem is internal to SQL Server. There are other ways to check this. I will show few of them in this blog to connect the dots.

SELECT physical_memory_in_use_kb AS Actual_Usage,
	large_page_allocations_kb AS large_Pages,
	locked_page_allocations_kb AS locked_Pages,
	virtual_address_space_committed_kb AS VAS_Committed,
	large_page_allocations_kb + locked_page_allocations_kb + 427000
FROM sys.dm_os_process_memory

From the above output of sys.dm_os_process_memory and resource monitor we could come to the below conclusion.

Actual physical memory = large pages + locked pages + working set.

You will also observe that the VAS committed is slightly larger than the Actual physical memory. This is also accounted and used by SQL Server as a service. But the engine does not use this memory. This memory is used by SQL OS for other components like threads, stack, etc. These cannot be locked by SQL Server. Other components of memory can be locked by SQL Server like Buffer pool, plan cache, etc.

You can also see this information from sys.dm_os_sys_info from the columns physical_memory_kb, committed_kb.

SELECT physical_memory_kb,
	committed_kb
FROM sys.dm_os_sys_info

All the above information is valid for SQL 2012 onwards. Till SQL Server 2008 R2 there are few page allocations which used to happen out of the buffer pool. These allocations were not controlled by buffer manager. From 2012 these were brought into buffer pool. So you never see a memory leak due to page allocations from 2012 with efficient design on memory management. Your Total Server Memory will never go beyond Target Server Memory.

Now you know how to answer the question when someone asks “what is the memory used by SQL Server?” Just use sys.dm_os_process_memory. Last one to mention if you are not already aware.

Target Server Memory = Max server memory set for your instance
Total Server Memory = Actual usage of memory of your Instance

Tomorrow we will look at another DMV related to memory. So, stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook