Symptoms: Free memory is low – all functions are running slower than normal.
Impact: Medium
Slow SQL responses will degrade the user experience, resulting in poor efficiency of your organization’s operations.
Expected behavior:
There is no standard metric for free memory. Based on the experience of our Expert Support team, a minimum of available space should be 1 gigabyte or more. As well, there is a need to ensure that this level is kept for a long period. Steady memory availability level is essential for the server’s good functionality. Consider adding memory as well as investigating the root cause, to allow server 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 the 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
The SQL Server is set to the 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 the operating system needs to flush the buffer pool in order to store and retrieve data from the hard disk.
Such situations will result in high Paging File usage. When free memory runs low, the server flushes the oldest page to disk when it requires a 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.
