Symptoms: Free memory is low – all host functions are running slower than normal.
Impact: High
The source may be either a limit in virtual or physical memory, memory pressure from other applications or inside the SQL Server.
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 while investigating the root cause, to allow the server to run smoothly.
Possible causes
Low page life expectancy Priority: Medium
See our recommendations regarding PLE here.
Recommended action :
Possible short-term action – add RAM.
Check buffer pool cache hit ratio and adjust buffer pool allocation. See our knowledge base regarding cache hits here.
Long-running transactions Priority: Medium
Properly size your log file and disk capacity to account for a worst case scenario (e.g maintenance or known large operations).
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. If doing operations on large numbers of rows, consider batching them up into more manageable chunks and giving the log time to recover.
See our knowledgebase entry regarding long queries here.
Queries code written ineffectively Priority: Medium
Recommended action :
Queries that run with a large number of iterations, or are susceptible to blocks and deadlocks, will hold onto memory and force page swapping.
SQL not releasing memory to the operating system Priority: Medium
Recommended action :
You must use the Max Server Memory configuration option to configure how much memory SQL Server can consume and it needs to allow sufficient space for the operating system’s own activities. If too much is reserved for SQL, it is not released when the SQL server is under light load, so the dynamic balance between OS and SQL does not adjust automatically.
Background
This measure corresponds to a shortage of memory to satisfy all program needs. Such situations will result in 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 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.
