3 Root Causes And Solutions
3.9 Statement Performance Analysis
This section gives an overview of issues and solutions concerning SQL statement performance.
3.9.1 SQL Statement Optimization
This section provides an overview of tools, traces and SAP HANA studio areas that can be used to identify critical SQL statements.
SQL statements that are not executed efficiently can cause local and system-wide problems. The most critical are the following areas:
● A long runtime can result in delays of the business activities
● A high CPU consumption can lead to system-wide CPU bottlenecks
● High memory consumption can be responsible for out-of-memory situations and performance penalties due to unload of tables from memory
SQL statements consuming significant resources are called expensive SQL statements.
Identification of Critical SQL Statements
A key step in identifying the source of poor performance is to understand how much time is spent in the SAP HANA engine for query execution. By analyzing SQL statements and calculating their response times, you can better understand how the statements affect application and system performance.
Before you are able to analyze and optimize a SQL statement you have to identify the critical SQL statements.
We can distinguish between the following scenarios:
● SQL statements that have caused problems in the past
● SQL statements that are currently causing problems
3.9.2 SQL Statements Responsible for Past Problems
Sometimes it is not possible to identify a critical SQL statement during runtime. In this case you can use the following approaches to identify one or several SQL statements that can have contributed to the problem.
You can identify a SQL statement either by its SQL text (“statement string”) or by the related statement hash that is uniquely linked to an individual SQL text. The mapping of the statement hash to the actual SQL text is described later.
● To determine SQL statements with a particularly high runtime you can check for the top SQL statements in terms of TOTAL_EXECUTION_TIME in the SQL plan cache in SAP HANA Studio on the Performance SQL Plan Cache tab.
Figure 28: SQL Statements With High Runtimes
● To determine the top SQL statements that were executed during a dedicated time frame in the past, you can check the SQL plan cache history (HOST_SQL_PLAN_CACHE). You can use the SQL statement:
“HANA_SQL_SQLCache_History” available from SAP Note 1969700 – SQL Statement Collection for SAP HANA in order to check for top SQL statements during a specific time frame:
You have to specify a proper BEGIN_TIME / END_TIME interval and typically use ORDER_BY =
‘ELAPSED’, so that the SQL statements with the highest elapsed time from SAP HANA are returned.
Figure 29: SQL Statements With Highest Elapsed Time
● The thread sample history (tables M_SERVICE_THREAD_SAMPLES,
HOST_SERVICE_THREAD_SAMPLES), if available, can also be used to determine the top SQL statements.
You can use the SQL statement: “HANA_Threads_ThreadSamples_FilterAndAggregation” available from SAP Note 1969700 – SQL Statement Collection for SAP HANA in order to check.
You have to specify a proper BEGIN_TIME / END_TIME interval and use AGGREGATE_BY =
‘STATEMENT_HASH’ to identify the top SQL statements during the time frame.
Figure 30: SQL Example Output
In this case the SQL statement with hash 51f62795010e922370bf897325148783 is executed most often and so the analysis should be started with it. Often you need to have a look at some more SQL statements, for example the statements related to the next statement hashes fc7de6d7b8942251ee52a5d4e0af728f and 1f8299f6cb5099095ea71882f84e2cd4.
● In cases where the M_SERVICE_THREAD_SAMPLES / HOST_SERVICE_THREAD_SAMPLES information is not usable you can use the thrloop.sh script to regularly collect thread samples as described in SAP Note 1989031 – Scheduling of Shell script “thrloop.sh”
● In case of an out-of-memory (OOM) situation you can determine potentially responsible SQL statements by analyzing the OOM dump file(s) as described in SAP Note1984422 – Analysis of HANA Out-of-memory (OOM) Dumps.
● SAP HANA Alert 39 (“Long running statements”) reports long running SQL statements and records them in the table _SYS_STATISTICS.HOST_LONG_RUNNING_STATEMENTS. Check the contents of this table to determine details of the SQL statements that caused the alert.
Related Information
SAP Note 1969700 SAP Note 1989031 SAP Note 1984422
3.9.3 SQL Statements Responsible for Current Problems
If problems like high memory consumption, high CPU consumption or a high duration of individual database requests are currently happening, you can determine the active SQL statements with the help of SAP HANA studio.
Check for the currently running SQL statements in SAP HANA studio on the Performance Threads tab.
Figure 31: Running SQL Statements in Threads Detail
3.9.4 SQL Statements Reported by Traces
Additional traces can be activated to help identify critical SQL statements.
The following standard traces exist to identify critical SQL statements. Activating traces in addition to the normally available performance information can be useful for the following reasons:
● Capturing of more detailed information
● Determination of SQL statements that are not obvious on a global level, but important for business critical applications.
The following traces are available to determine SQL statements:
● SQL trace
○ Capturing of performance data for all SQL statements
○ Filter conditions can be used to limit the trace activities
● Expensive statements trace
○ Captures all SQL statements with a runtime beyond a defined threshold Further details can be found in Tools and Tracing.
Related Information
Tools and Tracing [page 134]
3.9.5 Analysis of Critical SQL Statements
When you have identified the SQL text and the related statement hash based on the tools and traces, you can collect more information about the SQL statement in order to identify the root cause and optimize the SQL statement. The available analysis approaches are described here.
From a technical perspective, analyzing query plans allows you to identify long running steps, understand how much data is processed by the operators, and see whether data is processed in parallel. However, if you understand the idea and purpose behind the query, you can also analyze query plans from a logical perspective and consider the following questions to gain the insight you need:
● Does SAP HANA read data from multiple tables when only one is required?
● Does SAP HANA read all records when only one is required?
● Does SAP HANA start with one table even though another table has a much smaller result set?
3.9.6 SQL Plan Cache Analysis
The SAP HANA SQL Plan Cache can be evaluated in detail for a particular statement hash.
You can use the SQL statement: “HANA_SQL_StatementHash_KeyFigures” available in SAP Note 1969700 – SQL Statement Collection for SAP HANA in order to check for the SQL Plan Cache details of a specific SQL statement (the related STATEMENT_HASH has to be maintained as input parameter).
Figure 32: SQL PLAN Cache Example Output
Alternatively you can check the SAP HANA view M_SQL_PLAN_CACHE or the Performance SQL Plan Cache tab in SAP HANA studio.
The historic execution details for a particular SQL statement can be determined with the SQL statement:
“HANA_SQL_StatementHash_SQLCache_History” included with SAP Note 1969700. Also here the appropriate STATEMENT_HASH has to be specified as input parameter.
Figure 33: Statement Hash Example output:
Based on the results of this evaluation you can distinguish the following situations:
● If the value for Executions is unexpectedly high, further analysis should be done on the application side in order to check if it is possible to reduce the number of executions.
● If the value for Records is unexpectedly high, further analysis should be done on the application side in order to check if it is possible to reduce the number of selected records.
● If the value for Cursor duration is very high and at the same time significantly higher than the value for Execution time, you have to check which processing steps are executed on the application side between the individual fetches. A high value for Cursor duration can negatively impact the database in general because open changes may impact the MVCC mechanism.
● If the value for Preparation time is responsible for a significant part of the Execution time value you have to focus on optimizing the parsing (for example, sufficient SQL plan cache size, reuse of already parsed SQL statements).
● If Execution time is much higher than expected (that can be based on the statement complexity and the number of processed rows), the SQL statement has to be checked more in detail on technical layer to understand the reasons for the high runtime. See section “Query Plan Analysis” for more information.
Related Information
SAP Note 1969700
Query Plan Analysis [page 147]
Example: Reading the SQL Plan Cache [page 92]
3.9.7 Example: Reading the SQL Plan Cache
These examples aim to show you how to gain useful insights by analyzing the SQL plan cache.
Execution in a Single-Host System
This example aims to show you how to interpret information about execution time. The following table is sorted by TOTAL_EXECUTION_TIME.
Table 9:
USER_
NAME
STATMENT_ STRING TOTAL_ EXECUTION_
TIME
AVG_ EXECUTION_
TIME
EXECUTION_ COUNT
SYSTEM SELECT "REQUEST" ,
"DATAPAKID" ,
"PARTNO" , "RECORD" ,
"CALDAY" , ...
774,367,833 181,266 4,272
SYSTEM SELECT * FROM "/BIC/
AZDSTGODO40" WHERE
"SID" = ?
726,672,877 60,556,073 12
SYSTEM SELECT "JOBNAME" ,
"JOBCOUNT" ,
"JOBGROUP" ,
"INTREPORT" ,
"STEPCOUNT" ...
473,620,452 22,987 20,604
– <Further 6832 records> – – –
You could read these top 3 results as follows:
● Statement 1 takes the longest time overall but it is also executed frequently.
● Statement 2 is not executed very frequently but has the second highest total execution time. Why is this simple SQL taking so long? Does it have problems processing?
● The execution times for statement 3 are fine for one-off execution, but it runs too frequently, over 20,000 times. Why? Is there a problem in application code?
Sorting by AVG_EXECUTION_TIME or EXECUTION_COUNT provides a different angle on your analysis.
The following example aims to show you how to interpret information about locking situations. The information in columns TOTAL_LOCK_WAIT_COUNT and TOTAL_LOCK_WAIT_DURATION lets us know which statement is waiting for others and how much time it takes.
Table 10:
USER STATEMENT_STRING TOTAL_LOCK_
WAIT_COUNT
TOTAL_LOCK_
WAIT_DURATION
TOTAL_ EXECU
TION_TIME
SYSTEM SELECT
"FROMNUMBER","TONUMBER"
,"NRLEVEL" FROM
"NRIV" ... FOR UPDATE
11,549,961 210,142,307,207 676,473
SYSTEM UPDATE "NRIV" SET
"NRLEVEL" = ? WHERE
"CLIENT" = '000' ...
AND "TOYEAR" = '0000'
0 0 3,706,184
SYSTEM SELECT "DIMID" FROM
"/BIC/DZDSTGCUBE4"
WHERE "/B49/S_VERSION"
= ?
0 0 460,991
Here, it is clear that the first statement is waiting almost all the time. Known as pessimistic/optimistic locking, the SELECT...FOR UPDATE code locks the resulting columns and may be replaced by a non-locking variant, which can result in poor performance. If the application is critical, it may be necessary to revise the
SELECT...FOR UPDATE code for better resource utilization and performance.
Execution in a Distributed System
In distributed SAP HANA systems, tables and table partitions are located on multiple hosts. The execution of requests received from database clients may potentially have to be executed on multiple hosts, depending on where the requested data is located. The following example illustrates statement routing and how, if it is not enabled, requests from the database client are executed on the contacted index server (in this case the master index server) and the required data is fetched from the index server on the relevant host(s). However, if statement routing is enabled, after initial query compilation, request execution is routed directly to the host on which the required data is located.
Figure 34: Distributed Execution with Statement Routing Off and On
Execution times should be better with statement routing enabled. You can use the SQL plan cache to compare the execution statistics of statements with statement routing enabled and disabled and thus confirm the effect.
Statement routing is controlled by the client_distribution_mode parameter in the indexserver.ini file. It is enabled by default (value=statement).
The following SQL plan cache examples show the execution times of sample statements based on the scenario illustrated above.
Note
The column IS_DISTRIBUTED_EXECUTION indicates whether or not statement execution takes place on more than one host.
The TOTAL_EXECUTION_TIME for a statement is the sum of execution times on all hosts, therefore:
Table 11:
Statement Request Path Total Execution Time
UPSERT "RSBMONMESS_DTP"
( "MSGNO", "MSGTY", "MSGID", ...
seltera12 = execution time on seltera12
SELECT * FROM "/BI0/SIOBJNM"
WHERE "IOBJNM" = ?
seltera12 > selbld13 = execution time on seltera12 + execu
tion time on selbld13 SELECT * FROM "/B49/SCUSTOMER"
WHERE "/B49/S_CUSTOMER" = ?
seltera12 > seltera13 > selbld16 = execution time on seltera12 + execu
tion time on selbld16
Table 12:
Statement Request Path Total Execution Time
UPSERT "RSBMONMESS_DTP"
( "MSGNO", "MSGTY", "MSGID", ...
seltera12 = execution time on seltera12
SELECT * FROM "/BI0/SIOBJNM"
WHERE "IOBJNM" = ?
selbld13 = execution time on selbld13
SELECT * FROM "/B49/SCUSTOMER"
WHERE "/B49/S_CUSTOMER" = ?
selbld16 = execution time on selbld16
3.9.8 Detailed Statement Analysis
When you have identified a critical SQL statement and identified its overall key figures from the SQL plan cache analysis you can have a closer look at the actual runtime behavior of the SQL statement.
The following tools can be used for a more detailed analysis:
● Plan Explanation
○ Creation of an execution plan
● Plan Visualizer
○ Detailed graphical execution plan
○ Temporal breakdown (timeline) available
● QO Trace
○ Query optimizer trace
○ Advanced tool that can be useful to understand the decisions of the query optimizer
○ Particularly helpful to understand column searches
● JE Trace
○ Join evaluation trace
○ Advanced tool to analyze table join operations
● Performance trace
○ Low level recording of key performance indicators for individual SQL statement processing steps
○ Advanced tool that should only be used in collaboration with SAP support
● Kernel profiler
○ Sample based profiling of SAP HANA process activities
○ Advanced tool that should only be used in collaboration with SAP support All these tools are described in Tools and Tracing.
Related Information
Tools and Tracing [page 134]
3.9.9 Optimization of Critical SQL Statements
You can improve the general performance of the SAP HANA database by implementing various best practices, design principles, available features, and add-ons.
To enhance the performance of the SAP HANA database, we recommend you do the following:
● Optimize outlier queries
Queries that sometimes take much longer than expected can be caused by query-external factors (for example, resource bottlenecks) that have to be determined and eliminated.
● Check data manipulation commands (DML)
DML operations like INSERT, UPDATE and DELETE can be impacted by lock waits.
● Improve SQL query design
You can significantly improve the performance of your SQL queries by knowing how the SAP HANA database and SAP HANA engines process queries and adapting your queries accordingly.
● Create indexes for any non-primary key columns that are often queried.
SAP HANA automatically creates indexes for primary key columns; however, if you need indexes for non- primary key columns, you must create them manually.
● Create virtual star schemas on SAP HANA data by joining attribute views to tables that contain measures within the definition of an analytic view
By using analytic views, SAP HANA can automatically recognize star queries and enable the performance benefits of using star schemas, such as reducing dependencies for query processing.
● Develop procedures to embed data-intensive application logic into the database.
With procedures, no large data transfers to the application are required and you can use performance- enhancing features such as parallel execution. Procedures are used when other modeling objects, such as analytic or attribute views, are not sufficient.
○ If you use SQLscript to create procedures, follow the best practices for using SQLScript.
○ For statistical computing, create procedures using the open source language R.
● Download and install the available application function libraries, such as Predictive Analysis Library (PAL) and Business Function Library (BFL).
Application functions are like database procedures written in C++ and called from outside to perform data intensive and complex operations.
● Scale SAP HANA to improve performance.
SAP HANA's performance is derived from its efficient, parallelized approach. The more computation cores your SAP HANA server has, the better overall system performance is.
Note
With SAP HANA, you do not need to perform any tuning to achieve high performance. In general, the SAP HANA default settings should be sufficient in almost any application scenario. Any modifications to the predefined system parameters should only be done after receiving explicit instruction from SAP Support.
3.9.10 Outlier Queries
Outlier queries are database statements that take much more time than usual and expected. This usually happens because extra work has to be performed during execution.
Besides heavy load on the machine by non-SAP HANA processes (which should not be the case on production systems) SAP HANA itself can be under heavy load. Reasons include:
● Many users are connected and issue a large amount of statements
● Extraordinary expensive statements are executed
● Background tasks are running
Use the Load Monitor to determine the number of statements issued and the indexserver CPU usage while the slow statement execution was perceived ( See the figure Load Monitor in SAP HANA Studio, the lower line (red) is the CPU consumption in percent (%), the upper line (orange) is the SQL throughput / s):
Figure 35: Load Monitor in SAP HANA Studio
You can see that during the period in the red rectangle both CPU consumption and SQL throughput
decreased. During that time frame you would look for something that consumed a lot of resources or blocked the statements (locking); just after 15:35 you see that the CPU consumption increases while the SQL
throughput decreases. Here, a possible case would be a change in usage: instead of many small, fast SQL statements the workload changed to a few "heavy" (complicated calculation requiring many CPU cycles) SQL statements.
If there was a high statement load in the same period when you experienced the slow execution the root cause is likely a lack of resources. To resolve the situation consider restricting the number of users on SAP HANA, upgrading the hardware, or get in touch with SAP Support if scalability can be improved in this case.
If you did not experience a high statement load during the time frame of the problem, check for background activities:
● Delta Merges: Use Load Monitor Column Store Merge Requests and the monitoring view M_DELTA_MERGE_STATISTICS to check if delta merges happened. In that case try to improve the delta merge strategy to prevent merges happening in phases where users are disturbed (see the SAP HANA Administration Guide for details).
● Column Unloads: See Load Monitor Column Store Column Unloads and the Monitoring View M_CS_UNLOADS to look for signs of column unloads. If a column used in the problematic statement had to be loaded before execution, the execution itself will take significantly longer.
● Savepoints: Savepoints consume resources and write-lock the database during their critical phase. Check M_SAVEPOINTS and look for savepoints during the time frame of the problem. If a savepoint slowed down your execution, the chance of having the same problem again is very low. If it happens often, please contact SAP Support.
Related Information
SAP HANA Administration Guide M_DELTA_MERGE_STATISTICS M_CS_UNLOADS
M_SAVEPOINTS
3.9.11 Data Manipulation Language (DML) Statements
Data Manipulation Language (DML) statements are often slowed down by lock-wait situations.
Data Manipulation Language (DML) statements are often slowed down by lock-wait situations.