RDS for SQL Server memory pressure: Diagnosis and optimization

Procedure

1. Go to the RDS instance list, select a region at the top, and then click the ID of the target instance.

2. In the left-side navigation pane, click Monitoring and Alerts.

3. View the memory-related metrics mem_usage, Page_life_expectancy, and bufferpool_hit_ratio.

Initial assessment of memory pressure

For an RDS for SQL Server instance, a mem_usage (memory usage) value of 90% or higher is typically normal. SQL Server attempts to use as much available memory as possible to improve performance, for example, for data caching. This metric also includes memory used by the SQL Server process, the operating system, and management services.

However, if an instance enables high-memory-overhead features such as Linked Server, CLR, In-Memory OLTP, or a large number of XEvents or Traces, its actual memory consumption can significantly exceed the configured value of max server memory. This increases overall memory pressure and the risk of performance fluctuations. In this case, you should lower the max server memory value to reserve the necessary memory overhead and ensure system stability.

The recommended upper limits for the mem_usage metric are as follows:

• 97% for instances with 512 GB of memory

• 96% for instances with 256 GB of memory

• 95% for instances with 192 GB or less of memory

Procedure

1. Go to the RDS instance list, select a region at the top, and then click the ID of the target instance.

2. In the left-side navigation pane, choose Autonomy Services > Performance Optimization to go to the Performance Insight tab.

3. In the upper-right corner of the page, click Custom Metrics. In the full list of metrics, select the memory-related performance metrics, and then click Confirm. Common metric groups for memory analysis include Memory Usage Classification and AdvancedMemUsage (Advanced Memory Usage Classification).

Memory Usage Classification metrics

The metrics in the Memory Usage Classification group generally correspond to the performance counters of the SQL Server Memory Manager object. Typically:

• Total_Server_Memory_Kb: Shows if total memory usage is approaching its limit.

• Database_Cache_Memory_Kb and Stolen_Server_Memory_Kb: Show the proportion of memory allocated to data caching versus other components.

• Free_Memory_Kb: Monitors short-term free memory headroom.

Analyze it in the context of performance trends and the overall workload.

AdvancedMemUsage metrics (stolen memory breakdown)

The AdvancedMemUsage metrics help pinpoint the source of stolen memory. They break down stolen memory into categories that correspond to internal SQL Server components, allowing you to analyze usage in detail.

Analyzing the AdvancedMemUsage metrics helps you identify memory-consuming components and take appropriate action. For example:

• If metrics related to the plan cache, such as CACHESTORE_SQLCP and CACHESTORE_OBJCP, account for a high percentage of memory usage, focus on optimizing plan reuse and parameterization.

• If MEMORYCLERK_SQLCLR shows abnormal usage, inspect the CLR modules.

• If MEMORYCLERK_SOSNODE shows a long-term increasing trend, it indicates an issue with the SQL Server engine that may require operational measures such as an instance restart.

Symptoms

The following characteristics typically indicate that data pages cannot remain in memory for long and that the buffer pool is under pressure:

• I/O spikes: In Performance Insights, the Page_Reads (physical read) metric increases significantly, and disk I/O throughput increases.

• Short page life: The Page Life Expectancy (PLE) is consistently below the healthy threshold.

PLE represents the average time in seconds that a data page stays in the buffer pool. The traditional 300-second threshold is no longer suitable for large-memory instances. We recommend calculating the threshold dynamically based on the memory specification. A common formula is:

PLE = (Buffer Pool Memory(GB) / 4GB) * 300

PLE calculation examples:

• 16 GB instance (approx. 12 GB buffer pool): A reasonable PLE should be greater than 900 seconds (12 / 4 * 300).

• 128 GB instance (approx. 110 GB buffer pool): A reasonable PLE should be greater than 8,250 seconds (110 / 4 * 300).

Diagnostic process

• Confirm the correlation: Check if a drop in PLE is accompanied by a simultaneous increase in Page_Reads. If they are strongly correlated and occur during peak business hours, you can confirm that the issue is insufficient cache.

• Rule out noise: If PLE drops but I/O does not increase significantly, or if the drop occurs during a backup, index rebuild, or checkpoint, it is usually a normal, short-term fluctuation and can be safely ignored.

• Assess capacity: Compare database cache memory with the size of the active working set. If the cache is full but can only accommodate a very small portion of hot data, it indicates a hardware bottleneck.

Optimization solutions

