Define what “slow” means and capture a baseline

Begin by quantifying the problem. “The report is slow” is not a diagnosis; “this statement averages 4.2 seconds against a 500-millisecond SLA, called 900 times an hour” is. Pin down the exact statement, the expected response time, the frequency, and whether the slowness is constant or intermittent. Intermittent slowness points toward concurrency, memory pressure, or plan instability, while constant slowness points toward the statement and the data model itself.

Separate compilation time from execution time from the outset. A statement that is slow only on its first execution is paying compilation cost; one that is slow on every execution has an execution problem. The SQL Plan Cache records both, so capture the current numbers as your reference point before touching anything.

Capture the surrounding conditions too, not just the timing. Note the time of day and concurrent load, the parameter values in play, the connected application and user, and whether the table involved had recently been loaded or modified. Slow queries are frequently contextual: the same statement that returns instantly against a freshly merged table can crawl against one with a bloated delta store, and a plan that is ideal for one set of bind values can be poor for another. Recording context now saves hours later, because it lets you reproduce the slow condition deliberately rather than waiting for it to recur in production.

-- Baseline a specific statement from the plan cache
SELECT STATEMENT_HASH, EXECUTION_COUNT,
       TOTAL_EXECUTION_TIME / EXECUTION_COUNT AS avg_exec_us,
       TOTAL_COMPILATION_TIME, AVG_EXECUTION_MEMORY_SIZE
FROM M_SQL_PLAN_CACHE
WHERE STATEMENT_STRING LIKE '%FROM SALES_FACT%';

Surface the expensive statements

If you do not yet know which statement is the problem, let HANA tell you. Two complementary sources exist. The SQL Plan Cache is always on and aggregates statistics across executions, making it ideal for finding the statements that consume the most cumulative time. The Expensive Statements Trace is opt-in and records individual executions that exceed a duration or memory threshold, making it ideal for catching the specific slow runs that users actually feel.

Enable the Expensive Statements Trace with a sensible threshold so it captures the painful executions without flooding the trace. Then read M_EXPENSIVE_STATEMENTS for the offenders, including their duration, memory consumption, and the application user behind them.

-- Turn on the Expensive Statements Trace (> 1s)
ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM')
  SET ('expensive_statement', 'enable') = 'true',
      ('expensive_statement', 'threshold_duration') = '1000000'
  WITH RECONFIGURE;

-- Review captured offenders
SELECT START_TIME, DURATION_MICROSEC, MEMORY_SIZE, DB_USER, STATEMENT_STRING
FROM M_EXPENSIVE_STATEMENTS
ORDER BY DURATION_MICROSEC DESC;

Mine the SQL Plan Cache for patterns

With the statement identified, the SQL Plan Cache becomes your richest source of aggregate evidence. It records execution count, total and average execution time, compilation count and time, the number of rows processed, cursor durations, and memory consumption per cached plan. Reading these columns together tells a story: a high compilation-to-execution ratio suggests plan churn or a lack of parameterization; a large gap between average and maximum execution time suggests parameter-dependent behavior or contention.

Pay attention to EXECUTION_COUNT alongside timing. The aim is to direct effort where the cumulative cost is greatest, and to distinguish a genuinely expensive plan from a cheap plan executed relentlessly. The SQL Plan Cache is documented among the SAP HANA system monitoring views.

Two further signals reward attention here. First, watch the preparation and cursor-duration columns: a statement whose total time is dominated by compilation rather than execution often benefits from parameterization or from addressing plan-cache eviction, not from rewriting the query logic. Second, note plans with a high number of executions but a low cache hit pattern, which can indicate that literals are being passed where parameters belong, forcing HANA to compile a fresh plan for each variation and inflating both compilation time and plan-cache pressure across the system.

-- Top consumers by cumulative execution time
SELECT TOP 20 STATEMENT_HASH, EXECUTION_COUNT,
       TOTAL_EXECUTION_TIME, TOTAL_COMPILATION_TIME,
       TOTAL_EXECUTION_TIME / EXECUTION_COUNT AS avg_us,
       TOTAL_RESULT_RECORD_COUNT
FROM M_SQL_PLAN_CACHE
WHERE EXECUTION_COUNT > 0
ORDER BY TOTAL_EXECUTION_TIME DESC;

Capture the execution plan with EXPLAIN PLAN

Now look at how HANA intends to execute the statement. EXPLAIN PLAN writes the compiled operator tree into a table you can query, showing the operators, the order of execution, the engines selected — Join Engine, OLAP Engine, Calculation Engine, or Row Engine — and the estimated row counts at each stage. The engine mix matters: a query that unexpectedly falls into the Row Engine, or that mixes engines and forces expensive intermediate materialization, often reveals the cause immediately.

Read the plan from the innermost operators outward, watching for full column scans on large tables, large estimated intermediate result sets, and join operations whose estimated cardinality looks implausible. A mismatch between estimated and actual rows is one of the most reliable indicators of a planning problem. The syntax is part of the SAP HANA SQL reference.

In SAP HANA the choice of execution engine is especially informative. The columnar engines — the OLAP Engine for aggregations over star schemas and the Join Engine for column-store joins — are where HANA is fastest, while heavy reliance on the Row Engine for large column-store data, or repeated handoffs between engines that force intermediate results to be materialized, frequently explains an unexpectedly slow plan. When the plan reveals such an engine mix, the remedy usually lies in the query formulation or the data model rather than in any single parameter.

-- Generate and read the compiled plan
EXPLAIN PLAN SET STATEMENT_NAME = 'slow_sales' FOR
  SELECT region, SUM(amount)
  FROM SALES_FACT WHERE fiscal_year = 2026
  GROUP BY region;

SELECT OPERATOR_NAME, OPERATOR_DETAILS, EXECUTION_ENGINE,
       TABLE_NAME, OUTPUT_SIZE
FROM EXPLAIN_PLAN_TABLE
WHERE STATEMENT_NAME = 'slow_sales';

Go deeper with PlanViz and the SQL Analyzer

Where EXPLAIN PLAN shows intent, the Plan Visualizer — PlanViz — shows what actually happened. It captures a single execution and presents the operator tree with real timing, the rows flowing between operators, the degree of parallelism, and the dominant operators that account for most of the wall-clock time. This is where you stop guessing and see precisely which operator is expensive: a particular join, a search on an unselective column, an aggregation, or a materialization step.

Open the slow statement in PlanViz through SAP HANA Studio, the SQL Analyzer tool, or the relevant performance area of SAP HANA cockpit, and follow the time. The operator consuming the largest share of execution time is your target; everything before this step has been narrowing the search, and PlanViz confirms the culprit. The SAP HANA Plan Visualizer documentation describes how to read the operator timeline.

Observe active statements, threads, and blocking

Some problems only appear under live load. If the statement is fast in isolation but slow in production, the constraint is likely concurrency rather than the plan. Inspect what is executing right now through M_ACTIVE_STATEMENTS, examine thread activity through the service thread samples to see where time is spent inside the engine, and check for lock waits through the blocked-transactions view. A statement waiting on a lock held by another transaction is not slow because of its plan; it is slow because it is waiting.

This runtime layer distinguishes a planning problem from an operational one. Long-running threads concentrated in a single method, a growing number of active statements, or a chain of blocked transactions each point to a different remedy, and confirming which pattern is present prevents you from optimizing a query that was never the real bottleneck.

The service thread samples are particularly valuable because they show, over time, the methods inside the engine where threads accumulate — whether in a particular join, a search, a lock wait, or network and result transfer. A statement that appears slow but whose threads are mostly waiting on a lock or on a network round trip needs a concurrency or application fix, not a query rewrite. Reading this layer before optimizing keeps the effort aimed at the genuine constraint rather than at a symptom.

