Module 07Production VerifiedHANA 2.0 SPS 04–07+

SAP HANA Production SQL Handbook

A dedicated operational reference manual separate from conceptual tutorials. Every query is annotated with required privileges, database container (SystemDB vs. Tenant), safety classification, important columns, expected output, and next diagnostic steps.

Execution Best Practice: In an MDC environment, querying SYS.M_* inside a tenant returns metrics for that tenant alone. To query across the entire physical landscape, connect to SystemDB on port 30013 and query views with tenant prefix.

Target Container:

Safety Level:

Showing 9 verified SQL diagnostic queriesVerified for SAP HANA 2.0 SPS 04 - SPS 07+

Database Identification, Version, Build and Patch RevisionSafe (Read-Only)

Retrieves exact database version, compile branch, build timestamp, and patch level.

Both

Privileges: PUBLIC or MONITORING

Target Engine: HANA 1.0 & HANA 2.0 (All SPS)

SELECT KEY, VALUE FROM SYS.M_SYSTEM_OVERVIEW WHERE SECTION = 'System' AND KEY IN ('Version', 'Build Date', 'Platform', 'Instance Number');

Key Diagnostic Columns

KEY:System attribute name

VALUE:Exact version string (e.g. 2.00.070.00...)

Expected Diagnostic Output

KEY             | VALUE
------------------------------------------------------
Version         | 2.00.070.00.1679941421 (fa/hana2sp07)
Build Date      | 2023-03-27 19:45:00
Platform        | Linux x86_64
Instance Number | 00

DBA Interpretation: Database is running SAP HANA 2.0 SPS 07 Revision 70.00 on Linux x86_64 platform.

Next Diagnostic Action: Cross-check revision with SAP Note 2399995 to check for recommended critical patch levels.

Service Memory Allocation & Global Allocation Limit (GAL)Safe (Read-Only)

Exposes total physical memory, resident memory, allocated memory, and memory headroom against Global Allocation Limit.

Both

Privileges: MONITORING or CATALOG READ

Target Engine: HANA 2.0 (All SPS)

SELECT HOST, PORT, SERVICE_NAME, ROUND(LOGICAL_MEMORY_SIZE / 1024 / 1024 / 1024, 2) AS LOGICAL_MEM_GB, ROUND(PHYSICAL_MEMORY_SIZE / 1024 / 1024 / 1024, 2) AS RESIDENT_MEM_GB, ROUND(TOTAL_MEMORY_USED_SIZE / 1024 / 1024 / 1024, 2) AS USED_MEM_GB, ROUND(EFFECTIVE_ALLOCATION_LIMIT / 1024 / 1024 / 1024, 2) AS EFFECTIVE_LIMIT_GB, ROUND((TOTAL_MEMORY_USED_SIZE / NULLIF(EFFECTIVE_ALLOCATION_LIMIT, 0)) * 100, 1) AS UTILIZATION_PCT FROM SYS.M_SERVICE_MEMORY ORDER BY SERVICE_NAME;

Key Diagnostic Columns

SERVICE_NAME:Engine process name (indexserver, nameserver)

RESIDENT_MEM_GB:Actual physical RAM consumed by the OS process

USED_MEM_GB:Pool memory currently allocated by HANA heaps

EFFECTIVE_LIMIT_GB:Maximum memory limit allocated to this service

UTILIZATION_PCT:Percentage of allowed limit used

Expected Diagnostic Output

HOST   | PORT  | SERVICE_NAME  | LOGICAL_MEM_GB | RESIDENT_MEM_GB | USED_MEM_GB | EFFECTIVE_LIMIT_GB | UTILIZATION_PCT
----------------------------------------------------------------------------------------------------------------------
hana01 | 30003 | indexserver   | 195.40         | 184.20          | 162.80      | 220.00             | 74.0
hana01 | 30001 | nameserver    | 5.10           | 4.10            | 3.20        | 58.00              | 5.5

DBA Interpretation: Indexserver is consuming 162.8 GB (74% of its 220 GB limit). The database has healthy memory headroom before approaching OOM thresholds.

Next Diagnostic Action: If UTILIZATION_PCT exceeds 85%, inspect M_HEAP_MEMORY to identify the top consuming heap allocators.

Host Resource Capacity: Physical RAM, CPU Usage & Swap ThrashingSafe (Read-Only)

Monitors overall Linux host health, total RAM, free OS memory, CPU utilization, and ensures swap space is not being paged.

Both

Privileges: MONITORING

