[[{“value”:”
You apply an upgrade over the weekend. Monday morning, someone reports that a critical reporting query is running much slower. You dig into the plan cache, compare before/after execution plans, find the optimizer chose a different join order, capture the old plan, pin it — and hours later, things are back to normal.
SAP HANA Cloud’s SQL Plan Advisor, combined with SQL Plan Stability, now supports a fully automated detect-test-apply cycle that handles this class of degraded performance without manual intervention. Here is how it works and how to configure it.
The Two Pieces You Need to Know
SQL Plan Stability — Your Insurance Policy
SQL Plan Stability lets you capture an Abstract SQL Plan (ASP) for any SELECT statement. An ASP is a compact, engine-independent representation of an execution plan’s key structural decisions: join order, join types, access paths, aggregation strategies. It is stored persistently and can be used later to regenerate that same physical plan, even after a software update or statistics refresh that would otherwise cause the optimizer to choose something different.
Key points:
– SAP HANA Cloud now automatically collects ASP for every distinct HEX plans and corresponding statistics.
– For non-HEX, only the statistics are collected for the corresponding statement hash
As ASPs are collected for every compiled plans with its corresponding statistics, we have a clearer understanding of what is a good plan versus a bad plan. This information is used it to protect against future degradation.
SQL Plan Advisor — The Automated Testing Engine
SQL Plan Advisor is the active component. It continuously monitors SQL execution, detects performance degradation by comparing the current execution plans statistics over the statistics stored in SQL Plan Stability, and now automatically runs A/B tests between the current plan and candidate alternatives to find and validate a better one.
The candidate plan types it evaluates are:
| Candidate Type | What It Is |
| NON_HEX | A plan generated by the non-HEX execution engine, tested when the HEX plan degrades |
| CAPTURED_ABSTRACT_SQL_PLAN | An ASP previously captured via SQL Plan Stability |
| PLAN_VARIANT |
An alternative plan from the plan cache, typically tied to a different parameter binding by caching multiple plans for a given statement hash ** NOT AUTOMATICALLY TESTED ** |
For parameterised queries, it is first determined if it can be covered by Plan Variant based on selectivity. If covered, the SQL Plan Advisor can test and collect statistics over the current plan and 50 with each alternative and checks for deviance before issuing a recommendation. As the testing maybe extensive, Plan Variant is currently scoped out for the automated testing.
The Fully Automated Flow
When configured for full automation, the pipeline looks like this:
Query executes
│
▼
HANA detects degradation (execution / CPU time compared to history)
│
▼
Statement auto-registered in SQL Plan Advisor
│
▼
Advisor tests current plan vs. candidates (NON_HEX, ASP)
over multiple live executions — no downtime, no test window
│
▼
Winner identified by empirical statistics
│
▼
Better plan auto-applied on the next execution
│
▼
Monitoring views updated — DBA can review at any time
The DBA is not in the critical path. You are informed after the fact, not paged during the incident.
Configuration
Step 1 — Enable SQL Plan Stability Capture for Critical Queries
Do this before your next update. In SAP HANA Cloud Central, navigate to SQL Plan Stability and enable capture for your most sensitive statements. For most instances, this should be already enabled.
Or use SQL directly:
— Enable capture for a specific statement hash
ALTER SYSTEM ALTER SQL PLAN CACHE ENTRY ‘<statement_hash>’
SET PLAN STABILITY CAPTURE PLAN = TRUE;
Captured ASPs land in `M_SQL_PLAN_CACHE_EXECUTION_PLAN_STATISTICS` and are referenced in the `CAPTURED_ABSTRACT_SQL_PLAN` column of `M_SQL_PLAN_ADVISOR`.
Step 2 — Configure SQL Plan Advisor Parameters
In SAP HANA Cloud Central, navigate to SQL Plan Advisor and enable Register Queries, and set Test Queries and Apply Advice to Automatically which should be the default configuration
Monitoring
You can monitor all of this graphically in SAP HANA Cloud Central → SQL Plan Advisor, where the UI shows registered queries, test progress, performance comparisons, and applied recommendations in one view.
How SQL Plan Stability and SQL Plan Advisor Work Together
The two features are complementary, not redundant:
– SQL Plan Stability is reactive and defensive: capture a known-good plan, apply it when something goes wrong. It requires you to have captured an ASP beforehand.
– SQL Plan Advisor is proactive and exploratory: it detects degradation automatically, tests multiple candidates (including your captured ASPs), and applies the empirically best one.
The combined strategy:
1. Before a QRC update: Capture ASPs for your critical statements via SQL Plan Stability which should be already captured.
2. After the update: If the optimizer picks a worse plan for any of those statements, SQL Plan Advisor detects the degradation, tests the captured ASP against the new plan, and automatically applies the better one. Or, if a non-HEX plan is now converted to a HEX plan but the performance have degraded then reverts the execution back to non-HEX.
3. Ongoing: Advisor continues monitoring for new degradation across the full workload, not just the statements you pre-selected.
You get both a safety net (captured ASPs) and an autonomous recovery mechanism (Advisor auto-apply).
What Gets Automated vs. What Still Needs DBA Judgement
Fully automated:
– Detection of performance degradation
– Registration of degraded statements as test candidates
– A/B testing based on historical plans statistics.
– Application of the most performant plan
Still requires DBA attention:
– Parameterized queries which can be covered by Plan Variant should be manually tested if Plan Variant is applicable.—
Conclusion
The automated testing was first applied with QRC 01/2026 version and we have been closely monitoring on a daily basis. Based on our telemetry data, 155 plans have been corrected back to a more performant plan based on historically captured ASPs. We have noticed up to 20 times performance increases after the recovery. Most of the reasons of degraded performance was due to statistics changes in the underly tables or plan changes for parameterized queries which couldn’t be covered by Plan Variant.
We are planning to improve the coverage of SQL Plan Advisor and improve the usability of our tooling for better manage SQL plans and improve user experiences.
References
– SQL Plan Advisor — SAP Help Portal
– Automating SQL Plan Management with SQL Plan Advisor — SAP Learning
– Leveraging SQL Plan Stability for Performance Optimization — SAP Learning
– Protect from Performance Regression with SQL Plan Stability — SAP Community Blog
– Using SQL Plan Advisor in SAP HANA Cloud Central — SAP Help Portal
“}]]
ALTER SYSTEM ALTER SQL PLAN CACHE ENTRY ‘<statement_hash>’
SET PLAN STABILITY CAPTURE PLAN = TRUE;Captured ASPs land in `M_SQL_PLAN_CACHE_EXECUTION_PLAN_STATISTICS` and are referenced in the `CAPTURED_ABSTRACT_SQL_PLAN` column of `M_SQL_PLAN_ADVISOR`.Step 2 — Configure SQL Plan Advisor ParametersIn SAP HANA Cloud Central, navigate to SQL Plan Advisor and enable Register Queries, and set Test Queries and Apply Advice to Automatically which should be the default configuration MonitoringYou can monitor all of this graphically in SAP HANA Cloud Central → SQL Plan Advisor, where the UI shows registered queries, test progress, performance comparisons, and applied recommendations in one view. How SQL Plan Stability and SQL Plan Advisor Work TogetherThe two features are complementary, not redundant:- SQL Plan Stability is reactive and defensive: capture a known-good plan, apply it when something goes wrong. It requires you to have captured an ASP beforehand.- SQL Plan Advisor is proactive and exploratory: it detects degradation automatically, tests multiple candidates (including your captured ASPs), and applies the empirically best one.The combined strategy:1. Before a QRC update: Capture ASPs for your critical statements via SQL Plan Stability which should be already captured.2. After the update: If the optimizer picks a worse plan for any of those statements, SQL Plan Advisor detects the degradation, tests the captured ASP against the new plan, and automatically applies the better one. Or, if a non-HEX plan is now converted to a HEX plan but the performance have degraded then reverts the execution back to non-HEX.3. Ongoing: Advisor continues monitoring for new degradation across the full workload, not just the statements you pre-selected.You get both a safety net (captured ASPs) and an autonomous recovery mechanism (Advisor auto-apply). What Gets Automated vs. What Still Needs DBA JudgementFully automated:- Detection of performance degradation- Registration of degraded statements as test candidates- A/B testing based on historical plans statistics. – Application of the most performant plan Still requires DBA attention:- Parameterized queries which can be covered by Plan Variant should be manually tested if Plan Variant is applicable.—ConclusionThe automated testing was first applied with QRC 01/2026 version and we have been closely monitoring on a daily basis. Based on our telemetry data, 155 plans have been corrected back to a more performant plan based on historically captured ASPs. We have noticed up to 20 times performance increases after the recovery. Most of the reasons of degraded performance was due to statistics changes in the underly tables or plan changes for parameterized queries which couldn’t be covered by Plan Variant.We are planning to improve the coverage of SQL Plan Advisor and improve the usability of our tooling for better manage SQL plans and improve user experiences.References- SQL Plan Advisor — SAP Help Portal- Automating SQL Plan Management with SQL Plan Advisor — SAP Learning- Leveraging SQL Plan Stability for Performance Optimization — SAP Learning- Protect from Performance Regression with SQL Plan Stability — SAP Community Blog- Using SQL Plan Advisor in SAP HANA Cloud Central — SAP Help Portal”}]] Read More Technology Blog Posts by SAP articles
#SAPCHANNEL