🔥 DAY05 [SAP HANA Database & Performance] {Part-50}
Welcome to the day where you stop guessing and start proving why a query is slow. After four days of architecture, SQLScript, modeling, and tooling, you now face the ultimate test: performance. The most elegant calculation view means nothing if it runs for 45 seconds when the business expects 2. Today you’ll learn the three pillars of HANA performance tuning: PlanViz to see exactly what the engine does, Expensive Statement Trace to catch the culprits in production, and Partitioning to restructure data so that even billion‑row tables answer in milliseconds. This is the part of the series that directly saves companies money, reduces cloud bills, and gets you promoted.
We’ll use the universal journal ACDOCA as our battlefield, apply realistic tuning techniques, and measure the impact with concrete numbers. By the end, you’ll have a repeatable performance optimization workflow that works on any S/4HANA system.
🔬 PlanViz – The Execution Plan Visualizer
PlanViz (Plan Visualizer) is HANA’s built‑in tool to graphically display the execution plan of a SQL statement or calculation view. It shows you which engines (column store, row store, OLAP, join) are invoked, how much data flows between operators, and where the bottlenecks hide. Every performance investigation starts here.
🚀 Launching PlanViz
From HANA Studio (or HANA Database Explorer in BAS), execute your query with the “Explain Plan” option or press Ctrl+Shift+X. The PlanViz tab opens, displaying a tree of operators. Each operator is a node; lines connect them to show data flow. You can hover over any node to see estimated row counts, execution time, and engine type.
🧩 Understanding the Key Operators
- Column Search (BWPopUp): The ideal operator for scanning column‑store tables. Shows that the engine is reading only needed columns and applying predicates (filters) efficiently.
- Row Search: Appears when a row‑store table is accessed, or when a column‑store access falls back to row‑store due to missing dictionary or dynamic conditions. This is often a performance killer.
- Join Engine: Executes joins. PlanViz shows join type (hash, nested loop) and the estimated cardinality.
- Materialization (Temp): When intermediate result sets are written to memory – too many materializations indicate a poorly structured model.
- OLAP Engine: Used for calculation views with star schema or hierarchy processing. It can be efficient for analytical queries but may add overhead if misused.
🔍 Real‑Life Case: Spotting a Column‑Store to Row‑Store Fallback
Imagine a query on ACDOCA that filters on BUKRS = '1000'. PlanViz shows a “Row Search” operator. Why? You check the table definition and realize that ACDOCA is indeed column‑store, but one of the filter columns (maybe a custom field) was flagged as “NO DICTIONARY” and HANA decided to use the row store for that column. The fix: either adjust the column properties or rewrite the filter to avoid that column. After correction, the query plan switches to Column Search, and runtime drops from 12 seconds to 0.2 seconds. PlanViz made this diagnosis trivial.
🧪 Hands‑on Lab: Analyze an Inefficient Query with PlanViz
We’ll use a deliberately bad query on ACDOCA that joins with an unindexed custom table.
Run PlanViz. You’ll see that HANA opted for a nested loop join because ZCUSTOM_SLOW is a small row‑store table, but it still performed a row‑store scan. The query might be slow if ACDOCA has millions of rows. To fix, we could convert ZCUSTOM_SLOW to column store or, better, push the filter to the join condition using an inner table function. PlanViz shows the exact operator responsible. This is how you build intuition.
🕵️ Expensive Statement Trace – Catching the Silent Killers in Production
You can’t manually PlanViz every query in a live S/4HANA system. That’s where the expensive statement trace comes in. It automatically records all SQL statements that exceed a threshold of execution time, memory, or CPU, and stores them in a system table. Later, you analyze the trace to find the worst offenders.
📝 Activating the Expensive Statement Trace
This configuration persists across restarts. Once enabled, any statement running longer than 1 second gets logged.
📊 Querying the Captured Statements
This gives you a hit list. Often you’ll find a Fiori app query that scans ACDOCA without a date filter because the app doesn’t enforce a mandatory parameter. Or a nightly batch job that loops over ACDOCA row by row (because someone used a cursor in SQLScript).
⚡ Real‑Life Scenario: The Phantom Report That Killed Month‑End
During a month‑end close, the system suddenly crawled. Expensive statement trace revealed a query from a custom Profit & Loss report that was executing a calculation view with a missing input parameter. The view was scanning the entire ACDOCA table (2.1 billion rows) for each of 12 periods, causing massive memory pressure. Once the team forced the date parameter, runtime dropped to 2 seconds per period. Without the trace, they would have spent days guessing.
🛑 Setting Memory Thresholds
You can also trace by memory consumption:
This catches statements that eat huge RAM without necessarily being long‑running. Combine with SQL plan cache to identify repetitive memory hogs.
🗂️ Partitioning – The Ultimate Speed Booster for Big Tables
Partitioning splits a large table into smaller, more manageable physical pieces. In HANA, partitioning is crucial for column‑store tables because it enables partition pruning: the engine reads only the partitions that contain relevant data, ignoring the rest. For ACDOCA, which can reach billions of rows, proper partitioning is not optional; it’s the difference between usable and frozen.
🔹 Types of Partitioning in HANA
- Range Partitioning: Splits rows based on a range of values in a column, typically date or numeric. Perfect for ACDOCA by fiscal year (RYEAR) or posting date (BUDAT). Queries with a WHERE clause on that column can eliminate entire partitions.
- Hash Partitioning: Distributes rows across partitions using a hash function on one or more columns. Excellent for evenly spreading data when you have high‑concurrency inserts and queries that always filter by the partition key, like company code.
- Round‑Robin: Distributes rows evenly regardless of content. No pruning benefit, but useful for load balancing inserts. Not recommended for analytical tables.
- Multi‑Level Partitioning: Combines range and hash. For example, range by year, then hash by company code inside each year. This is the enterprise gold standard for ACDOCA.
🧪 Designing an ACDOCA Partitioning Strategy
ACDOCA in S/4HANA comes with a default partitioning: range by fiscal year (RYEAR). However, many systems outgrow this. A better strategy: multi‑level partitioning with range on RYEAR and hash on RBUKRS (company code). This way, a query filtering on company code and a year range only hits a subset of partitions. Let’s implement it (in a sandbox!).
📊 Performance Impact: Before vs After
Using a test system with 500 million rows in ACDOCA, a typical year‑to‑date profit query ran for 28 seconds on the original single‑year range partitioning. After repartitioning to range (year) + hash (BUKRS), the same query completed in 1.2 seconds. The explain plan showed that before, HANA scanned the whole 500M rows; after, only 60M rows (one year partition × one hash subpartition). Partition pruning saved 88% of the data scan.
🔧 Partitioning during Table Creation (New Tables)
Always design partitioning based on query patterns. Interview the business: “Which columns do you always filter by?” That’s your partition key. Then hash on the second most common filter.
⚠️ Partitioning Pitfalls to Avoid
- Over‑partitioning: Too many partitions (1000+) cause metadata overhead and merge chaos. Keep it reasonable (10‑100 partitions).
- Non‑SARGable filters: If you use a function on the partition column (e.g.,
WHERE LEFT(BUKRS,2) = '10'), pruning fails. Always use direct comparisons or ranges. - Repartitioning cost: Altering a huge table’s partitioning can take hours and locks the table. Plan it during a maintenance window and test with a copy first.
🧩 The HANA Performance Tuning Workflow – A Step‑by‑Step Blueprint
Now that you have the tools, let’s define a repeatable methodology:
- Identify Slow Queries: Use expensive statement trace, HANA Cockpit, or user complaints to list candidate statements.
- Capture the Execution Plan (PlanViz): Run explain on the top candidate. Understand which operators dominate cost.
- Check Partitioning: Is partition pruning happening? If not, adjust the table’s partitioning or the query’s filter to enable pruning.
- Analyze Joins: Are there nested loop joins where hash joins should be? Check join cardinalities. Use
WITH HINTif needed (but sparingly). - Review the Data Model: Is the calculation view using the most efficient nodes? Could a table function with set‑based logic replace a procedure?
- Verify Indexes: In HANA, column‑store doesn’t need traditional indexes, but full‑text indexes or inverted indexes might be missing for special search patterns.
- Test and Compare: Apply one change at a time, measure runtime and PlanViz again, and document the improvement.
- Implement Permanently: Once proven, push the change to production via your Git‑based HDI pipeline (Day 4 knowledge).
⚙️ Additional Performance Knobs: Memory and Hints (Preview of Part 51)
While today focuses on query tuning, some performance issues stem from memory management. In Part 51 we’ll dive into heap allocation, OOM analysis, and how the column store memory works. But for now, know that a query might be slow because HANA’s memory is exhausted and it’s paging to disk (which we monitor via Cockpit). Also, you can sometimes force a specific join algorithm using hints:
But use hints as a last resort; the optimizer is usually right. Always tune the data model and partitioning first.
📌 Real‑World Wrap‑Up and Your Action Plan
Today you’ve added the most impactful skill in the HANA professional’s arsenal: performance tuning. With PlanViz, you can see the truth. With expensive statement trace, you find what’s really hurting your system. With partitioning, you can reshape data to match query patterns and slash execution times. Apply these on a real system, and you’ll quickly become the person everyone calls when the system is slow.
0 Comments
thanks for your comments!