AVAILABLE MEMORY % BELOW THRESHOLD

MS-SQLPerformanceQueriesSQL

Symptoms:

Slow SQL responses coming from fall in available RAM.

Available memory (percentage of total) is below the threshold.

Impact: High

Expected behavior :

This indicates that available memory is running below the critical level, which can be an indicator of impending crash or freeze.
As well as measuring and alerting to the absolute total of available memory (in Gbyte) here this alert compares available to total, which can be a more meaningful indicator of stress.

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.

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.

Queries code written ineffectively – 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. If doing operations on large numbers of rows, consider batching them up into more manageable chunks and giving the log time to recover.

Background

This measure corresponds to a shortage of memory to satisfy all program needs. 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.

Share with friends:

You may also like this:

RECENT POSTS

Menu