SAP HANA Performance

HANA is fast because it is in-memory and columnar, so set-based scans and aggregations fly. You keep it fast by working with the grain: filter and aggregate in the database, avoid moving large data sets out, and prefer set-based SQL/CDS over procedural row loops.

• Row-by-row processing (loops) instead of set operations.
• Pulling large data to the app tier instead of pushing logic down.
• Expensive joins / late aggregation in poorly-designed views.
• Missing filters scanning more than needed.

Performance problems on HANA cluster into a small number of shapes, and recognising them shortens diagnosis considerably.

Row-by-row processing. Code that selects rows and loops. On a database built for set operations this is the dominant cause of anything slow, and the fix is a statement rather than a tune.

Filters that do not reach the database. A predicate wrapped in a function, applied to a calculated column, or left to the consumer. The plan shows the whole table being read, and the filter happening afterwards.

Wrong join cardinality in a model. Declared optimistically, so pruning either does not happen or produces wrong results.

Unmerged delta. A table with heavy writes and infrequent merges carries a large delta store, which is slower to read and consumes memory. Nothing obvious points at it.

Memory pressure. Tables unloaded and reloaded on access, producing performance that varies unpredictably rather than failing.

Missing selectivity. A query that genuinely has to read most of a very large table, where the answer is a different data model rather than tuning.

The order to check them is roughly this list, because the first two account for most of what a consultant meets.

Use HANA’s tools, the SQL plan visualizer (Plan Viz) and Explain Plan to see how a query executes, plus the HANA cockpit for system-level monitoring. These show where time and memory go so you optimise the real bottleneck.

Which tool for which question. PlanViz for one slow statement, showing operators, row counts and time per step. The expensive statements trace for finding which statements are costly across the system, which is where to start rather than with the one somebody complained about. SQL trace for what an application actually sent, which is how you discover a loop issuing ten thousand statements. And the monitoring views for memory, unloads and delta sizes.

One habit makes all four tools more useful: capture a baseline while the system is behaving normally. A plan, a memory reading and a top ten expensive statements list taken on a good day give you something to compare against on a bad one. Without a baseline, every number looks either fine or alarming depending on who is reading it, and the argument about whether the system has actually got slower cannot be settled. Ten minutes a month is enough to keep one current.

An hour on a system with real load, and it is the core diagnostic skill.

1. Open the expensive statements trace and sort by total execution time rather than by duration.
2. Take the top entry. Note whether it is one slow statement or one fast statement executed constantly.
3. Run it through PlanViz. Read where the filter was applied and the row count at each step.
4. If rows are read and discarded late, the predicate did not push down. Look for a function around the filtered column.
5. If the plan is sensible and the volume is simply large, check whether the table is partitioned and whether pruning applied.
6. Check the table's delta size and last merge while you are there.

Step two is the discipline. A statement taking two seconds and running fifty thousand times a day costs far more than one taking a minute once, and total time is what says so.

Memory questions on HANA get answered badly because the obvious number is the wrong one. Used memory as the operating system reports it includes the pool HANA has reserved and not necessarily filled, so it looks alarming and means little. The numbers worth reading are different.

Resident versus used. Used is what HANA is actually holding. Resident is what the operating system has given it. They differ, and comparing the wrong pair produces false alarms.

The allocation limit. A ceiling HANA will not cross. Approaching it is the signal that matters.

Column unloads. A count that should be near zero. Rising unloads mean HANA is evicting data to stay under the limit, which it reloads on next access. This is the mechanism behind the classic complaint that the system is fast in the morning and slow after lunch.

What consumes memory, in the order worth checking: the column tables themselves, the delta stores of heavily written tables, intermediate results of large queries, and the statement memory of a few badly written statements that each request enormous working sets.

The last one deserves attention because it is the one a single user can cause. One statement joining without a filter can request more memory than the entire rest of the workload, and the effect on everybody else is immediate. The expensive statements trace records peak memory alongside duration, which is why it answers memory questions as well as time ones.

Layer and simplify calculation/CDS views, push filters/aggregation down, and minimise data movement. Because the engine is so fast, most remaining performance problems are design problems, solvable by better models and SQL.

Two rules cover most of it. Filter as early as the model allows, in a projection at the bottom rather than at the top, so the work above is done on fewer rows. And let aggregation happen at the bottom too, rather than pulling detail up and summing it. Both are visible in the plan, which is why reading one is worth more than memorising guidelines. See the architecture for why these hold.

Partitioning belongs in the same conversation. A very large table split by a key the queries actually filter on lets the engine skip whole partitions, and the same table split by a key nothing filters on adds overhead and buys nothing. So partitioning is a decision made from the query pattern, not from the row count, which is the way it is usually made.

• Row loops instead of set-based logic.
• Late/absent filtering and aggregation.
• Not using Plan Viz/Explain to find the real issue.

• Adding indexes reflexively. Column stores are searched differently, and an index is useful in narrower circumstances than on a row database.
• Tuning what somebody complained about. The expensive statements trace usually names something else.
• Ignoring unload statistics. See backup and security and administration for the operational context.

Two more that come up constantly. Testing on a system with no load. A statement that performs well alone can be the one that pushes a busy system over its memory limit, and a single user test cannot show that. Fixing the symptom in the model. Adding a filter high in a calculation view because the report is slow hides the fact that the same view is slow for every other consumer too; the fix belongs where the data is read.

Version note: the tooling for all of this has moved into the HANA cockpit and the database explorer, while older material refers to HANA Studio. The traces, the views and the plan output are the same underneath, so an instruction written for the studio translates once you know which cockpit screen replaces it.

Reading a plan is the diagnostic half, and rewriting the model or the code so the database does the work is the part you do in the course.