• Scale up memory: If the working set is much larger than the instance's memory specification, upgrading the instance is the most direct way to reduce I/O latency.

• Optimize queries: Check for full table scans or large-scale aggregations. If they exist, add indexes to change scans into seeks. This prevents a single large query from evicting all hot data from the buffer pool.

• Reduce the working set:

  • Archive: Move historical cold data to archive tables.

  • Compress: Enable table or index data compression so that the same amount of memory can hold more data pages.

  • Maintain: Rebuild heavily fragmented indexes to increase page density, and drop unused indexes to reduce maintenance overhead.

Symptoms

Abnormally high stolen memory usage, which can reach engine limits and contend for buffer pool space, usually indicates poor execution plan reuse.

• Memory characteristics: stolen memory continuously grows and remains at a high level, approaching the internal quota for the plan cache. This prevents database cache memory from receiving sufficient memory, which in turn forces PLE to drop.

• Performance characteristics: CPU usage increases because the cache requires frequent clearing and recompilation of new plans. Disk I/O also increases because the data page cache space is compressed.

• Common triggers: The application sends large volumes of non-parameterized ad hoc SQL, or an ORM framework generates a massive number of single-use execution plans. As a result, the cache is filled with low-value plans.

Diagnostic process

• Confirm the correlation: Observe whether stolen memory is significantly higher than the historical baseline and shows a negative correlation with a drop in PLE.

• Proportion of single-use plans: This metric indicates the number of plans with usecounts = 1 and their memory usage. If single-use plans occupy an extremely high proportion of the plan cache (for example, more than 50%), it indicates that a large amount of memory is being wasted.

  SQL command for diagnosis

  WITH PlanStats AS
  (
      SELECT
          cp.usecounts,
          cp.size_in_bytes / 1024.0 / 1024.0 AS size_mb
      FROM sys.dm_exec_cached_plans AS cp
      WHERE cp.cacheobjtype = 'Compiled Plan'
  )
  SELECT
      total_plans           = COUNT(*),
      single_use_plans      = SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END),
      single_use_ratio_pct  = 
          CAST(
              100.0 * SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0)
              AS DECIMAL(5,2)
          ),
      total_plan_mb         = CAST(SUM(size_mb) AS DECIMAL(18,2)),
      single_use_plan_mb    = CAST(SUM(CASE WHEN usecounts = 1 THEN size_mb ELSE 0 END) AS DECIMAL(18,2)),
      single_use_mem_pct    = 
          CAST(
              100.0 * SUM(CASE WHEN usecounts = 1 THEN size_mb ELSE 0 END) 
              / NULLIF(SUM(size_mb), 0)
              AS DECIMAL(5,2)
          )
  FROM PlanStats;
  

• Identify the source: Sample the SQL text of single-use plans by using sys.dm_exec_sql_text. If you find many queries that are structurally identical and differ only in literal values, the application lacks parameterization. The following SQL statement retrieves the top 20 single-use plans that consume the most cache space:

  SQL command to view cached queries

  --View the top 20 ad-hoc queries that consume the most memory and their text
  SELECT TOP 20
      cp.usecounts AS [Execution_Count],
      cp.size_in_bytes / 1024 AS [Plan_Size_KB],
      cp.objtype AS [Object_Type],
      st.text AS [SQL_Text] 
  FROM sys.dm_exec_cached_plans AS cp
  CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
  WHERE cp.objtype = 'Adhoc' 
    AND cp.usecounts = 1 
  ORDER BY cp.size_in_bytes DESC;

Optimization solutions

• Enable the optimize for ad hoc workloads option

  • Procedure: Go to the RDS instance list, go to the instance details page, and in the left-side navigation pane, click Parameters. Search for and enable the optimize for ad hoc workloads parameter.

  • How it works: After this parameter is enabled, the server stores only a lightweight stub for the first execution of an ad hoc SQL query. The full execution plan is stored only on the second execution.

  • Effect: This significantly reduces the stolen memory consumed by single-use queries and alleviates memory pressure. This option optimizes memory usage but does not reduce CPU compilation overhead.

• Application-side optimization

  • Parameterize queries: Modify the application logic to use parameterized queries instead of string concatenation.

  • ORM configuration: Check if the ORM framework has auto-parameterization or a second-level cache enabled.

