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.
See our recommendations regarding PLE here.
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.
As free memory is consumed, pages residing in the memory are swapped out to disk to be replaced by needed data.
Correct the buffer pool allocation, or add memory. See our explanation of page life expectancy here.
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.
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 server is set to wrong maximum level.
Optimize memory allocation.
Missing indexes will cause extensive data searching from disk, resulting in page swapping.
See our explanation for missing/corrupt indexes here.
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.