Target Engine: HANA 2.0 (All SPS)

SELECT HOST, ROUND(TOTAL_PHYSICAL_MEMORY / 1024 / 1024 / 1024, 2) AS TOTAL_RAM_GB, ROUND(FREE_PHYSICAL_MEMORY / 1024 / 1024 / 1024, 2) AS FREE_RAM_GB, ROUND(USED_PHYSICAL_MEMORY / 1024 / 1024 / 1024, 2) AS USED_RAM_GB, ROUND(TOTAL_SWAP_SPACE_SIZE / 1024 / 1024 / 1024, 2) AS TOTAL_SWAP_GB, ROUND(USED_SWAP_SPACE_SIZE / 1024 / 1024 / 1024, 2) AS USED_SWAP_GB, CPU_USAGE AS TOTAL_HOST_CPU_PCT FROM SYS.M_HOST_RESOURCE_UTILIZATION;

Key Diagnostic Columns

TOTAL_RAM_GB:Physical RAM installed in server chassis

USED_SWAP_GB:Amount of swap space actively paged (CRITICAL: should be near 0)

TOTAL_HOST_CPU_PCT:Combined CPU consumption across all cores

Expected Diagnostic Output

HOST   | TOTAL_RAM_GB | FREE_RAM_GB | USED_RAM_GB | TOTAL_SWAP_GB | USED_SWAP_GB | TOTAL_HOST_CPU_PCT
----------------------------------------------------------------------------------------------------
hana01 | 256.00       | 58.40       | 197.60      | 32.00         | 0.10         | 18

DBA Interpretation: Host has 256 GB RAM with 58.4 GB free. Swap used is only 0.10 GB (near zero), confirming that the in-memory engine is not thrashing swap disk.

Next Diagnostic Action: If USED_SWAP_GB is > 2 GB, Linux kernel is paging out HANA pages due to excessive memory pressure or misconfigured vm.swappiness.

Active Running Statements & CPU-Intensive Worker ThreadsSafe (Read-Only)

Exposes statements currently executing in the engine right now, their runtime, executing user, and thread state.

Tenant

Privileges: MONITORING

Target Engine: HANA 2.0 (All SPS)

SELECT S.STATEMENT_ID, S.CONNECTION_ID, S.USER_NAME, S.APPLICATION_USER_NAME, ROUND(S.ALLOCATED_MEMORY_SIZE / 1024 / 1024, 2) AS ALLOC_MEM_MB, ROUND(S.ELAPSED_TIME / 1000000, 2) AS RUNTIME_SEC, S.STATEMENT_STATUS, SUBSTR(S.STATEMENT_STRING, 1, 120) AS SQL_TEXT FROM SYS.M_ACTIVE_STATEMENTS S ORDER BY S.ELAPSED_TIME DESC;

Key Diagnostic Columns

CONNECTION_ID:Client session ID (used if cancellation is needed)

RUNTIME_SEC:Active execution duration in seconds

ALLOC_MEM_MB:Memory dynamically allocated to this query

SQL_TEXT:Preview of the executing SQL statement

Expected Diagnostic Output

STATEMENT_ID     | CONNECTION_ID | USER_NAME | APPLICATION_USER_NAME | ALLOC_MEM_MB | RUNTIME_SEC | STATEMENT_STATUS | SQL_TEXT
---------------------------------------------------------------------------------------------------------------------------------------------------
1840291058201090 | 329102        | SAPABAP1  | RFC_BATCH_USER        | 4210.50      | 145.20      | Running          | SELECT VBAP.VBELN, VBAP.POSNR, VBAK.KUNNR FROM VBAP JOIN VBAK...

DBA Interpretation: Batch RFC user has been running a join on VBAP/VBAK for 145 seconds and has allocated 4.2 GB of memory.

Next Diagnostic Action: If statement exceeds business SLA or threatens OOM, cancel it using ALTER SYSTEM CANCEL SESSION '<CONNECTION_ID>'.

Lock Contention & Root Blocking Transaction IdentificationSafe (Read-Only)

Surgically identifies blocked sessions, lock mode requested, and the root blocker holding the exclusive lock.

Tenant

Privileges: MONITORING

Target Engine: HANA 2.0 (All SPS)

