Post SAP S/4HANA Conversion SQL Optimization
Congratulation, you have converted your ERP to SAP S/4HANA. From the Preparation to the Realization phase, with the many hours of planning and the challenges of conversion downtime, the source system is finally on SAP S/4HANA. In the System Conversion slide below, Performance tuning is one of the follow up activities in which will help you utilize the true benefit of SAP HANA.
This blog will cover some of tools available to analyze the SQL performance related issues. I will give an example of a customer case in which by using HANA SQL scripts, our engineer team have found a workaround to an incident which helped a customer in tuning a long running expensive query.
In subsequent blogs, I will discuss the usage of ABAP tools such as ST05, SQLM and ST03 for troubleshooting CDS Views performance. As some of us are new to SAP HANA from the classical DBs, finding a solution may require running long traces due to the complexity of the statement. As there is no one size fits all when it comes to optimization, understand the concepts and the tools that are available is key.
Let’s start by looking at the basics, BASIS 101, if you will. The goal is to Identify -> Analyze -> Improve. The assumption here is that you already have activated and collected data from the following tools below.
Ensure that you have workload monitor setup. Please review SAP Note 2369736 if there are any missing workload histories. Some possible causes are:
- Job SAP_COLLECTOR_FOR_PERFMONITOR is not running in client 000, with user DDIC or a user with the same authorization.
- Time zone setting in client 000 is not same as Operating System’s time zone.
SQLM is an ABAP tool for detailed analysis of SQL statements and requests over a long period of time. It also a displays Number or executions, statistics on execution time and which business processes are affected by these statements. You can also activate SQL tracing for long running statements. For more information on how to set up SQL Monitor, please review this document online.
- Review long running statement
- Look at the Total Records and Total DB Time
- Activate SQL Tracing to investigate if necessary
SQL Trace and Analysis
|ST12||ABAP||Single transaction analysis|
|SQL trace||SAP HANA Studio, SQL, ABAP (DBACOCKPIT)||2031647
|Expensive statements trace||SAP HANA Studio, SQL, ABAP (DBACOCKPIT)||2180165||Expensive statements trace|
Source: Note 2000002 – FAQ: SAP HANA SQL Optimization. Here you will find example of where to identify potential gotchas and low hanging fruit to optimize your SQL.
The most expensive SQL statements running in the SQL cache which can be determined in the following ways:
- M_SQL_PLAN_CACHE / HOST_SQL_PLAN_CACHE
- SAP HANA Studio -> Administration -> Performance -> SQL Plan Cache
- ABAP: DBACOCKPIT -> Performance -> SQL Plan Cache
- SQL: “HANA_SQL_SQLCache” / SQL: “HANA_SQL_SQLCache_TopLists” (SAP Note 1969700)
- Solution Manager self service “SQL Statement Tuning” (SAP Note 1601951)
Another approach in troubleshooting SQL Statement is in SAP HANA itself.
SAP HANA Performance Analysis Example
For those who are new to SAP HANA, this SAP HANA Emergency Suitcase
Note contains all essential reference for SAP HANA related troubleshooting guide. I highly recommend bookmarking this and download the SQL scripts and mini checks from Note 1969700
The following is a summary of a customer case example from our SAP HANA Engineer team:
Martin Frauendorfer showed how he used SQL scripts first as a general approach for analysis rather than setting up performance traces which sometime can be time consuming and difficult to decode due to the complexity of the query itself. It’s often if you look at the current state of the database, the basics output, GC, CPU, Memory, and Threads, you will find clues to help guide to your performance issue.
Disclaimer: The workaround in this example is specific to one of the customer. Your code and environment may differ so please do your testing first as the results may be different.
Expensive Database Statement Customer Case
Background: Customer reported long runtime from one expensive database request. In this example, the query itself looks a complex, involving CDS and functions. The output is from executing the scripts provided in the Note 1969700.
Active Statement running
The average execution time is 100 seconds for 19 records. This is the delay the customer is currently experiencing.
Further investigation points to ZYXWVUNISPCREP is a CDS View
SQL: “HANA_Objects_ObjectDependencies_Hierarchy” which lists all the dependent objects
From the output of the SQL script, the CDS View has a long list of objects including functions, views, tables within functions, etc..and to find the bottleneck could be complex and time consuming to analyze. A simpler approach is needed!
- Reproduce the issue first and Run SQL: HANA_Global_CurrentStateReport to see what other areas where we can see potential abnormalities
Here are the findings:
CPU, Disk I/O, MVCC garbage collection are ok
Here we see a lot of activities from the statement hash. Could this be the cause???
- On the “Running” state, you see the same statement hash with ParallelLoopWithEqJob: this appears to be looping
- Check SAP Note 2114710 for this specific thread_method: ParallelLoopWithEqJob:
Parallel evaluation of HAVING clause or filter evaluation after fact table join in context of OLAP engine
Follow up, According to Note 2142945 above.
|Enable / disable cyclic join||In some scenarios it can be useful to disable cyclic joins for specific SQL statements, see SAP Note 2000002 (“High runtime of cyclic joins”) for more information. Additionally these hints can be a workaround for problems in the following situations:|
- Cyclic joins sometimes show up as unfavorable feature and deactivation provides benefits.
- We suggested customer to implement NO_CYCLIC_JOIN hint for this query to disable cyclic joins.
- Hints can be specified on different levels (e.g. application coding, SAP HANA statement hints, calculation view properties) described in SAP Note 2142945
Conclusion and Results
After the solution was provided by the development team, the customer reported a runtime improvement from more than 100 seconds to less than 2 seconds once cyclic joins were switched off via hint.
This case study example demonstrates that before running traces and trying to figure out the SQL code and dependencies itself, one can take a simpler approach in information gathering and identifying the problem on the HANA database layer.
Thank you Martin Frauendorfer in providing the example and SQL scripts.