Symptoms: Free memory is below threshold – all functions are running slower than normal.
Impact: Medium
Memory is the most important resource with regard to the SQL Server’s performance. This metric represents the degree to which the whole environment is being starved of memory and unable to perform optimally.
Default settings are:
| CRITICAL : | 10% | |
| MEDIUM : | ||
| LOW : | ||
| Timespan : | more than 5 minutes |
Expected behavior: There is no standard metric for the availability of free memory. Steady memory availability level is essential for the server’s good functionality. Consider adding memory as well as investigating the root cause, to allow the server to run smoothly.
Possible causes
Low page life expectancy Priority: Medium
Recommended action :
See our recommendations regarding PLE here.
Long-running queries Priority: Medium
Recommended action :
Optimize the application code – avoid starting a transaction in your application that is having long conversations with SQL Server, or risks leaving one open too long.
See our recommendations regarding long queries here.
Incorrect buffer pool allocation Priority: Medium
As free memory is consumed, pages residing in the memory are swapped out to disk to be replaced by needed data.
Recommended action :
Correct the buffer pool allocation, or add memory. See our explanation of page life expectancy here and buffer cache hit ratio here.
Queries code written ineffectively Priority: Medium
Queries that run with a large number of iterations, or are susceptible to blocks and deadlocks, will hold onto memory and force page swapping. See our explanation of coding problems causing deadlocks here.
Recommended action :
Optimize code. Possible actions :
- Redesign program to maximize use of indexed data.
- Redesign table structures to match the requirements of the programs by building indexes.
- Make use of temporary tables.
SQL not releasing memory to the operating system Priority : Medium
SQL server is set to wrong maximum level.
Recommended action :
Optimize memory allocation.
Missing or corrupt indexes Priority : Medium
Missing indexes will cause extensive data searching from disk, resulting in page swapping.
Recommended action :
See our explanation for missing/corrupt indexes here.
Background
This measure corresponds to a shortage of memory to satisfy all program needs. Such situations will result in poor paging file usage. That means that operating system needs to flush the buffer pool in order to store and retrieve data from the hard disk. Such situations will result high Paging File usage. When free memory runs low, the server flushes the oldest page to disk when it requires new space. Thus sustained physical I/O both for reading and writing, which is a major performance concern.
The more memory your server has the more cached disk reads and writes it can perform. Lack of system memory can cause high non-cached disk reads and writes. Adding memory to your server can help lower physical disk access.
