SAP HANA Query Optimization: From Explain Plans to Workload Classes

Giulia Ferrari breaks down what you need to know

A single long-running query can spike memory usage to the point where HANA declares an out-of-memory (OOM) event, taking down critical processes. I’ve seen this panic in developers’ eyes too many times. The recent paper on HANA optimization techniques reminds us that the fundamentals still matter—explain plans, early filtering, workload management—but the real skill is stitching them together into a cohesive defense. Here’s how to move from firefighting to architectural resilience.

The Real Story: Explain Plans Are Your First Line of Defense

Most developers wait until a query becomes noticeably slow before investigating. By then, you’re already in reactive mode. The paper emphasizes explain plans and avoiding full table scans as foundational steps, but I’d argue that every stored procedure or CDS view should be profiled during development, not after.

Take a typical ABAP CDS view that joins BKPF and BSEG. If the developer doesn’t apply an early filter on the fiscal year, HANA may resort to a full table scan on BSEG—millions of rows, all in memory, wasted. Use EXPLAIN PLAN and then query PLAN_TABLE to check for TABLE SCAN operations:

EXPLAIN PLAN FOR SELECT * FROM ZCDS_BSEG_HUGE WHERE ... ;
SELECT * FROM PLAN_TABLE WHERE OPERATION = 'TABLE SCAN';

Or, more practically, open PlanViz in HANA Studio and look for red boxes. Full table scans aren’t always evil—small dimension tables are fine—but on fact tables they’re a disaster. The fix is often pushing where‑clause conditions as high as possible, even into the lowest-level views. Don’t assume the HANA optimizer will magically propagate filters; sometimes it needs a push.

The biggest trap? Dynamic SQL from Fiori apps or generic reporting tools. You can’t unit-test every generated statement, but you can set up monitoring (more on that later) to catch the worst offenders early.

What This Means for You: Resource Management Is a Collaboration, Not a Basis‑Only Task

The paper rightly points to memory limits and workload classes for prioritizing processes. Many organizations treat HANA’s memory as a black box—until OOM. In my research, I’ve found that simple workload classification can reduce critical query wait times by 40% during peak loads.

Here’s the non-obvious part: developers need to define workload classes with basis, not wait for them. For example, create a high‑priority class for real‑time sales order processing and a low‑priority one for nightly data loads:

CREATE WORKLOAD CLASS "CRITICAL_FOREGROUND" SET 'PRIORITY' = 9;
ALTER WORKLOAD CLASS "CRITICAL_FOREGROUND" ENABLE;
-- assign to specific application user or connection
ALTER USER SALES_APP_USER SET PARAMETER WORKLOAD_CLASS = 'CRITICAL_FOREGROUND';

Then, set a memory limit per statement to stop a rogue query from eating everything:

ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'system') 
SET ('memorymanager', 'statement_memory_limit') = '32' WITH RECONFIGURE;

Be cautious: setting it too

References

• Performance Optimization in SAP HANA
• SAP HANA Platform Overview- SAP AI Core Documentation

References

• SAP AI Core Documentation
• SAP HANA Platform Overview
• Performance Optimization in SAP HANA