ApsaraDB RDS:Diagnose and optimize memory pressure in RDS SQL Server

Check key metrics in Monitoring and Alerts

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

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

3. Review the following metrics:

Instance-specific `mem_usage` thresholds:

If high-memory-overhead features are enabled — such as Linked Server, CLR, In-Memory OLTP, or large numbers of Extended Events (XEvents) or Traces — actual memory consumption may significantly exceed the configured max server memory value. In that case, lower max server memory to reserve headroom for the OS and management services.

Calculate the healthy PLE threshold for your instance:

The traditional threshold of 300 seconds does not scale to large-memory instances. Use this formula instead:

PLE threshold = (Buffer pool memory in GB / 4) × 300

Examples:

• 16 GB instance (buffer pool approximately 12 GB): healthy PLE > 900 seconds

• 128 GB instance (buffer pool approximately 110 GB): healthy PLE > 8,250 seconds

Analyze memory composition in Performance Insight

If Monitoring and Alerts shows a potential problem, use Performance Insight to get a breakdown of what memory is being used for.

1. In the left navigation pane, choose Autonomy Service > Performance Optimization to open the Performance Insight tab.

2. Click Custom Metrics in the upper-right corner. Add metrics from the Memory Usage Categorization and AdvancedMemUsage groups.

Symptoms

• Page_Reads rises significantly in Performance Insight alongside a drop in PLE

• Disk I/O throughput grows while query data volume has not changed

• PLE stays consistently below the instance-specific threshold

Optimization options

Symptoms

• Stolen_Server_Memory_Kb grows continuously and remains elevated, close to the internal quota limit for the Plan Cache

• Database_Cache_Memory_Kb is squeezed downward as Stolen Memory grows

• CPU usage rises from frequent cache eviction and plan recompilation

• Disk I/O rises because the buffer pool is shrinking

• The application sends many structurally identical SQL statements with different literal values (for example, WHERE id = 123, WHERE id = 456)

Diagnostic steps

Step 1: Check the proportion of single-use plans.

A high percentage of plans executed only once indicates that the cache is filling with non-reusable plans.

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;

If single-use plans account for more than 50% of plan cache memory, the cache is being wasted on non-reusable plans.

Step 2: Identify the SQL statements generating single-use plans.

-- Top 20 ad hoc queries consuming the most plan cache memory
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;

If the results show many statements with the same structure but different literal values, the root cause is unparameterized queries.

Optimization options

Option 1: Enable `optimize for ad hoc workloads` (recommended)

This parameter caches only a lightweight stub on the first execution of an ad hoc query. The full plan is stored only if the query runs again. This reduces Stolen Memory from single-use plans without affecting CPU compilation overhead.

1. Go to the RDS instance list and open the instance details page.

2. In the left navigation pane, click Parameter Settings.

3. Search for optimize for ad hoc workloads and enable it.

Option 2: Parameterize queries in the application

Modify application code to use parameterized queries instead of string concatenation. If the application uses an Object-Relational Mapping (ORM) framework, check whether automatic parameterization or a second-level cache is available.

Option 3: Enable forced parameterization at the database level

If modifying the application is not feasible, enable forced parameterization on the affected database:

1. Go to the RDS instance list and open the instance details page.

2. In the left navigation pane, click Database Management.

3. Click View Details for the target database.

4. In the Basic Information section, set parameterization to FORCED and click Submit.

Symptoms

Queries that involve sorting or hashing — such as ORDER BY, GROUP BY, DISTINCT, or hash joins — request a block of memory called a memory grant. If this grant cannot be satisfied, two failure modes occur:

This pressure type does not cause PLE to drop. If PLE looks healthy but you see the above symptoms, the issue is in the query workspace, not the buffer pool.

Diagnostic steps

Find queries currently waiting for or consuming large memory grants:

-- Real-time memory grants
SELECT
    mg.session_id,
    mg.request_time,
    mg.grant_time, -- NULL means the query is waiting for a grant (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;

Find historical queries that have requested the largest grants:

SELECT TOP 20
    qs.execution_count,
    (qs.max_grant_kb / 1024.0)                             AS max_grant_mb,
    (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;

In the query_plan column, search for the Spill keyword or look for warning icons on Sort or Hash Match operators.

Root causes and fixes