SQL: INTERNAL MEMORY PRESSURE

Symptoms: Internal Memory Pressure is high.

Impact: Medium

Memory is the most important resource with regard to SQL Server performance. This metric represents the degree to which the SQL server is being starved of memory and unable to perform optimally.

It is an indication of degradation in server performance.

Expected behavior :

There is no standard metric for memory pressure. You should set your own thresholds relative to actual allocated memory for SQL and after reviewing past behavior.

Threshold settings:

Metrics are set in megabytes over a specified time span.  Settings should be calculated relative to the total memory available.  The following example displays the suggested settings for a server with 80 gigabytes total memory:

Possible causes

Poor coding   Priority: Medium
Best practices for coding should be strictly enforced, to ensure that all queries optimally consume and then release resources.
Recommended action :
Using AimBetter monitor, identify any queries that have remained open for an inordinate length of time. Recover the query plan, and see whether any improvements are available.

Index missing or corrupted  Priority: Medium
Missing or corrupted indexes can lead to queries working inefficiently and consuming resources.
Recommended action :
See our recommendations regarding memory optimization here.

Background

The way SQL Server uses memory rests on the configuration of hardware and of  SQL Server, CPU and I/O. Memory pressure may indicate a very wide range of different (and combined) problems such as:

• missing indexes
• badly designed tables
• an actual shortage of memory on the server for the designated workload
• other applications running on the system
• unexpected numbers of users logging onto the server

and many more. At the same time, SQL Server that has been running smoothly on an over-allocation of memory may have been able to hide lots of these and other issues, so changes in load alone may be the trigger and should be an important factor to rule out before undertaking further investment on additional RAM.

Important to understand that not only memory is affected! Memory pressure situations might trigger:

• extra I/Os
• higher recompile ratio
• longer queries (if they have to wait for grants)
• extra CPU activity
• other unnecessary activities

Causes of pressure can be broken down in two broad categories – physical and virtual:

External Memory Pressure – Physical

Physical memory (RAM) running low. This causes the system to trim working sets of currently running processes which may result in an overall slowdown. SQL may reduce the commit target of the buffer pool and start clearing internal caches often.

External Memory Pressure – Virtual

Running low on space in the system page file(s). This may cause the system to fail memory allocations, as it is unable to page out currently allocated memory. This condition may result in the whole system responding very slowly or even bring it to a halt.

Internal Memory Pressure – Physical

SQL is responding to external memory pressure (SQL Server sets lower memory usage caps).
Check if someone changed memory settings (e.g. ‘max server memory’).
Changes in memory distribution of internal components.

Internal Memory Pressure – Virtual
Running low on Virtual Address Space (VAS) due to fragmentation or running out of VAS (usually a process terminates). SQL Server detects this condition and may:

• release reserved regions of VAS
• reduce buffer pool / memmanager commit target
• start shrinking caches.

SQL OS implements a framework to enable memory pressure handling. In the heart of the framework lies the Resource Monitor task (RM), which monitors the state of the external and internal memory indicators. Once one of them changes, RM calculates the corresponding notification and broadcasts it.

Most important memory measurements deal with SQL Server’s buffer pool, an internal cache of database pages, SQL Server’s memory and the system’s memory. These measurements are:

1. Page Life Expectancy
2. Buffer Cache hit ratio
3. Page reads / sec
4. Page writes / sec
5. Lazy writes/sec
6. Memory Grants Pending
7. Total Server Memory
8. Target Server Memory
9. Available Megabytes
10. Pages / sec
11. Paging File % Usage