SELECT B.WAITING_CONNECTION_ID, B.LOCK_OWNER_CONNECTION_ID AS ROOT_BLOCKER_CONN_ID, B.LOCK_TYPE, B.LOCK_MODE, ROUND(B.WAITING_TIME / 1000, 2) AS WAIT_TIME_SEC, B.SCHEMA_NAME, B.TABLE_NAME, C_WAIT.APPLICATION_USER_NAME AS WAITING_USER, C_HOLD.APPLICATION_USER_NAME AS HOLDING_USER, SUBSTR(C_HOLD.CURRENT_STATEMENT_STRING, 1, 100) AS BLOCKER_STATEMENT FROM SYS.M_BLOCKED_TRANSACTIONS B JOIN SYS.M_CONNECTIONS C_WAIT ON B.WAITING_CONNECTION_ID = C_WAIT.CONNECTION_ID JOIN SYS.M_CONNECTIONS C_HOLD ON B.LOCK_OWNER_CONNECTION_ID = C_HOLD.CONNECTION_ID ORDER BY B.WAITING_TIME DESC;

Key Diagnostic Columns

WAITING_CONNECTION_ID:Session stuck waiting for lock

ROOT_BLOCKER_CONN_ID:Offending session holding the lock

WAIT_TIME_SEC:Duration in seconds the session has been stuck

TABLE_NAME:Contended database table

HOLDING_USER:Business user holding the root lock

Expected Diagnostic Output

WAITING_CONNECTION_ID | ROOT_BLOCKER_CONN_ID | LOCK_TYPE | LOCK_MODE | WAIT_TIME_SEC | SCHEMA_NAME | TABLE_NAME | WAITING_USER | HOLDING_USER | BLOCKER_STATEMENT
-------------------------------------------------------------------------------------------------------------------------------------------------------------
329400                | 328105               | RECORD    | EXCLUSIVE | 420.50        | SAPABAP1    | ACDOCA     | FINANCE_01   | BATCH_REORG  | UPDATE ACDOCA SET XBLNR = ...

DBA Interpretation: BATCH_REORG (Connection 328105) holds an exclusive record lock on ACDOCA for 420 seconds, blocking FINANCE_01 from completing transactions.

Next Diagnostic Action: Contact holding user or execute ALTER SYSTEM DISCONNECT SESSION '328105' if business operations are frozen.

Top 10 Longest-Running Expensive SQL StatementsSafe (Read-Only)

Profiles the most expensive SQL queries based on cumulative elapsed execution duration from M_EXPENSIVE_STATEMENTS.

Tenant

Privileges: MONITORING

Target Engine: HANA 2.0 (All SPS)

SELECT TOP 10 STATEMENT_HASH, USER_NAME, APPLICATION_NAME, START_TIME, ROUND(DURATION_MICROSEC / 1000000, 2) AS RUNTIME_SEC, ROUND(CPU_TIME / 1000000, 2) AS CPU_SEC, ROUND(MEMORY_SIZE / 1024 / 1024, 2) AS MEM_MB, RECORDS, SUBSTR(STATEMENT_STRING, 1, 150) AS SHORT_SQL FROM SYS.M_EXPENSIVE_STATEMENTS ORDER BY DURATION_MICROSEC DESC;

Key Diagnostic Columns

STATEMENT_HASH:Unique hash to lookup query in Plan Cache

RUNTIME_SEC:Total elapsed time in seconds

CPU_SEC:CPU processing time across all worker threads

MEM_MB:Peak memory allocated

Expected Diagnostic Output

STATEMENT_HASH                   | USER_NAME | APPLICATION_NAME | START_TIME          | RUNTIME_SEC | CPU_SEC | MEM_MB  | RECORDS | SHORT_SQL
---------------------------------------------------------------------------------------------------------------------------------------------------
a8f9210c48e02914bb10294109204910 | SAPABAP1  | ABAP:SAPL...     | 2026-10-23 06:14:02 | 340.20      | 850.40  | 6200.00 | 1250000 | SELECT * FROM BSEG WHERE BUKRS = ...

DBA Interpretation: Query took 340 seconds with 850 seconds of CPU time (indicating high multi-core parallelism) and allocated 6.2 GB RAM.

Next Diagnostic Action: Generate Explain Plan using: EXPLAIN PLAN FOR SELECT ... or analyze statement hash in Plan Visualizer.

Backup Catalog: Recent Full, Differential and Log Backup HealthSafe (Read-Only)

Validates that overnight full data backups and automated redo log backups completed successfully.

Both

Privileges: BACKUP ADMIN or MONITORING

Target Engine: HANA 2.0 (All SPS)

