HANA performance monitoring: the signals that matter

HANA exposes hundreds of M_* monitoring views, and it is easy to drown in them. The useful question is not "what can I monitor" but "what do I watch continuously so a problem surfaces while it is still small". This is a practitioner's shortlist: the signals worth a permanent place on a HANA monitoring view, the exact system views behind them, and what to alert on versus merely trend. Everything below is queryable with hdbsql or any SQL client.

# Connect with hdbsql. Prefer a stored secure-store key over inline creds.
hdbsql -U MONITORING_KEY                       # key from hdbuserstore
hdbsql -n <host>:3<inst>15 -d <TENANT> -u <user> -p <pw>   # explicit
#   port 3<inst>13 = SYSTEMDB, 3<inst>15 = first tenant (MDC)

Memory: the signal HANA lives and dies by

HANA is an in-memory database, so memory is the first thing to watch and the one most likely to ruin a day. Watch used memory against the allocation limit, not against physical RAM. Sustained pressure here is what precedes an out-of-memory (OOM) dump.

-- Used memory vs the effective allocation limit, per service
SELECT HOST, SERVICE_NAME,
       ROUND(TOTAL_MEMORY_USED_SIZE/1024/1024/1024, 1)          AS used_gb,
       ROUND(EFFECTIVE_ALLOCATION_LIMIT_SIZE/1024/1024/1024, 1) AS limit_gb,
       ROUND(100 * TOTAL_MEMORY_USED_SIZE
                 / EFFECTIVE_ALLOCATION_LIMIT_SIZE, 1)           AS pct
FROM   M_SERVICE_MEMORY
ORDER  BY pct DESC;

-- Host-level physical picture (resident vs installed RAM)
SELECT HOST, USED_PHYSICAL_MEMORY, FREE_PHYSICAL_MEMORY,
       ALLOCATION_LIMIT
FROM   M_HOST_RESOURCE_UTILIZATION;

Trend the percentage always; alert when a service crosses a high-water mark (e.g. 90% of its allocation limit) with enough headroom to react. Resident vs physical RAM catches a neighbour process squeezing HANA before HANA itself errors.

The column store: delta merges and table growth

HANA's column store buffers writes in a delta store and periodically merges them into the read-optimised main store. When merges fall behind, whether failing or starved because the system is busy, queries slow and memory climbs. Watch tables with a large or growing delta, and merges that error.

-- Biggest column tables + how much sits unmerged in the delta store
SELECT TOP 20 SCHEMA_NAME, TABLE_NAME,
       ROUND(MEMORY_SIZE_IN_TOTAL/1024/1024, 0) AS mem_mb,
       RAW_RECORD_COUNT_IN_DELTA                 AS delta_rows
FROM   M_CS_TABLES
ORDER  BY delta_rows DESC;

-- Delta merges that failed recently (ERROR is the one to alert on)
SELECT START_TIME, SCHEMA_NAME, TABLE_NAME, MOTIVATION, EXECUTION_TIME
FROM   M_DELTA_MERGE_STATISTICS
WHERE  SUCCESS = 'FALSE'
ORDER  BY START_TIME DESC;

CPU and the savepoint rhythm

CPU on a HANA host is usually fine until a batch window collides with online users. Trend host CPU for sustained saturation that means the workload has outgrown the sizing. Alongside it, watch savepoint duration: savepoints taking noticeably longer signal I/O pressure on the data/log volumes, an early warning that storage needs attention.

-- Recent savepoints; CRITICAL_PHASE_DURATION is the blocking part
SELECT TOP 20 START_TIME, DURATION, CRITICAL_PHASE_DURATION, TOTAL_SIZE
FROM   M_SAVEPOINTS
ORDER  BY START_TIME DESC;

Disk: data, log, and the trace directory nobody watches

-- Disk usage by type (DATA / LOG / TRACE / BACKUP)
SELECT HOST, USAGE_TYPE,
       ROUND(USED_SIZE/1024/1024/1024, 1)  AS used_gb,
       ROUND(TOTAL_SIZE/1024/1024/1024, 1) AS total_gb
FROM   M_DISK_USAGE ORDER BY used_gb DESC;

-- Last successful data + log backup (age is the alert)
SELECT ENTRY_TYPE_NAME, STATE_NAME, MAX(SYS_END_TIME) AS last_ok
FROM   M_BACKUP_CATALOG
WHERE  STATE_NAME = 'successful'
GROUP  BY ENTRY_TYPE_NAME, STATE_NAME;

Blocked transactions and long-runners

A single long statement holding a lock stalls the queue behind it. The point is not to auto-kill. It is to make a human aware while the queue is short, not after the helpdesk lights up.

-- Who is blocked, and by whom
SELECT BLOCKED_TRANSACTION_ID, LOCK_OWNER_TRANSACTION_ID,
       LOCK_TYPE, WAITING_SCHEMA_NAME, WAITING_TABLE_NAME
FROM   M_BLOCKED_TRANSACTIONS;

-- Statements running far longer than normal (seconds)
SELECT CONNECTION_ID, ROUND(DURATION/1000/1000) AS secs, STATEMENT_STRING
FROM   M_ACTIVE_STATEMENTS
WHERE  DURATION > 60*1000*1000
ORDER  BY DURATION DESC;

System replication, if you run it

If the database is protected by HANA System Replication (HSR), the replication state is first-class. Watch that the secondary is ACTIVE and in sync, and watch the shipping backlog. A secondary that has silently fallen out of sync is a DR posture that exists on paper only.

SELECT HOST, SECONDARY_HOST, REPLICATION_MODE,
       REPLICATION_STATUS,             -- ACTIVE / SYNCING / ERROR
       SHIPPED_LOG_POSITION - LAST_SAVEPOINT_LOG_POSITION AS backlog
FROM   M_SERVICE_REPLICATION;

What to alert on versus what to trend

A view that alerts on everything trains operators to ignore it. A division that works:

Multi-tenant adds one rule

On an MDC system every signal above exists per tenant as well as for SYSTEMDB, and a healthy SYSTEMDB does not mean a healthy tenant. From SYSTEMDB you can read across tenants via the SYS_DATABASES schema (for example SYS_DATABASES.M_SERVICE_MEMORY), but the monitoring layer still has to present them as one operational view while keeping each tenant's data isolated, which is a harder problem than collecting the metrics, and the subject of a separate guide on multi-tenant HANA.

Where Farrenio fits

Farrenio's HANA database monitoring is built around this handful of signals, read continuously across many databases, with the tenant boundary intact and an audit trail behind every access, sitting next to the application-side Basis transactions so the database view and the SM50/SM37/ST22 view are one screen, not two tools. To see these signals against your own HANA, write to contact@farrenio.com and we will scope a short trial on a non-production database.