SAP HANA: Detailed Memory Consumption Analysis Walkthrough (RCA Framework)

1. Top-Down RCA Diagnostic Methodology #

When SAP HANA experiences memory spikes or Out of Memory (OOM) incidents, a structured Basis root cause analysis (RCA) addresses three core questions in sequence:

┌─────────────────────────┐       ┌──────────────────────────────────┐       ┌───────────────────────────────┐
│     STEP 1: WHEN?       │ ────> │         STEP 2: WHAT?            │ ────> │      STEP 3: WHO / WHICH JOB? │
│  (Identify Peak Time)   │       │(Top Memory Allocator / Top Table)│       │ (Pinpoint Thread, User, TCode)│
└─────────────────────────┘       └──────────────────────────────────┘       └───────────────────────────────┘

[!NOTE]
Memory history statistics in SAP HANA are maintained by the Statistics Server (default retention: 14 days). Conduct root cause analysis promptly before historical records are purged.

Tooling: SAP Note 1969700 SQL Statement Collection #

Download SQLStatements.zip attached to SAP Note 1969700. Execute the scripts via HANA Studio, SAP HANA Cockpit (Database Explorer), or DBA Cockpit (DB02).

2. Step 1: Identifying the Memory Peak Time #

Script to Run #

HANA_Resources_CPUAndMemory_History (from SAP Note 1969700)

Parameter Configuration (Modification Section) #

/* Modification section */
BEGIN_TIME        = '2024/01/01 00:00:00'   /* YYYY/MM/DD HH24:MI:SS */
END_TIME          = '2024/01/01 23:59:59'
AGGREGATION_TYPE  = 'PEAK'                  /* Query peak values */
AGGREGATION_BY    = 'TIME'
TIME_AGGREGATE_BY = '10_MIN'                /* Group samples in 10-minute intervals */

(Direct query fallback via Statistics Server if the script collection is unavailable):

SELECT TOP 50 
    HOST, 
    SERVER_TIMESTAMP, 
    ROUND(INSTANCE_TOTAL_MEMORY_USED_SIZE/1024/1024/1024, 2) AS "Used_GB",
    ROUND(ALLOCATION_LIMIT/1024/1024/1024, 2) AS "Limit_GB"
FROM _SYS_STATISTICS.HOST_RESOURCE_UTILIZATION_STATISTICS 
WHERE SERVER_TIMESTAMP BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59'
ORDER BY INSTANCE_TOTAL_MEMORY_USED_SIZE DESC;

• Objective: Identify the timeframe where memory usage exceeded safe operational thresholds (> 85–90% Allocation Limit).
• Target Timeframe Identified: e.g., 2024/01/01 11:00:00 to 2024/01/01 11:10:00 → Target Timeframe for Step 2 and Step 3.

3. Step 2: Isolating Top Memory Consumers & Heap Allocators #

Script to Run #

HANA_Memory_TopConsumers (or HANA_Memory_TopConsumers_History)

Parameter Configuration #

/* Modification section */
BEGIN_TIME = '2024/01/01 11:00:00'
END_TIME   = '2024/01/01 11:10:00'

Allocator Meaning & Action Reference Table #

[!TIP]
Refer to Question 13 in SAP Note 1999997 for comprehensive diagnostic steps covering all known Heap Allocators.

4. Step 3: Pinpointing Threads, Users & Batch Jobs #

Script to Run #

HANA_Threads_ThreadSamples_FilterAndAggregation (reads from M_SERVICE_THREAD_SAMPLES)

Parameter Configuration #

/* Modification section */
BEGIN_TIME = '2024/01/01 11:00:00'
END_TIME   = '2024/01/01 11:10:00'

Key Output Columns to Inspect #

1. THREAD_TYPE: SqlExecutor (originating from user or application queries) or JobWorker (internal HANA parallel execution thread).
2. THREAD_METHOD:
   • ExecutePrepared: Executing prepared SQL statements.
   • merging: Performing a Delta Merge.
   • indexing: Building or updating indexes.
   • MakeHeapJob, ZipResultJob, ParallelForJob: Parallel data processing operations.
3. APP_USER: Dialog user triggering the transaction from SAP GUI or Fiori.
4. APP_SOURCE: ABAP program name or transaction code (e.g., ZFIN_REPORT, FAGLL03).
5. DURATION_MS: Continuous execution duration of the thread.

5. Real-World Case Study #

• Symptom: HANA RAM rapidly escalated to 98% of Allocation Limit during business hours.
• Root Cause Analysis:
  1. Step 1: Peak usage narrowed to 11:00 - 11:10.
  2. Step 2: Allocator Pool/itab surged by several hundred gigabytes.
  3. Step 3: Dozens of JobWorker threads running MakeHeapJob and ZipResultJob concurrently. Extracting the associated SQL statement revealed:

     CALL CHECK_TABLE_CONSISTENCY('CHECK', NULL, NULL);
     

• Conclusion: An administrator triggered a comprehensive table consistency check across the entire database during peak production hours.

6. Best Practices & Emergency Mitigation #

1. Enforce Statement Memory Limit #

Configure within global.ini to prevent unoptimized queries from exhausting all host RAM:

[memorymanager]
statement_memory_limit = 50GB
statement_memory_limit_threshold = 80

2. Operational Rules for CHECK_TABLE_CONSISTENCY #

• Never execute full-database consistency checks (NULL, NULL) on production systems during business hours.
• Always scope the check to a specific schema and table:

  CALL CHECK_TABLE_CONSISTENCY('CHECK', 'SAPHANADB', 'ACDOCA');
  

3. Emergency Memory Relief (> 95% RAM Usage) #

Immediately unload large, non-critical tables from memory (data remains completely safe and persistent on disk):

ALTER TABLE "SAPHANADB"."BSIS" UNLOAD;

7. Reference SAP Notes #

• SAP Note 1969700: SQL Statement Collection for SAP HANA
• SAP Note 1999997: FAQ: SAP HANA Memory (Detailed Allocator Troubleshooting)
• SAP Note 2114710: Check Table Consistency in SAP HANA
• SAP Note 2301382: High "Used memory" in Page/DefaultLPA after upgrade
• SAP Note 3011480: How-To: Troubleshooting SAP HANA Memory Consumption