How do I troubleshoot low freeable memory in my RDS for SQL Server instance?

Resolution

Monitor memory usage on your instance

To monitor your memory usage, you can use Amazon CloudWatch metrics or Enhanced Monitoring.

Use CloudWatch metrics

To identity low memory, use the Amazon Aurora and RDS console to monitor the FreeableMemory CloudWatch metric.

To identity an increase in workload when available memory is low, monitor the following metrics:

• DatabaseConnections
• CPUUtilization
• ReadIOPS
• ReadThroughput
• WriteIOPS
• WriteThroughput

For information about the preceding metrics, see Amazon CloudWatch instance-level metrics for Amazon RDS.

Turn on Enhanced Monitoring

To monitor operating system (OS) metrics for Microsoft SQL Server, use Enhanced Monitoring. When you turn on Enhanced Monitoring, you can set the metric collection interval to 1, 5, 10, 15, 30, or 60 seconds. The default is a 60-second granularity, but it's a best practice to set the granularity to 1 or 5 seconds.

You can also use Enhanced Monitoring to create CloudWatch alarms to monitor your Amazon RDS for SQL Server DB instance's memory usage.

Limit the memory that the Amazon RDS instance uses

Determine the max_server_memory value for your instance, and then set the max_server_memory to a value that doesn't cause system-wide memory pressure.

Determine the max_server_memory value

Use the following calculation to determine the max_server_memory value for your instance:

max_server_memory = total_RAM - (1 GB for the OS + memory_basis_amount_of_RAM_on_the_server)

total_RAM equals the total instance type memory.

memory_basis_amount_of_RAM_on_the_server is determined in the following ways:

• If RAM on the server is between 4 GB and 16 GB, then keep 1 GB per 4 GB of RAM. For example, keep 4 GB for a server with 16 GB of RAM.

• If RAM on the server is over 16 GB, then keep 1 GB per 4 GB of RAM up to 16 GB. Keep 1 GB per 8 GB of RAM that's larger than 16 GB.

For example, if a server has 64 GB of RAM, then the calculation for the max_server_memory is the following:

• 1 GB for the OS
• Up to 16 GB RAM: 16/4 = 4 GB
• Remaining RAM larger than 16 GB: (64-16)/8 = 6 GB
• (1 GB for the OS + memory_basis_amount_of_RAM_on_the_server) = 1 + 4 + 6 = 11 GB
• max_server_memory: 64 - 11 = 53 GB

Set the max_server_memory

To change the max_server_memory, use a custom parameter group to configure the value. Provide the value for max_server_memory in max server memory (MB). Because max_server_memory is a dynamic parameter, you don't need to reboot for the changes to take effect.

Note: After you configure max_server_memory, you must continually monitor FreeableMemory to determine whether to increase or decrease the allocated memory.

Check whether you use SSIS, SSAS, or SSRS options on the DB instance

Check your RDS option group to determine whether you use SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), or SQL Server Reporting Services (SSRS) options on the DB instance. The memory that the options use exists outside the max_server_memory setting and increase the memory footprint on the instance. If you don't use these options, then modify the option group to remove them.

Note: If you're using SSIS, SSAS, or SSRS on the instance, then you must adjust max_server_memory to accommodate the options.

For example, if you're using SSRS, then set the SSRS Max memory value to 10% of the total memory of the DB instance. For example, 10% on an instance that has 64 GiB of memory is approximately 6.4 GiB. So, adjust the max_server_memory value to approximately 46 GiB (64 - 11 - 6.4 = 46 GiB).

Check database connections

Each database connection that you make to the instance requires some memory allocation outside the buffer pool for worker threads. So, a spike in DatabaseConnections can cause a drop in freeable memory.

Monitor the DB instance with Performance Insights

You can use Performance Insights to analyze your database performance and identify potential bottlenecks that slow down the instance. Use the Performance Insights dashboard to monitor database load, waits, queries, hosts, and users.

Perform regular maintenance on the DB instance

Perform regular index maintenance and keep statistics updated. Highly fragmented indexes can increase I/O activity and cause more memory usage. Also, outdated statistics can result in an inaccurate cardinality estimation and cause the database to select a suboptimal query plan. For more information, see Optimize index maintenance to improve query performance and reduce resource consumption on the Microsoft website. Also, see UPDATE STATISTICS (Transact-SQL) on the Microsoft website. 

Note: It's a best practice to perform index and statistics maintenance during non-peak time or during a maintenance window.

Monitor the PLE and the BCHR

To identify memory pressure, monitor Page Life Expectancy (PLE) and Buffer Cache Hit Ratio (BCHR). For optimal performance, check that the values for PLE and BCHR are as high as possible. If the values for PLE and BCHR are consistently low over a period of time, then tune the queries that access data or increase the instance class to provide more memory.

To use Performance Insights to monitor the metrics, complete the following steps:

1. Open the Aurora and RDS console.
2. In the navigation pane, choose Performance Insights.
3. Use the Filter a DB instance search field to select the instance that you want to monitor.
4. Set the time range that you want to review the metrics for. 
5. In the Metrics dashboard, select Custom dashboard - SQL Server database, and then choose Add first widget.
6. In the Filter metrics by name, category or ID search field, search for Page Life Expectancy, and then select it.
7. Choose Add Widget.
8. Repeat step 6 and 7 to select Buffer Cache Hit Ratio for the custom dashboard.

For more information about PLE and BCHR, see Buffer manager performance objects on the Microsoft website.

When there's memory pressure on the instance and low PLE and BCHR, PAGEIOLATCH wait increases. Microsoft SQL Server is waiting for a page to be loaded from the disk into memory. You might also see the RESOURCE_SEMAPHORE wait when a query's memory request fails because of low memory. CPU usage then increases because data pages aren't cached long enough in memory. When this occurs, Microsoft SQL Server must repeatedly access data on the disk.

Select the correct instance size for your workload

The amount of memory on an instance depends on the instance type. Make sure that you select an instance class with sufficient resources so that the DB instance has enough resources for the workload. When an instance has fewer resources, you experience performance issues. An oversized instance wastes resources.

For example, the db.r5.8xlarge instance class provides 32 vCPU and 256 GiB of memory. When you provision an Amazon RDS instance with db.r5.8xlarge, the following resources share all 256 GiBs of memory on the instance class:

• The operating system
• Amazon RDS processes
• The database engine
• Worker threads
• Business Intelligence suite applications, such as SSIS, SSAS, and SSRS.

For more information about how Microsoft SQL Server uses memory, see the Memory management architecture guide on the Microsoft website.

Baseline your resource usage

To baseline resource usage on the instance, monitor metrics such as FreeableMemory, Page Life Expectancy, and Buffer Cache Hit Ratio. If data volume significantly increases on the instance, then increase the max_server_memory value. Make sure to increase the max_server_memory value in proportion to the data volume change to maintain the same level of performance on the instance.

Note: To identify components that are using memory inside SQL Server, you can use SQL Server tools, such as Reports and DMVs.

To use SQL Server Management Studio (SSMS) to review SQL Server memory usage, complete the following steps:

1. Open SSMS, and then connect to your Amazon RDS for SQL Server instance.
2. In Object Explorer, right-click the Amazon RDS instance endpoint name.
3. Choose Reports, Standard Reports, Memory Consumption.

To download SSMS, see Download SQL Server Management Studio (SSMS) on the Microsoft website. 

You can also query sys.dm_os_memory_clerks to identify the components that use the maximum memory inside SQL Server. For more information, see sys.dm_os_memory_clerks (Transact-SQL) and Internal memory usage by SQL Server engine on the Microsoft website.