Introduction

SAP HANA is already fast thanks to its in-memory columnar engine, but poorly written SQL, sub-optimal data-modeling, or misconfigured hardware can turn even HANA into a bottleneck. In this post we’ll deep-dive into the 2025 best practices that keep HANA screaming along, covering architecture basics, tuning levers, common anti-patterns, and real-world fixes.

1. Quick Refresher: How SAP HANA Executes Your Workload

  • In-Memory Column Store – Data blocks sit in RAM, compressed, sorted, and dictionary-encoded.
  • Row Store – Smaller, transactional tables; ideal for metadata or lookup data.
  • Multicore & Massive Parallel Processing (MPP) – Queries split into operators that fan out across CPU threads.
  • Delta Store & Merge – Inserts land in a write-optimized delta; merges convert them to read-optimized main.
  • Persistent Storage Layer – Every 5 min HANA saves a savepoint to disk; redo logs stream continuously for crash recovery.

Understanding where your query burns CPU or IO is the first step toward optimisation.

2. Hardware & Deployment Checklist (2025 Edition)

  • Memory Sizing – Aim for 2× data footprint in RAM. Example: 1 TB compressed data ⇒ 2 TB RAM.
  • NUMA Awareness – Map NUMA nodes to HANA services to prevent remote-memory hops.
  • Intel Ice Lake / AMD EPYC Genoa – 64+ physical cores, large L3. HANA SPS 07 is now fully optimised for both.
  • NVMe Tier-1 Log Volume – 1 GB/s sustained write; 2× volumes in RAID-10.
  • Scale-Out vs Scale-Up – If queries touch >50 % of total data, scale-up; else scale-out with SSD-attached storage.

3. Data Modeling Best Practices

  • Flatten Hierarchies – Replace snowflakes with star schemas; remove unnecessary joins.
  • Partition Large Tables (>2 Bn rows) – Hash on primary key to parallelise merges; or range on CALMONTH.
  • Avoid Calculated Columns in Views – Materialise them in the base table or a snapshot table.
  • Use Proper Data Types – INT1/2, TINYINT, SMALLDECIMAL save RAM and CPU.
  • Remove Unused Columns – Each extra column adds dictionary and compression overhead.

4. Indexing & Compression

  • Inverted Index for Text – Speeds up CONTAINS predicates by 10-50×.
  • Full-Text & Fuzzy Search – Can coexist with column-store; test cardinality before enabling.
  • Secondary Index on Row Store – Only when column-store doesn’t serve the pattern.
  • Compression Types – Run
CALL "SYS"."PROC_COMPRESSION_OPTIMIZE"(
    TABLE_NAME => 'SALES_ORDER'
);

to let HANA auto-choose RLE, dictionary, or sparse.

5. Query Optimisation Workflow

  1. Capture the Query – Use HANA Cockpit → Performance → Expensive Statements.
  2. Plan Visualization – Click Visualize Plan; study operators.
  3. Look for:
    • Hot operators (≥70 % of runtime)
    • Blocked merges / large row engines
    • Inefficient joins (n×m)
  4. Rewrite or HINT – Common tricks:
-- Force join order
SELECT /*+ JOIN_THRU_AGGR */ ...
-- Avoid row engine
ALTER TABLE PRODUCT COLUMN STORE;

5. Retest – Target ≥50 % runtime reduction.

6. Managing the Delta Merge

Poorly merging deltas is the silent killer of HANA performance.

  • Monitor
SELECT TABLE_NAME, DELTA_MERGE_OUTSTANDING
FROM M_CS_TABLES
WHERE DELTA_MERGE_OUTSTANDING > 0;
  • Tune Merge Policy –
ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM') SET ('merge','smart_merge_decision') = 'on';
  • Schedule Off-Peak Forced Merge – Use SAP Job Scheduler or cron + hdbsql.

7. Leveraging Native Predictive & ML (Without Killing CPU)

  • Use PAL/APL predictive libraries inside stored procedures; allocate dedicated SCRIPTING threads.
  • Cache intermediate results using CREATE LOCAL TEMPORARY COLUMN TABLE.
  • Pin frequently used models to dynamic tiering if RAM is tight.

8. Security vs Performance

  • Encryption in Memory (default off) adds ~3 % overhead – enable only for highly sensitive tables.
  • Auditing – Keep audit trail retention ≤90 days, archive older logs to cold storage.
  • Row-Level Security – Use FILTER clauses in Calculation Views rather than view-on-view chains.

9. Monitoring & Alerting Stack

  • HANA Cockpit 2.0 SP15 – Built-in, browser-based; supports Grafana plug-ins.
  • SQL Statement Trace (ST05) – Still the fastest way to trace binding variables.
  • E2E Diagnostics – Integrates with SAP Focused Run for enterprise dashboards.
  • Alert KPIs to watch:
    • CPU >80 % for 15 min
    • Column Unload/Reload Events
    • Savepoint Duration >60 s
    • Redo Log Write Latency >10 ms

10. Mini Case Study – Retailer Cuts Nightly Batch from 5 h to 42 min

  • Problem – 3 Bn row sales fact table, nightly ETL hung on MERGE INTO.
  • Fix –
    1. Partitioned fact table by fiscal year.
    2. Converted small lookup tables to row store for faster point lookup.
    3. Added PARALLEL_HINT 32 on ETL procedure.
    4. Enabled Smart Merge and scheduled aggressive deltas.
  • Result – Batch finished in 42 min, freeing 4 h SLA buffer; user ad-hoc reporting latency dropped 68 %.

11. Migration & Upgrade Pointers (SPS 07 → SPS 08 Preview)

  • Preparation Checks – Run SAP HDBLCM --action=check_installation.
  • Custom Code Regression – ABAP on HANA: use ATC variant S4HANA_READINESS_2025.
  • Zero-Downtime Option (ZDO) – Now GA; requires System Replication with version rollback snapshot.
  • Cloud Migration – RISE with SAP offers HANA Cloud, Data Lake tiers for cold/archival data.

Conclusion

Performance tuning in SAP HANA is equal parts art and science. Start with data modeling, keep an eye on the delta merge, profile your SQL, and leverage the ever-growing tooling that SAP bundles into each SPS release. With the 2025 optimisations above, you’ll squeeze every last nanosecond from your hardware while keeping TCO in check.