# SAP HANA Performance Health Checklist: Memory, CPU, Locks and Merges > Source: https://netray.co/tools/sap-hana-performance-health-checklist > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer A 36-point SAP HANA 2.0 health check where every item names the monitoring view, parameter or SAP Note to look at. ## Category sap ## Erp SAP HANA ## Audience - SAP Basis administrator - SAP HANA DBA - SAP technical architect ## Tool **Kind:** checklist **Groups:** #### 1. Memory **Items:** **Text:** Compare TOTAL_MEMORY_USED_SIZE with EFFECTIVE_ALLOCATION_LIMIT per service in M_SERVICE_MEMORY; sustained use above roughly 80-90% of the limit needs action **Critical:** true Check the global allocation limit (global.ini [memorymanager] global_allocation_limit) against physical RAM and other tenants on the host List top heap allocators in M_HEAP_MEMORY ordered by EXCLUSIVE_SIZE_IN_USE (Pool/... categories) to see what is really consuming memory **Text:** Look for column store unloads in M_CS_UNLOADS with REASON = 'LOW MEMORY'; repeated unloads of large tables mean memory pressure **Critical:** true **Text:** Check the trace directory for OOM dumps (*.rtedump.*.oom.trc) and read the top allocators and the statement that triggered them **Critical:** true Set statement_memory_limit (global.ini [memorymanager]) so one runaway statement cannot take the whole system Review the largest column tables in M_CS_TABLES (MEMORY_SIZE_IN_TOTAL) for archiving, data aging or partitioning candidates #### 2. CPU and threads **Items:** **Text:** Query M_SERVICE_THREADS for active threads with a long DURATION, grouped by THREAD_TYPE and STATEMENT_HASH **Critical:** true Use M_SERVICE_THREAD_SAMPLES (or HOST_SERVICE_THREAD_SAMPLES for history) to see what threads were doing during the slow period Check CPU and memory per host over time in M_HOST_RESOURCE_UTILIZATION (TOTAL_CPU_USER_TIME, TOTAL_CPU_SYSTEM_TIME, USED_PHYSICAL_MEMORY) Review indexserver.ini [execution] max_concurrency and max_concurrency_hint against the logical CPU count; leave defaults unless SAP Note 2600030 says otherwise Check indexserver.ini [session] default_statement_concurrency_limit if single statements flood all cores Confirm no OS level CPU steal or power saving mode on virtualised hosts (hypervisor and BIOS settings per SAP guidance) #### 3. SQL and plan cache **Items:** **Text:** Enable the expensive statements trace: global.ini [expensive_statement] enable = true with a threshold_duration in microseconds (for example 1000000 for 1 s) **Critical:** true Read M_EXPENSIVE_STATEMENTS for the top statements by DURATION_MICROSEC and MEMORY_SIZE **Text:** Rank M_SQL_PLAN_CACHE by TOTAL_EXECUTION_TIME and by AVG_EXECUTION_TIME to separate frequent cheap statements from rare heavy ones **Critical:** true Check plan cache hit ratio and evictions in M_SQL_PLAN_CACHE_OVERVIEW; high eviction counts point to an undersized indexserver.ini [sql] plan_cache_size Capture the explain plan or a PlanViz trace for the top statement before changing indexes or hints Follow SAP Note 2000002 (FAQ: SAP HANA SQL Optimization) for statement tuning steps #### 4. Locks and transactions **Items:** **Text:** Check M_BLOCKED_TRANSACTIONS for current lock waits and the blocking transaction (LOCK_OWNER_TRANSACTION_ID) **Critical:** true Find long open transactions in M_TRANSACTIONS by START_TIME, including idle connections that hold a transaction open **Text:** Check MVCC version counts in M_MVCC_TABLES (NAME = 'NUM_VERSIONS'); a high, growing count usually means an old open snapshot blocks garbage collection **Critical:** true Map the blocker to the application user via M_CONNECTIONS before cancelling a session Review record lock waits in M_RECORD_LOCKS and table locks in M_TABLE_LOCKS for recurring hot spots #### 5. Persistence, merges and disk **Items:** **Text:** Check savepoint duration and CRITICAL_PHASE_DURATION in M_SAVEPOINTS; long critical phases block DML **Critical:** true List tables with a large delta store: M_CS_TABLES ordered by MEMORY_SIZE_IN_DELTA **Text:** Look for failed merges in M_DELTA_MERGE_STATISTICS (SUCCESS = 'FALSE' and LAST_ERROR) **Critical:** true Check data and log volume I/O latency in M_VOLUME_IO_TOTAL_STATISTICS; slow log writes slow every commit **Text:** Check log segment states in M_LOG_SEGMENTS and confirm log backups run, so the log volume does not fill **Critical:** true Confirm data and log volume file systems have free space headroom #### 6. Housekeeping and configuration **Items:** **Text:** Run HANA_Configuration_MiniChecks from the SQL statement collection in SAP Note 1969700 and interpret results with SAP Note 1999993 **Critical:** true Review open alerts in _SYS_STATISTICS.STATISTICS_CURRENT_ALERTS or in SAP HANA cockpit Confirm the embedded statistics service is active (nameserver.ini [statisticsserver] active = true) so history views are filled Check parameters against SAP Note 2600030 (Parameter Recommendations in SAP HANA Environments) Clean up old trace files (ALTER SYSTEM REMOVE TRACES or the cockpit) and switch off temporary traces after analysis Keep the HANA revision current against the SAP HANA revision strategy and known issue notes **Scoring Note:** Items marked critical are the first things to check during an incident. All view and column names are for SAP HANA 2.0; verify column names against the System Views Reference for your SPS. ## Content **Intro:** This checklist walks an SAP HANA 2.0 system through memory, CPU, SQL, locks, persistence and housekeeping, naming the exact monitoring view, parameter or SAP Note for each check. Use it for a monthly health review or as the first pass in a performance incident. **Sections:** #### How to use it **Body:** Work top to bottom during a health review, or start with the critical items during an incident. Most checks are a single SELECT on a monitoring view in the SYS schema, run from HANA cockpit, the SQL console in HANA Studio or hdbsql as a user with the MONITORING role or CATALOG READ. For history beyond what M_ views keep, use the matching _SYS_STATISTICS.HOST_* tables. Tick each item, note the finding, and export to Excel to keep a dated record you can compare month to month. **Bullets:** - Run checks during the slow period, not afterwards; most M_ views show current state. - Save the output of the mini checks with the date and revision. - Change one parameter at a time and record it. #### Where the method comes from **Body:** The structure follows the SAP HANA Troubleshooting and Performance Analysis Guide and the FAQ notes SAP support uses: SAP Note 1999997 (FAQ: SAP HANA Memory), SAP Note 2114710 (FAQ: SAP HANA Threads and Thread Samples), SAP Note 2000002 (SQL optimization) and SAP Note 1969700, the SQL statement collection that contains HANA_Configuration_MiniChecks and hundreds of analysis scripts. The mini checks compare dozens of key figures with expected values and mark potentially critical results with an X in the C column; SAP Note 1999993 explains each check ID and what to do next. #### Worked example: slow system after month end **Body:** Users report slow reports every first working day. M_SERVICE_MEMORY shows the indexserver at 93% of its effective allocation limit and M_CS_UNLOADS lists repeated LOW MEMORY unloads of a large ACDOCA partition. M_HEAP_MEMORY shows Pool/JoinEvaluator high during the slow window, and M_EXPENSIVE_STATEMENTS points to one analytical query with a multi gigabyte MEMORY_SIZE. The fix sequence is to set statement_memory_limit so that query fails instead of evicting everyone else, tune the query, then review whether the allocation limit or data volume growth needs a sizing change. #### Common mistakes **Body:** The most common mistake is reading host memory from the OS and concluding HANA is out of memory; HANA pre-allocates and the right numbers are used memory versus the allocation limit. Others: changing max_concurrency without measuring, leaving the expensive statements trace at a very low threshold so it fills the trace directory, cancelling a blocked session instead of the blocker, and ignoring failed delta merges until the delta store is huge. Long running idle transactions from batch tools are a frequent hidden cause of MVCC version build up. **Cta:** If you want your Basis team to ask these HANA health questions in plain English and get the query and the answer on your own servers, we can set that up. **Faq:** #### What are SAP HANA mini checks? **Answer:** Mini checks are the SQL script HANA_Configuration_MiniChecks from the statement collection attached to SAP Note 1969700. It evaluates many configuration and runtime key figures in one run and marks potential problems. SAP Note 1999993 describes how to interpret each check ID. Use the script version that matches your HANA revision. #### How do I find what is using memory in SAP HANA? **Answer:** Start with M_SERVICE_MEMORY for used memory versus the effective allocation limit, then M_HEAP_MEMORY ordered by EXCLUSIVE_SIZE_IN_USE to see the heap allocators, and M_CS_TABLES for the largest column tables. SAP Note 1999997, the memory FAQ, lists the standard scripts for each step. #### Where is the SAP HANA threads FAQ? **Answer:** SAP Note 2114710 (FAQ: SAP HANA Threads and Thread Samples) explains thread types, states and how to use M_SERVICE_THREADS and the thread sample views. It is the reference when many threads are waiting or a single statement uses all cores. #### Why are delta merges failing? **Answer:** Check M_DELTA_MERGE_STATISTICS for LAST_ERROR. Common causes are memory shortage during the merge, a table locked by a long open transaction, or auto merge disabled for the table. Large deltas slow reads, so fix failures before they accumulate. ## Search terms sap hana performance issues, sap hana mini checks, hana mini checks sap note, sap hana memory issues, sap hana memory optimization, faq sap hana threads, sap hana health check checklist, m_expensive_statements --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json