It is really a good experience to figure out memory change which could be possibly memory pressure SQL Server is facing. I will demonstrate how to figure out memory pressure in SQL Server using Windows Perfmon.

Before I start demonstration on figuring out memory pressure, DBA need to have deep understanding of memory Configuration with SQL Server with Standalone instance and Multiple Instance on the same Machine. Don’t ignore memory configuration for the Operating System (OS).

System can face serious problem if enough memory is not given to the Operating System.

Example: System Shutdown due to not enough memory (unexpected shutdown) which lead to Production hamper.

 

Perfmon Counter to Capture Memory Utilization

SQL Server: Memory Manager: Total Server Memory

SQL Server: Memory Manager: Target Server Memory

Process: Working Set à Instance àSqlservr

Memory: System Cache Resident Bytes

Memory: % Committed Bytes in Use

Memory: Available Mbytes

 

Process: Working Set. This is the current size of the memory area that the process is utilizing for code, threads, and data. The size of the working set will grow and shrink as the VMM can permit. When memory is becoming scarce the working sets of the applications will be trimmed. When memory is plentiful the working sets are allowed to grow. Larger working sets mean more code and data in memory making the overall performance of the applications increase. However, a large working set that does not shrink appropriately is usually an indication of a memory leak.

If you see the values of working set being trimmed it indicates a Memory Pressure or Leak. Look into SQL Server Error Log, SQL server Agent Error Log, In case u did not find anything, check for Jobs if they fail withl Memory Issues (Out of Memory).

 

Externally:

Physical memory (RAM) running low (Memory Trimmed). This causes the system to trim working sets of currently running processes, which can result in overall slowdown.

 

SQL Server detects this condition and, depending on the configuration, it might reduce the commit target of the buffer pool and start clearing internal caches.

Internally:

SQL Server detects high memory consumption internally, causing redistribution of memory between internal components.

 

Internal memory pressure may be a result of:

• Responding to the external memory pressure (SQL Server sets lower memory usage caps).
  
• Changed memory settings (for example, max server memory).
  
• Changes in memory distribution of internal components.