SAP HANA Performance Health Checklist (Free)

0%

0 of 36 items complete

12 critical items still open - these are the highest-risk gaps.

Download checklist:

1. Memory

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 actionCRITICALCheck the global allocation limit (global.ini [memorymanager] global_allocation_limit) against physical RAM and other tenants on the hostList top heap allocators in M_HEAP_MEMORY ordered by EXCLUSIVE_SIZE_IN_USE (Pool/... categories) to see what is really consuming memoryLook for column store unloads in M_CS_UNLOADS with REASON = 'LOW MEMORY'; repeated unloads of large tables mean memory pressureCRITICALCheck the trace directory for OOM dumps (*.rtedump.*.oom.trc) and read the top allocators and the statement that triggered themCRITICALSet statement_memory_limit (global.ini [memorymanager]) so one runaway statement cannot take the whole systemReview the largest column tables in M_CS_TABLES (MEMORY_SIZE_IN_TOTAL) for archiving, data aging or partitioning candidates

2. CPU and threads

Query M_SERVICE_THREADS for active threads with a long DURATION, grouped by THREAD_TYPE and STATEMENT_HASHCRITICALUse M_SERVICE_THREAD_SAMPLES (or HOST_SERVICE_THREAD_SAMPLES for history) to see what threads were doing during the slow periodCheck 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 otherwiseCheck indexserver.ini [session] default_statement_concurrency_limit if single statements flood all coresConfirm no OS level CPU steal or power saving mode on virtualised hosts (hypervisor and BIOS settings per SAP guidance)

3. SQL and plan cache

Enable the expensive statements trace: global.ini [expensive_statement] enable = true with a threshold_duration in microseconds (for example 1000000 for 1 s)CRITICALRead M_EXPENSIVE_STATEMENTS for the top statements by DURATION_MICROSEC and MEMORY_SIZERank M_SQL_PLAN_CACHE by TOTAL_EXECUTION_TIME and by AVG_EXECUTION_TIME to separate frequent cheap statements from rare heavy onesCRITICALCheck plan cache hit ratio and evictions in M_SQL_PLAN_CACHE_OVERVIEW; high eviction counts point to an undersized indexserver.ini [sql] plan_cache_sizeCapture the explain plan or a PlanViz trace for the top statement before changing indexes or hintsFollow SAP Note 2000002 (FAQ: SAP HANA SQL Optimization) for statement tuning steps

4. Locks and transactions

Check M_BLOCKED_TRANSACTIONS for current lock waits and the blocking transaction (LOCK_OWNER_TRANSACTION_ID)CRITICALFind long open transactions in M_TRANSACTIONS by START_TIME, including idle connections that hold a transaction openCheck MVCC version counts in M_MVCC_TABLES (NAME = 'NUM_VERSIONS'); a high, growing count usually means an old open snapshot blocks garbage collectionCRITICALMap the blocker to the application user via M_CONNECTIONS before cancelling a sessionReview record lock waits in M_RECORD_LOCKS and table locks in M_TABLE_LOCKS for recurring hot spots

5. Persistence, merges and disk

Check savepoint duration and CRITICAL_PHASE_DURATION in M_SAVEPOINTS; long critical phases block DMLCRITICALList tables with a large delta store: M_CS_TABLES ordered by MEMORY_SIZE_IN_DELTALook for failed merges in M_DELTA_MERGE_STATISTICS (SUCCESS = 'FALSE' and LAST_ERROR)CRITICALCheck data and log volume I/O latency in M_VOLUME_IO_TOTAL_STATISTICS; slow log writes slow every commitCheck log segment states in M_LOG_SEGMENTS and confirm log backups run, so the log volume does not fillCRITICALConfirm data and log volume file systems have free space headroom

6. Housekeeping and configuration

Run HANA_Configuration_MiniChecks from the SQL statement collection in SAP Note 1969700 and interpret results with SAP Note 1999993CRITICALReview open alerts in _SYS_STATISTICS.STATISTICS_CURRENT_ALERTS or in SAP HANA cockpitConfirm the embedded statistics service is active (nameserver.ini [statisticsserver] active = true) so history views are filledCheck 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 analysisKeep the HANA revision current against the SAP HANA revision strategy and known issue notes

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.

Get your mini check output reviewed

Send us the mini check result and revision number and we will point out the findings worth acting on first.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

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.

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

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

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

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.

Frequently Asked Questions

What are SAP HANA mini checks?

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?

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?

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?

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.

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.

Talk to an Expert Explore More Free Tools

Related Tools

SAP

More SAP tools

See all

SAP HANA Performance Health Checklist: Memory, CPU, Locks and Merges