Host: Process memory

Symptoms : 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.

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 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.