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 gigabyte total memory:

CRITICAL :

20000 megabyte

MEDIUM :

10000 megabyte

LOW :

5000 megabyte

Time span :

more than 5 minutes

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.

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 system to trim working sets of currently running processes which may result in 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.

SQL OS implements a framework to enable memory pressure handling. In the heart of the framework lies Resource Monitor task (RM), which monitors 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: