## 2.4.1.1 HANA System-wide Performance Analysis
This topic describes solutions for both current and former system performance problems.
## Analysis of Current Performance Problems
Before taking any other action while the problem is present it is of utmost importance to capture a few HANA runtime environment (RTE) dump files and a Kernel Profiler Trace to collect information about the database's internal processes. Proceed as follows:
## Generating a Runtime Dump
Capture a number (3-5) of RTE dumps. There are several options for recording runtime dumps as described in the KBA 1813020 - How to generate a runtime dump on SAP HANA .
If the problem occurs randomly and you cannot predict when it will happen you can automate the collection of the runtime dumps with the SAP HANASitter tool which can be used, for example, to trigger methods such as the creation of traces and dumps when specific conditions are met. Refer to KBA 2399979 - How-To: Configuring automatic SAP HANA Data Collection with SAP HANASitter .
## Kernel Profiler Trace
Create a Kernel Profiler Trace, either:
- via the SQL console (see ALTER SYSTEM START KERNEL PROFILER statement)
- in hdbcons as described in KBA 1804811 SAP HANA Database: Kernel Profiler Trace .
## Analysis
Once the RTE Dumps are ready use an automated analysis tool such as the HANA Dump Analyzer to spot obvious problems. Refer to SAP Note 2498739 - How-To: Analyzing Runtime Dumps with SAP HANA Dump Analyzer . Most known problem situations are recognizable by the tool and you should then be able to take the first mitigation steps. Using the 'Auto Analyzer' option in the HANA Dump Analyzer the following summaries might be shown in the result:
- High Workload
- High CPU
- Waitgraph detected
Each of the identified issues require specific steps to perform an in-depth analysis and gain further insight on what potential culprits are.
## High workload / High CPU
This indicates that most of the active / running threads are in ownership of one specific application workload; if the HANA instance is configured according to official SAP guidance this should not happen under normal circumstances. It is important to understand which application user is responsible for the workload. If the situation is disruptive, ask the user to refrain form any further actions until the exact root cause is identified to avoid worsening the situation.
As a second step, check whether HANA Workload Management is configured according to SAP recommendations. Refer to KBA 2222250 - FAQ: HANA Workload Management . Pay particular attention to the appropriate configuration of the following workload related parameters:
- default_statement_concurrency_limit
- max_concurrency*
- max_concurrency_hint*
- num_cores*
(*not necessarily required as of HANA SPS3) Refer to KBA 2600030 - Parameter Recommendations in SAP HANA Environments .
## Waitgraph detected
This situation indicates that threads are not able to efficiently acquire locks on objects as they are already held by other threads (see Thread investigation below). The waitgraph is a directed graph depicting which threads are waiting for which locks and in turn which threads are holding the locks to be acquired. It is a good practice here to investigate what exactly the lock-holding threads (not the lock acquiring threads) are busy with. If no insights can be gained during this analysis and the problem requires an immediate mitigation, consider canceling the session responsible for the lock-holding threads by:
1. Identifying the corresponding connection ID of the problematic thread by examining the runtime dump; search the file for the thread ID identified, for example:
2. Cancel the session with:
```
1348967027[thr=<thread_id>]: SqlExecutor, TID: 34, UTID: 364438059, CID: <conn_id>,….
```
```
ALTER SYSTEM CANCEL SESSION '<conn_id>';
```
## Threads
A next step is to check what threads are currently running on the database while the performance issue exists. Y ou can check this in SAP HANA cockpit from the Threads card of the System Overview or from the Performance tab in SAP HANA Studio which is illustrated here:
Threads Overview
Refer to KBA 2114710 - FAQ: SAP HANA Threads and Thread Samples for tips on how to identify and solve problems with running and blocked threads. The information given for the following frequently-asked questions, for example, will be helpful:
- What kind of information is available for the SAP HANA threads?
- How can I interpret the thread state?
- What are the main thread types, methods and details?
## HANA Database Parameters
Check that the database parameters are configured as recommended. You can do this by running the script HANA_Configuration_Parameters_1.00.90+ , or HANA_Configuration_Parameters_Values_1.00.90+_MDC attached to the KBA 1969700 SQL Statement Collection for SAP HANA . The script can be executed in the system database as well as at the tenant database level. Recommended parameter values are discussed in more detail in the KBA 2600030 Parameter Recommendations in SAP HANA Environments .
Example Output of the HANA_Configuration_Parameters_1.00.90+ Script
## Analysis of Former Performance Problems
You can also analyze system-wide performance problems that occurred in the past using the methods described here.
## Thread Sample Analysis
When the issue is no longer present, a HANA Thread Sample analysis can be conducted to identify potential culprits. Proceed as follows using the scripts in the SQL Statement Collection for SAP HANA (attached to SAP Note 1969700 - see link in Further Information below):
1. Define a problematic time-period: be as accurate as possible here to avoid distortion of the statistical data by workloads not directly related. Use scripts TimeFrameReport (HANA_Global_TimeFrameReport_2.00.043+.txt) and LoadHistory (HANA_LoadHistory_Services_2.00.030+.txt) to do the following:
- Evaluate CPU / Memory / Disk
- Check Running vs. Waiting Threads
- Check on MVCC Versions
- Check Blocked Transactions / Locking Issues
2. Use the above information to identify the most active application workload for a defined time-period using the script HANA_Threads_ThreadSamples_FilterAndAggregation_*.
If you had a high CPU utilization in the past, use the script HANA_LoadHistory_Services_2.00.*.txt (or HANA_LoadHistory_Services_1.00.120+.txt for HANA 1) to find a time-period where the CPU was high. If a problematic time-period is identified run the HANA_Threads_ThreadSamples_FilterAndAggregation_* script
for this specific period. For example, if you have identified the period 10:00-11:00 a.m. on the 25th April, go to the modification section of the script and enter the following values:
```
'2021/04/07 10:00:00' BEGIN_TIME, '2021/04/07 11:00:00' END_TIME,
```
If the issue lies further in the past (older than few hours), you have to switch the DATA_SOURCE to 'HISTORY':
```
'HISTORY' DATA_SOURCE,
```
You can use the AGGREGATE_BY filter, for example, to see which threads have been consuming most of the CPU time:
```
'HASH, THREAD_STATE, THREAD_DETAIL, THREAD_METHOD' AGGREGATE_BY
```
Use the AGGREGATE_BY filter with the following values if you want to see which application user and component was actively consuming most of the CPU time:
```
'HASH, THREAD_STATE, APP_SOURCE, APP_USER' AGGREGATE_BY
```
Additionally, if you are only looking for the threads actively consuming CPU, you can set a filter in the modification section for THREAD_STATE:
```
'RUNNING' THREAD_STATE,
```
For further information refer to KBA 2114710 - FAQ: SAP HANA Threads and Thread Samples .
## Log Files for OS and Network
If the steps already described do not help to identify the performance problem, then it may be that the issue is not related to the SAP HANA database but could be an operating system, hardware, or network-related problem. In this case you can check the messages file for the time of the performance issue from the directory VAR/LOG for the HANA nodes. Check the messages for any indication of OS, hardware or network-related problems.
If necessary you can collect a runtime dump when the performance issue happens again and is live on the system as described above.
## Known Performance issues and How to Resolve Them
The following table identifies some specific known problems with links to KBAs which give technical details about how to resolve them.
| Symptom                                                                                    | Solution                                                                                                                                                                                                                                                                                                                                                                                                             |
|--------------------------------------------------------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| After migration to HANADBfor ECC or Suite on HANAsystem reports and transactions are slow. | Check that you are using HANAoptimized transactions and applications, some optimizations may need to be explicitly switched on via the switch framework (transaction SFW5). Refer to SAP Note 1761546 - SAP ERP poweredbySAPHANA - Optimizations and the attached document (Suite on HANAOptimizations) which lists optimized objects (reports and transactions). 1761546 SAP ERP powered by SAP HANA- Optimizations |
| Symptom                                                                                                                                                                                                                 | Solution                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| After migration to HANADBcustom transactions or ABAP programs are slow.                                                                                                                                                 | You may need to optimize custom programs and transactions by using the code inspector tool and SQL monitor. Refer to the note 1912445 - ABAP custom code migration for SAP HANA- recommendations and Code Inspector variants for SAP HANAmigration . See also the blog post 'ABAP on HANA- from analysis to optimization' which includes, for example, a section on The Golden Rules: Five performance guidelines for ABAP database operations . |
| The system is slow with a lot of state- ments running. Statements of the type SELECT TOP X cause a performance problem on SAP HANA.                                                                                     | Refer to the SAP Note 2238679 - High CPU Consumption Caused by UnifiedTa- ble::MVCCObject::generateOLAPBitmapMVCC .                                                                                                                                                                                                                                                                                                                              |
| After a HANAupgrade the performance of calculation views is poor.                                                                                                                                                       | Refer to the following SAP Notes: • 2441054 - High query compilation times and absence of plan cache entries for queries against calculation views • 2291812 SAP HANADB: Disable/Enable CalculationEngine Feature - Calc- View Unfolding .                                                                                                                                                                                                       |
| In an ABAP environment you are using the fast data access (FDA) protocol and the performance of queries is poor.                                                                                                        | Refer to the KBA 2399993 - FAQ: SAP HANAFast Data Access . Question 6 identifies a list of possible problems which may occur with FDA.                                                                                                                                                                                                                                                                                                           |
| Using fast data access the HANAsys- tem has poor performance. In the HANAtrace files you see entries like: Destroying allocator 'Connection/xxxxx/ Pool/RowEngine/Session' with x blocks and x bytes still allocated' . | This known issue is related to memory management of FDA queries and is descri- bed in SAP Note 2580435 - Memory Leak in Pool/RowEngine/Session . This may be solved by upgrading to a higher version of SAP HANA: HANA01SPS12 Revision 122.15 or higher, or, HANA02SPS01 Revision 012.04 or higher.                                                                                                                                              |
## Further Information
Links to KBAs and SAP Notes which may be helpful :
- 1969700 - SAP HANA Statement Collection
- 1813020 - How to generate a runtime dump on SAP HANA
- 2399979 - How-To: Configuring automatic SAP HANA Data Collection with SAP HANASitter
- 2498739 - How-To: Analyzing Runtime Dumps with SAP HANA Dump Analyzer.
- 2000000 - FAQ: SAP HANA Performance Optimization
- 2222217 - How-To: Troubleshooting SAP HANA Startup Times
- 2222250 - FAQ: HANA Workload Management
- 2600030 - Parameter Recommendations in SAP HANA Environments
- 2114710 - FAQ: SAP HANA Threads and Thread Samples
You can also use Guided Answers to analyze performance problems. The following Guided Answer trees are focused on the SAP NetWeaver product:
- Master Guided Answer: Performance issues
- How to perform a Network analysis (Using Operation System Monitor - ST06)
- Analysis of Hardware Bottlenecks for Performance (Using Operation System Monitor - ST06)
- Performance Analysis Procedure for an ABAP Program (Using SAP Workload Monitor - ST03)
## Related Information
ALTER SYSTEM {START | STOP | SAVE | CLEAR} KERNEL PROFILER Statement (System Management)