Performance tracing, SQL and HANA analysis — Concepts and mental models

Training simulation: All sample identifiers, logs, requests, transactions, values and cases are fictional. Tools and permissions vary by SAP release; perform any changes only in approved DEV/QA environments.

Performance tracing, SQL and HANA analysis — Concepts and mental models

Outcome: Prove the bottleneck using traces before proposing code or index changes.

SAP environment: ABAP Open SQL and CDS/HANA landscapes; SQL Monitor, SAT/ST05 and HANA plan access require authorized trace scope.

Mental models

1. Understand the boundary

Performance debugging begins with a repeatable baseline: same business input, same user, representative data volume and warm/cold cache condition. Average user impression is not a trace. Decompose total elapsed time into application, database, RFC and rendering costs.

2. How this investigation works

Use ST05 to observe SQL statements and executions; SAT for ABAP runtime call costs; SQL Monitor for workload frequency where available. A SELECT inside a loop can multiply round trips. Large joins, incorrectly defined CDS association cardinality and missing predicates can also expand result sets.

3. Evidence-based decision making

A faster query is only a correct fix if authorization filtering, currency precision, client isolation and business semantics remain unchanged. Compare key count, row set and aggregates as well as timing.

Three common false diagnoses

• The most expensive individual SQL statement is not always the biggest total cost; frequency matters.
• A CDS association can duplicate or omit records when cardinality assumptions are wrong.
• A cache-warmed single run is not defensible before-and-after performance evidence.

Worked synthetic evidence

Synthetic ST05 extract:SELECT VBAK ... executions=1, duration=12msSELECT VBAP WHERE VBELN=? ... executions=1500, total=6600msApplication runtime=7200ms, DB=6900msOrder count=1500; item rows expected=7800 

Diagnostic sequence

1. Measure baseline duration and SQL workload
2. Inspect DB trace, call hierarchy and access plan
3. Verify correctness and response-time improvement

What to inspect

ST05 · SAT · ST12 (where available) · SQL Monitor · Plan analysis

Your understanding check

Question: A report runs 1500 identical item queries inside a loop. What is a likely first optimization hypothesis?

A. Replace with set-oriented retrieval while confirming semantics.
B. Add WAIT UP TO 1 SECONDS to the loop.
C. Disable user authorization filtering to speed it up.

Correct: A. An N+1 access pattern usually deserves controlled bulk-query evaluation.

Safety boundary: Limit performance traces to approved users/time windows. Never disable authorization, SQL client filters or productive indexes without DBA/change review.

Synthetic support case: An ABAP report is slow or data appears incorrect after query optimization.

The synthetic trace attributes 6600 of 6900 ms database time to an item SELECT executed once per header row. The repeated round trip is the primary performance bottleneck.

• The most expensive individual SQL statement is not always the biggest total cost; frequency matters.
• A CDS association can duplicate or omit records when cardinality assumptions are wrong.
• A cache-warmed single run is not defensible before-and-after performance evidence.

Move the repeated item lookup into a correctly filtered bulk read or appropriate CDS query; validate authorization and data equivalence before measuring the improved runtime.

• Positive test: Same 1500 headers and 7800 item rows returned with identical business totals.
• Negative test: A restricted-role user sees only permitted orders; the optimization doesn't leak client data.
• Cross-process regression: Repeated runs demonstrate reduced cumulative DB time without excessive memory use.

Limit performance traces to approved users/time windows. Never disable authorization, SQL client filters or productive indexes without DBA/change review.

Performance debugging is about measurement: SQL trace (ST05), runtime analysis (SAT), identifying expensive statements, N+1 loops, missing indexes and large result sets — and proving improvement with numbers.