SELECT TOP 15 ENTRY_TYPE_NAME, STATE_NAME, UTC_START_TIME, UTC_END_TIME, ROUND(SECONDS_BETWEEN(UTC_START_TIME, UTC_END_TIME) / 60, 2) AS DURATION_MIN, ROUND(BACKUP_SIZE / 1024 / 1024 / 1024, 2) AS SIZE_GB, MESSAGE FROM SYS.M_BACKUP_CATALOG ORDER BY UTC_START_TIME DESC;

Key Diagnostic Columns

ENTRY_TYPE_NAME:complete data backup, log backup, differential data backup

STATE_NAME:successful, failed, or running

DURATION_MIN:Duration in minutes

SIZE_GB:Backup size on disk or Backint

Expected Diagnostic Output

ENTRY_TYPE_NAME       | STATE_NAME | UTC_START_TIME      | UTC_END_TIME        | DURATION_MIN | SIZE_GB | MESSAGE
-------------------------------------------------------------------------------------------------------------------
complete data backup  | successful | 2026-10-23 01:00:00 | 2026-10-23 01:42:15 | 42.25        | 412.50  | Backup completed.
log backup            | successful | 2026-10-23 08:15:00 | 2026-10-23 08:15:10 | 0.17         | 0.52    |

DBA Interpretation: Full data backup completed successfully in 42 minutes (412 GB). Log backups are running continuously every 15 minutes.

Next Diagnostic Action: If any backup shows "failed", inspect the MESSAGE column and backup.log trace.

HANA System Replication (HSR): Connection State & Log Shipping SyncSafe (Read-Only)

Verifies primary-secondary replication connection state, sync mode, secondary active status, and log shipping replay backlog.

SystemDB

Privileges: MONITORING

Target Engine: HANA 2.0 (All SPS)

SELECT SITE_NAME, SECONDARY_SITE_NAME, REPLICATION_MODE, OPERATION_MODE, CONNECTION_STATE, SECONDARY_ACTIVE_STATUS, ROUND(BACKLOG_SIZE / 1024 / 1024, 2) AS BACKLOG_MB, ROUND(SHIPPED_LOG_BUFFERS_DURATION / 1000, 2) AS SHIPPING_LATENCY_SEC FROM SYS.M_SYSTEM_REPLICATION ORDER BY SITE_NAME;

Key Diagnostic Columns

REPLICATION_MODE:SYNC, SYNCMEM, or ASYNC

OPERATION_MODE:logreplay or delta_datashipping

CONNECTION_STATE:ACTIVE, CONNECTING, or ERROR

SECONDARY_ACTIVE_STATUS:YES (in sync) or NO (behind / down)

BACKLOG_MB:Unshipped redo log volume pending replication

Expected Diagnostic Output

SITE_NAME | SECONDARY_SITE_NAME | REPLICATION_MODE | OPERATION_MODE | CONNECTION_STATE | SECONDARY_ACTIVE_STATUS | BACKLOG_MB | SHIPPING_LATENCY_SEC
---------------------------------------------------------------------------------------------------------------------------------------------
SITE_PRI  | SITE_SEC            | SYNCMEM          | logreplay      | ACTIVE           | YES                     | 0.00       | 0.05

DBA Interpretation: HSR is in SYNCMEM mode with logreplay. Connection is ACTIVE, secondary is fully in sync with zero backlog and sub-second network shipping latency.

Next Diagnostic Action: If CONNECTION_STATE is ERROR or BACKLOG_MB is growing continuously, inspect network interface and secondary nameserver trace.

Emergency Session Remediation: Cancel Statement or Terminate SessionModifying (State Change)

Gracefully rolls back a runaway query or forcefully kills an unresponsive database connection.

Tenant

Privileges: SESSION ADMIN

Target Engine: HANA 2.0 (All SPS)

-- Step 1: Identify offending Connection ID SELECT CONNECTION_ID, USER_NAME, CLIENT_HOST, APPLICATION_NAME, CURRENT_STATEMENT_STRING FROM SYS.M_CONNECTIONS WHERE CURRENT_STATEMENT_STRING LIKE '%ACDOCA%'; -- Step 2: Gracefully CANCEL the statement (rolls back current statement only; session remains open) ALTER SYSTEM CANCEL [ABORT] SESSION '329102'; -- Step 3: If hung or thread fails to abort, forcefully TERMINATE the entire connection ALTER SYSTEM DISCONNECT SESSION '329102';

DBA Interpretation: CANCEL SESSION sends an interrupt signal to the executing thread to roll back current query. DISCONNECT SESSION severs the TCP socket and frees all held locks immediately.