Sql Server - Detecting and Monitoring Memory Pressure

Introduction - Detecting and Monitoring Memory Pressure 

I will be writing this article in four parts because this topic is huge and amount of information can be overwhelming for readers and for me also. So we will be exploring it step by step.

1.  DBCC MemoryStatus
2.  Using DMV
3.  Alert and perfmon counters

As a DBA or developer we all know the importance of memory and we are aware of what happens when Sql Server or any application run out of sufficient memory. So it is important to keep track of memory utilization and early detection of memory pressure. If we have sufficient information we can take preventive measures like adding more memory or tweaking the memory management. This is all together is a different topic so let’s focus on job in hand.

Let’s assume a scenario - we are a part of team who takes care of an application. We know that it is very important that our application should work flawless and fast even in high time. Let’s assume that during peak hours, your application is working terribly slow or you are getting system time out error. What would be your first guess? Most probably you will think something is wrong from backend side, why database is taking this much time or what is the reason for failure. There could be many reasons like deadlock, too much locking, lack of memory, Disk IO etc. If you notice in above mentioned issues there is one thing is common that is memory. So I feel it would be good idea to take a quick look at the memory status. For that you can query DMV sys.dm_os_process_memory or use a modified script –

SELECT 

(physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB, 

(locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB, 

(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB, 

process_physical_memory_low, 

process_virtual_memory_low 

FROM sys.dm_os_process_memory;

Output would be something like this -

Memory_usedby_Sqlserver_MB – It’s memory used by your Sql Server and in this case our database size is 4606 MB or 4.6GB. It will be different for your application. If you want a perfect definition here it is , according to Microsoft ‘physical_memory_in_use_kb Indicates the process working set in KB, as reported by operating system, as well as tracked allocations by using large page APIs’  

locked_page_allocations_kb - Specifies memory pages locked in memory.

total_virtual_address_space_kb - Indicates the total size of the user mode part of the virtual address space.

process_physical_memory_low - Indicates that the process is responding to low physical memory notification.

process_virtual_memory_low - Indicates that low virtual memory condition has been detected.                                            

 If you run for all columns you get the result something like this –

select * from sys.dm_os_process_memory;

Or

select

physical_memory_in_use_kb,

large_page_allocations_kb,

locked_page_allocations_kb,

total_virtual_address_space_kb,

virtual_address_space_reserved_kb,

virtual_address_space_committed_kb,

virtual_address_space_available_kb,

page_fault_count,

memory_utilization_percentage,

available_commit_limit_kb,

process_physical_memory_low,

process_virtual_memory_low

from sys.dm_os_process_memory; 

This basic query gives an overview of the memory status. This information can be a good starting point to start the troubleshooting.

Next part we will be exploring DBCC MemoryStatus command , So stay tuned and Happy Scripting.