SAP HANA Performance Optimization: A Practitioner’s Guide to Memory, Queries, Indexes, and Workload Management

Arjun Mehta breaks down what you need to know

After two decades of wrestling with every database from Oracle 8i to SAP HANA Cloud, I’ve learned one hard truth: moving to an in-memory columnar store doesn’t eliminate performance tuning—it shifts the problem. Too many teams assume “it’s fast by default” and skip the fundamental disciplines that keep HANA humming under real-world load. In the field, I still see the same four areas crippling systems that should be screaming fast. Let me walk you through each one, with the concrete fixes that have saved my clients’ month-end closings more times than I can count.

Memory Management: The Foundation That’s Often Overlooked

HANA’s entire performance promise rests on keeping working data in memory. Yet I repeatedly find servers where global allocation is misconfigured, causing Linux to swap out column-store pages—a death sentence for analytical queries. The columnar engine assumes random access with near-zero latency; when the OS moves pages to disk, a query that should finish in milliseconds can balloon to minutes.

I recall a pharmaceutical client running a 2TB HANA system where standard CO-PA reports took 20 minutes during month-end. The culprit? The basis team had left the default global_allocation_limit parameter, effectively letting the OS manage memory. Swapping was happening silently. After we set a hard limit at 85% of physical RAM and pinned critical tables in memory, the same reports dropped to under 30 seconds.

Key configurations I always enforce:

• global_allocation_limit in global.ini – Typically 80-90% of physical RAM, leaving enough for the OS and other processes.
• NUMA-aware memory allocation – On multi-socket servers, bind HANA services to local memory to avoid expensive remote memory access. I’ve seen a 40% improvement in BW queries just by setting affinity and aligning with SLES parameters.
• Column store unload thresholds – Use minimal_unload_priority to keep frequently scanned tables resident. Monitor M_CS_UNLOADS weekly.

Bottom line: if you see any non-zero values in M_MEMORY for “swapped out” or heavy paging in HANA_Disks, you’ve already lost the HANA advantage. Don’t wait for a crisis.

Query Optimization with PlanViz: Don’t Trust the SQL Generator

Moving to HANA doesn’t mean your ABAP code or generated SQL is suddenly efficient. I’ve spent countless hours in PlanViz tracing queries that were “good enough” on Oracle but become monsters on HANA because implicit type conversions break column‑store compression and pruning.

Consider an actual case: a production planning report that performed 8 left outer joins on material, plant, and vendor tables. The execution time was 15 minutes. PlanViz revealed that a date filter was being applied after a full table scan because the field in the WHERE clause was a NVARCHAR while the column was DATE. The engine couldn’t use the column’s delta store or selective deletion. I rewrote the SQL to:

SELECT ...
FROM MARA
INNER JOIN MARC ON MARA.MATNR = MARC.MATNR
WHERE MARC.ERSDA = TO_DATE('20250101','YYYYMMDD')

Further, I split a complex OR condition into two UNION ALL branches, each benefiting from efficient index usage. The result: sub‑second response. The trick is to always run the expensive statements trace (ALTER SYSTEM ADD TRACE EXPENSIVE_STATEMENTS) and feed the output into PlanViz. Look for red markers—partition pruning not occurring, high estimated rows, or sort operations spilling to disk. Those are your rewrite targets.

Indexing Strategies: Composite vs. Full-Text and the Hidden Cost of Over-Indexing

Classic DBA habits die hard. I still see developers asking for B‑tree indexes on HANA. The column store already builds inverted indexes on every column, but you can improve specific query patterns with additional structures. The two I lean on most are:

• Composite (concatenated) attributes – For predicates that filter on multiple columns together, like MATNR and `BW

References

• SAP HANA DB Performance Tunning.- SAP HANA Platform Overview- SAP Community Hub

References

• SAP HANA DB Performance Tunning.
• SAP HANA Platform Overview
• SAP Community Hub