-- What is running now, and what is blocked?
SELECT CONNECTION_ID, DURATION_MICROSEC, CPU_TIME_MICROSEC, STATEMENT_STRING
FROM M_ACTIVE_STATEMENTS
ORDER BY DURATION_MICROSEC DESC;

SELECT BLOCKED_CONNECTION_ID, LOCK_OWNER_CONNECTION_ID,
       LOCK_TYPE, WAITING_RECORD_ID
FROM M_BLOCKED_TRANSACTIONS;

Examine memory, the column store, and delta merge

Because HANA is an in-memory engine, query speed depends on whether the data the query needs is resident in memory and well organized. Under memory pressure, the engine unloads columns and must reload them on demand, turning a fast scan into a slow one; a statement can also hit its memory limit and spend time managing intermediate results. The column-store table view reveals memory consumption, total record counts, and — critically — the size of each table’s delta storage.

Delta merge is central to HANA performance. New rows land in a write-optimized delta store and are periodically merged into the read-optimized main store. When the delta grows too large because a merge is overdue, reads slow down because the engine must search both structures. Checking delta size and merge history frequently explains a query that has gradually degraded. Memory management and delta merge are covered in the SAP HANA administration documentation.

Memory pressure deserves a system-wide view as well as a per-table one. If the host is approaching its allocation limit, the engine becomes increasingly aggressive about unloading columns, and queries that were comfortably in-memory begin paying reload costs that appear, misleadingly, as query slowness. Correlate the per-table figures with overall host memory and with any recent growth in data volume or concurrent workload. When the evidence points to genuine capacity exhaustion rather than a single inefficient statement, the correct remedy is capacity planning and workload management, and treating it as a query-tuning problem will only produce frustration.

-- Memory, record counts, and delta size per table
SELECT SCHEMA_NAME, TABLE_NAME,
       MEMORY_SIZE_IN_TOTAL / 1048576 AS mem_mb,
       RECORD_COUNT, RAW_RECORD_COUNT_IN_DELTA AS delta_rows,
       LAST_MERGE_TIME
FROM M_CS_TABLES
ORDER BY RAW_RECORD_COUNT_IN_DELTA DESC;

Examine the data model, then fix and validate

With the hotspot identified, the remedy follows from the evidence. If a single large table dominates, partitioning by range or hash can enable partition pruning and parallel processing across partitions. If a join is the hotspot, the cause is often a data-model issue — a calculation view that prevents the optimizer from pushing filters down, an unselective join column, or cardinality the optimizer cannot estimate without better information. If the delta store is the cause, schedule or trigger a merge and review the auto-merge configuration. If memory unloads are the cause, the answer is capacity planning rather than query rewriting.

Whatever the fix, apply exactly one change and then re-measure against the Step 1 baseline using the SQL Plan Cache and a fresh PlanViz capture. A change that does not move the measured numbers is not a fix, however reasonable it seemed. Document the before-and-after, because the next engineer — possibly you in six months — will need to know what was tried and what worked.

Resist the temptation to apply several promising changes at once. Partitioning a table, forcing a delta merge, rewriting a calculation view, and adjusting a memory parameter in a single deployment may well make the query faster, but it leaves you unable to attribute the gain, unable to reverse a regression cleanly, and unable to teach the next person what mattered. The discipline of one change per measurement is slower for a single statement and dramatically faster across an estate, because it builds durable, transferable knowledge of how the workload truly behaves.

-- Example remediations, applied one at a time
-- Range-partition a large fact table for pruning
ALTER TABLE SALES_FACT PARTITION BY RANGE (fiscal_year)
  (PARTITION 2024 <= VALUES < 2025,
   PARTITION 2025 <= VALUES < 2026,
   PARTITION OTHERS);

-- Force an overdue delta merge, then re-baseline
MERGE DELTA OF SALES_FACT;