SAP HANA capture and replay
Testing application workload can be a huge effort for users, developers and consultants alike. Also, things do not get easier on a large scale, especially for moving from one revision or SPS of SAP HANA to another.
Initially released with SAP HANA1 SPS12, SAP HANA capture and replay offers semi-automated support for integrated testing in the context of SAP HANA., the goal being to simplify the manual effort needed for creating tests and performing a more accurate replays than what is possible with other approaches.
The process is simple:
- Generate workload on the source database and record it using the new tool (1). For initializing a consistent test system, SAP recommends to use a full database backup of the source system.
- Use the tool to pre-process the workload and prepare it for replay on your desired SAP HANA test system (2).
- Trigger the replay (3) and use the replay report to analyze query runtime or number of rows in result sets between the capture and the replay (4).
The use cases are diverse:
- Evaluate potential regressions and improvements across SAP HANA revisions, O/S software updates, firmware changes, hardware changes, etc.
- Evaluate impact of changed information model implementations, system landscape setups, table distributions, index changes, partition changes, etc.
There are several new features available with SAP HANA2 SPS00:
- Option for fully transaction-consistent replay
- Option for result-based comparison
- New capture configuration features
- Replay to replay comparison
- New load graph visualization
- New XSA-based application in SAP HANA Cockpit
SAP HANA workload analyzer
Analyzing performance issues in SAP HANA can be a very complex and difficult task. Usually, several layers of analysis are required in order for the end user to find the correct monitoring view or develop custom queries to deliver the needed information.
Also released in SAP HANA1 SPS12, SAP HANA workload analyzer offers deeper insights into current system workload by analyzing thread samples. These samples are taken continuously and offer a real-time look at what is going on in the customer’s system. This part of the application is called the sampling-based workload analyzer.
With SAP HANA2 SPS00 the instrumentation-based workload analyzer has been released. This part of the application focuses on analyzing the engine instrumentation of captured workloads instead of evaluating thread samples in a running system.
The tools are integrated into SAP HANA Cockpit as web-based UIs. For better analysis, several flexible, chart-based analysis options are included for analyzing current system workload, as well as timeline-based analysis option on application and statement hierarchy levels for both real-time and capture analysis.
The tools offer means for drill-down evaluation of possible root causes of performance issues either by analyzing real-time or historical thread samples or engine instrumentation of captured data. They allow administrators to view the current state and health of a system at a glance or take a closer look into workload that has been captured. Both support multi-dimensional performance analysis to evaluate existing dominant bottlenecks.
There are several new features available with SAP HANA2 SPS00:
- Analysis based on source code instrumentation, provides details on individual processing steps
- Provides analysis based on steps executed, from application to DB or within DB itself, based on application and statement level hierarchy
- Enables user to evaluate anomalies in the timeline and match potentially problematic statements
- Possibility to leverage captured workload for analysis
SAP HANA SQL analyzer
When analyzing performance on a system level is not enough, for example using the aforementioned SAP HANA workload analyzer, users are often forced to look into the actual query details to identify bottlenecks or issues. These steps all have been part of typical performance analysis in SAP HANA in the past and continue to do so today. With query-level analysis in most cases being the lowest level of depth in the process, it is also the most complex one. It requires deep knowledge into SAP HANA and plenty of experience with queries and data models.
Previously only available as the so-called “PlanViz” in SAP HANA Studio, SAP now offers the SAP HANA SQL analyzer as part of SAP HANA2 SPS00. The tools are both very similar in regard to their usecase: analyzing query-level performance KPIs. SAP HANA SQL analyzer is the first XSA-based tool from SAP for this purpose.
For the initial release, SAP HANA SQL analyzer only contains a subset of the features available in its SAP HANA Studio counterpart. The following features are available in SAP HANA2 SPS00:
- Basic overview
- New overview can be shown for executed SQL statements, accessible from expensive statements or from plan trace view
- Shows overall KPIs regarding runtime, dominant operators, tables used, etc.
- Similar to PlanViz overview in SAP HANA Studio
- Operator List view
- New operator list view shows operators of SQL statement execution
- Includes details on execution time of operators as well as accessed objects and output rows
- Useful to figure out expensive operators and what objects they handled
- Tables Used view
- New tables used view shows tables accessed during SQL statement execution
- Includes details on location of table, partition (if applicable), number of accesses and entries processed
- Useful to figure out filter pushdown, tables accesses as well as physical information of tables in one overview
- Statement Statistics view
- New statement statistics view shows information about SQL statements involved during procedure execution
- Includes details on execution times, statement order and more
- Useful to figure out which part of the procedure has taken up the most time during execution and could be a potential bottleneck