• Enable Forced Parameterization

  If you cannot modify the application code, you can enable database-level Forced Parameterization in the RDS console:

  • Go to the RDS instance list, go to the instance details page, and in the left-side navigation pane, click Database Management.

  • For the database where you want to enable Forced Parameterization, click View Details on the right.

  • In the Basic Information section, find the parameterization parameter, set it to FORCED, and click Submit.

    Note

    Verify this change in a test environment to avoid performance regressions for some complex queries due to plan changes.

Symptoms

Unlike buffer pool pressure, this type of pressure does not necessarily cause PLE to drop. When a query involves sort or hash operations, a query can exhibit two typical symptoms if it cannot obtain enough workspace memory:

• Queued waits

  • Symptom: The query does not start executing and remains in the SUSPENDED state.

  • Metric: The wait type is RESOURCE_SEMAPHORE (extremely severe, indicating that memory is exhausted and that queries are queuing) or RESOURCE_SEMAPHORE_QUERY_COMPILE, and the value of the Memory Grants Pending performance counter increases sharply.

• Spills to disk

  • Behavior: The query runs, but it is extremely slow.

  • Metrics: I/O writes to TempDB increase significantly.

  • Execution plan: A yellow warning icon appears on the sort or hash match operator with the message "Operator used tempdb to spill data..." This indicates that memory was insufficient, forcing SQL Server to write intermediate results to disk for computation.

Diagnostic process

You can analyze the SQL statements with the highest memory grant consumption by querying real-time or historical data. In the execution plan (query_plan) of the result, search for the Spill keyword or check for a warning icon on the Sort/Hash operators.

• Find the statements that are currently consuming the most memory grant.

  Analyze real-time queries

  -- View real-time memory grants
  SELECT
      mg.session_id,
      mg.request_time,
      mg.grant_time, -- If NULL, the query is waiting for memory (RESOURCE_SEMAPHORE)
      (mg.requested_memory_kb / 1024.0) AS requested_mb,
      (mg.granted_memory_kb / 1024.0) AS granted_mb,
      (mg.required_memory_kb / 1024.0) AS required_mb,
      mg.queue_id,
      mg.wait_order,
      st.text AS sql_text,
      qp.query_plan
  FROM sys.dm_exec_query_memory_grants AS mg
  CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS st
  CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
  ORDER BY mg.granted_memory_kb DESC;
  

• Analyze historical execution statistics from the cache to find the queries that have requested the most memory.

  Analyze historical queries

  SELECT TOP 20
      qs.execution_count,
      (qs.max_grant_kb / 1024.0) AS max_grant_mb, -- Max memory requested in a single execution
      (qs.total_grant_kb / qs.execution_count / 1024.0) AS avg_grant_mb,
      (qs.total_worker_time / qs.execution_count / 1000.0) AS avg_cpu_ms,
      qs.last_execution_time,
      SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
          ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
            END - qs.statement_start_offset)/2) + 1) AS query_text,
      qp.query_plan
  FROM sys.dm_exec_query_stats AS qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
  CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
  ORDER BY qs.max_grant_kb DESC;
  

Root causes and optimization solutions

• Stale statistics (most common cause)

  • Cause: Stale statistics cause the optimizer to severely underestimate the number of rows in the result set (e.g., estimating 1 row when 1 million rows will be processed). Based on this estimate, the optimizer requests a very small amount of memory (e.g., 1 MB), leading to frequent spills to disk during execution.

  • Solution: Update database statistics periodically or during off-peak hours to ensure the optimizer can accurately estimate memory requirements.

• Contention from concurrent large queries

  • Cause: Multiple resource-intensive reporting queries that require full table sorts or large-scale hash aggregates run concurrently, exhausting available memory and forcing subsequent queries into a wait queue.

  • Solution: Schedule memory-intensive reporting tasks to run serially during off-peak hours to avoid resource contention during peak times.

• Code or index design issues

  • High-memory operators: The query contains complex JOIN, GROUP BY, ORDER BY, or DISTINCT operations but lacks appropriate indexes, forcing SQL Server to build a large hash table in memory or perform intermediate sorts, causing memory demand to surge.

  • Excessive data width: Using SELECT * or selecting unnecessary long text columns can cause individual rows of data to become bloated. Because Memory grant size = Estimated row count × Average row width, the wider the columns, the more total memory is required.

  • Solution:

    • Create sort-friendly indexes: Create covering or ordered indexes to help the optimizer choose less memory-intensive physical operators.

    • Optimize on the application side: Select only necessary columns to reduce row width. Break down large, multi-join queries into simpler steps.