This metric points to a shortage of memory to satisfy program needs resulting in poor paging file usage.
Lack of system memory can cause high non-cached disk reads and writes. When free memory runs low, the server flushes the oldest page to disk to get additional free space demanding physical I/O for reading and writing, which is a major performance concern. Adding memory to your server can help lower physical disk access. Process Memory indicates when a specific process running in the server utilizes RAM memory. This process might be an application, system program, or resource.
Find out how you can save hours of work when investigating the root cause of this issue.
Symptoms :
Free memory is low – all functions are running slower than normal. In other cases, when a specific program utilizes more RAM memory than usual, its specific function can be degraded.
Impact: Medium
Slow 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. A steady memory availability level is essential for the server’s good functionality. A minimum of available space should be 1 gigabyte or more for low total RAM Servers (up to 16 GB) and more than 15 GB available memory for high total RAM servers.
Process memory depends on the server’s activity and total memory. It also depends on other OS metrics in total. You should consider checking this issue when single process memory is more than 500 MBs, repeatedly and over time.
Possible causes of high process CPU load
1- Poor application design caused by heavy tasks or non-optimal settings. Priority: Medium
In some applications, there are tasks that are running by default on working hours, which might overload workflow. In other cases, the application settings are not optimal for needed workflow or do not fit the host server.
Problem identification:
Search for processes consuming much RAM memory and seen on the command line as part of the suspicious application that is checked currently.
- Enter the Task manager and follow up on specific processes that consume much RAM memory compared to other processes running. Follow if the processes are related routinely to the same application. Take into account that you can only see live data without historical events.
- Look for errors related to that application. Consult the application team to check if these errors might be related to non-optimal application settings.
- Consider changing application settings by consulting with the application team. For example, consider removing specific overloading tasks to non-working hours in order to reduce total RAM consumption in working time. You should track this issue over time, which might be hard to do without proper monitoring.
Recommended action :
Try making changes to current application settings while consulting with the application team. Track any change that is done.
2- The server does not fit application requirements. Priority: High
There are cases when the server does not fit the application requirements for better resources than exist in the hosting machine. This results from insufficient memory available on a system to support a particular program or workload.
Problem identification:
Search for processes causing high RAM memory and seen on the command line as part of the suspicious application that is checked currently. Look for errors.
- Enter the Task manager and follow up on specific processes that are consuming at least 500MB RAM memory. Search for the command line in order to check whether these processes are assigned to the same application repeatedly. Take into account that you can only see live data without historical events.
- Review the total RAM memory available in the machine.
- Look for errors related to the most RAM-consuming application, which might be related to non-fit hosting machines. You should do this step with the application team.
Recommended action :
Consider upgrading hardware and consulting the application team.
3- Long-running queries with open transactions Priority: Medium
Long-running queries can consume excessive system resources such as CPU and cache, which can cause other queries to slow down or fail, and may even crash the database server. In addition, it can cause further problems, including locks, data consistency issues, and rollback failures.
Problem identification:
Identify long-running queries with open transactions that hold on OS resources while focusing on cache usage and the applications that consume most RAM memory.
- Use SQL Server Management Studio to identify the long-running queries with open transactions that are consuming more memory. For Oracle, you should use Oracle Dynamic Performance Views or other tools. In both, include searching for the program which activates the queries.
- Look for queries with code written ineffectively. Consider how to improve the performance of these queries. Try to look at improving query code, reducing transaction length, or identifying inefficient operations in the query’s plan. This task might be complicated, requiring a highly skilled DBA.
- You may need to increase the amount of memory allocated to SQL Server or Oracle.
Recommended action :
Optimize the application code – avoid starting a transaction in your application that is having long conversations with the 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 specifying the log time to recover.
Improve the query execution plan, increase the allocated max memory, or consider upgrading your hardware to increase the amount of RAM available to the server.
