3 Root Causes And Solutions
3.10 Application Performance Analysis
This section provides a high-level guide for analyzing the performance of SAP HANA-based applications.
When you have a SAP HANA-based application with unsatisfying performance you should start a systematic investigation considering the various layers that are involved. Here we focus on the database layer;
approaches for analyzing UI and application server performance are described in the respective documents.
3.10.1 SQL Trace Analysis
The first step in application performance analysis is to figure out if the database layer is causing performance problems for your application at all.
Context
You should analyze how many and which database calls are made and what their contribution to the overall application performance is. This should be done within the context of a given user interface step or
transaction.
Procedure
1. Start the tracing of database calls.
2. Run the application from its user interface or with any other driver.
Both, SAP HANA studio and SAP HANA Web-based Development Workbench provide two main tracing tools, namely SQL trace and Expensive statements trace.
A convenient way to narrow the trace analysis to the scope of a user interface step or transaction is to use the passport-based filtering of Expensive statements trace in the SAP HANA Web-based Development Workbench, which also offers aggregated statistics to quickly answer above questions.
Deep tracing (including complete execution plans) is provided by Plan Trace in SAP HANA studio.
3. Terminate the tracing and review aggregated and individual results.
4. As a result of this investigation you might see some indicators for bad application logic creating excessive load on the database such as:
○ Too many database calls (per transaction/UI step)
○ Many identical executions, for example repeated identical selects
○ Too many records returned (per execution or in total)
○ Too many columns or all columns of a row selected
○ Inefficient statement reuse, that is, statements that need to be optimized over and over again
○ One or more database calls with unexpected bad performance, so you should further investigate those calls
Related Information
Plan Trace [page 160]
Analyzing SQL Traces [page 141]
Analysis Tools in SAP HANA Web-based Developer Workbench [page 169]
3.10.2 Statement Measurement
Once you have determined which SQL statements are problematic you should first perform a sound
measurement in order to get reliable performance numbers and to make sure that indeed your statement(s) are causing the issues and not the current state of your SAP HANA system.
Procedure
1. Execute your statement(s) and measure their performance (in particular response time).
Both, SAP HANA studio and SAP HANA Web-based Development Workbench offer basic measurement of SQL statements. In addition, the SAP HANA Web-based Development Workbench implicitly supports observing the system state and also executing repeated measurements. In SAP HANA studio you have to use the administration perspective in order to examine the system state.
2. Check your SAP HANA system status for disturbing conditions, such as high load, high resource usage and so on
3. In case of disturbing conditions repeat your measurement from step 1.
4. Repeat your measurements until you get stable results without major variations (for example, 3 stable executions in a row).
Note
Higher initial response times could be an indicator of caches that are not properly filled. Depending on your business needs you can decide whether this is acceptable or not.
5. Once you have a stable result you may also acquire a detailed SAP HANA engine trace which will allow for a deeper analysis.
Results
As a result of this activity you have reliable data for your query performance, both for initial query execution performance (possibly cold execution) and stabilized execution performance (warm execution).
Related Information
Statement Performance Analysis [page 86]
3.10.3 Data Analysis
The easiest way to analyze a poorly performing SQL statement is to look at the data flow as this can be matched with the understanding of the business/application needs.
Procedure
1. Check the result size, that is the number of records and number of columns, returned by your SQL statement and compare it with the actual business needs.
As a result of this investigation you might restrict the result size by changing the application logic (for example, the number columns selected in the field list or by applying additional filters in the WHERE clause) with regards to its database call.
2. Check the usage of underlying tables, meaning the set of tables used, their size and the number of entries selected from those tables, and compare it with your understanding of the business needs.
As a result of this investigation you might identify tables that should not be involved at all and adapt your statement or the underlying database logic accordingly (for example, by checking joins and join types).
SAP HANA studio offers data flow analysis with the Tables Used view in the SAP HANA Plan Visualizer perspective.
3. Check the data flow of your statement, that is, the order in which tables are joined, how filters are applied and the size of intermediate results.
As a result of this investigation you may identify:
○ inefficient join orders (starting with table A and not with table B, when that is the much smaller result set)
○ unexpectedly missing filters (that is, intermediate selections which seem too broad).
You can then adapt your statement or underlying database logic as necessary.
SAP HANA studio offers data flow analysis with the Graphical Plan view in the SAP HANA Plan Visualizer perspective.
Related Information
Tables Used [page 158]
Analyzing SQL Execution with the Plan Visualizer [page 148]
3.10.4 Source Analysis
Before conducting a detailed technical analysis it is recommended to first analyze source artifacts (models, scripts, views) for possible performance-impacting aspects.
Context
The actual activities depend on the respective artifact (models, scripts, views).
Procedure
1. Graphical Calculation Models
SAP HANA studio offers dedicated performance debugging capabilities as part of the Modeler for graphical calculation models; furthermore the Plan Visualizer perspective provides deeper inspection of these models.
a. Check the effect of the attributes, parameters and filters used in the query on the execution time.
b. Check that the information is combined and aggregated in the correct way as required by the business scenario, for example:
○ Is the aggregation behavior consistent with the intended semantics?
○ Is the join cardinality correct for each join?
○ Are dynamic joins used?
○ Is the result always aggregated?
○ Has Join Optimization been enabled?
○ Do analytical privileges influence performance?
c. Check whether any performance-relevant execution hints are activated, for example:
○ Enforce execution via a specified database engine
○ Enforce upper bound for parallelization
d. Check whether modeling constructs are used that are known to be costly, for example:
○ Calculated join attributes
○ Complicated filter expressions
○ Mixed models that involve engine switches
e. Check whether intermediate steps produce reasonable results (in terms of size).
2. SQL Script
The Statement Statistics view (part of the Plan Visualizer) support analysis of SQLScript.
a. Check if your procedure fits with the given guidelines For example, see Developing Procedures
b. Analyze your script for most expensive steps / statements 3. Plain SQL
a. Check if your statement fits with the guidelines for SQL Query Design For example, see SQL Query Design
Results
As a result of this activity you either figured out and solved problems at the source level or you now have to continue with an even deeper technical analysis.
Related Information
Developing Procedures [page 117]
Technical Analysis [page 127]
Statement Statistics [page 152]
3.10.5 Technical Analysis
The deepest level of performance analysis addresses the technical details of a database statement execution.
Context
You should follow this in order to track down problematic performance symptoms from which you can derive possible root causes at higher levels.
There are a number of tools that can support you:
● SAP HANA studio offers the Plan Visualizer perspective which allows for deep technical analysis.
Dedicated views and filters support the analysis along numerous dimensions.
● Furthermore, there are multiple tools allowing for even deeper analysis such as Explain Plan and the numerous tracing tools.
Procedure
1. Inspect aggregated execution KPIs (execution time, resource consumption, distribution characteristics) in order to figure out in which aspect or dimension you might look for possible issues.
2. For any suspicious KPI, track down the KPI to the deepest possible level manifesting the symptom.
3. From there, try to correlate the symptom with the cause, in higher level statement elements.
4. You might restrict the scope of your analysis by focusing on the critical path, or on a specific time interval, system node, engine, or execution plan operator.
Related Information
Analyzing SQL Execution with the Plan Visualizer [page 148]
Analyzing SQL Execution with the Plan Explanation [page 147]