This lesson covers the troubleshooting of high CPU consumption on the system.
A constantly high CPU consumption leads to a considerably slower system, where no more requests can be processed. From an end-user perspective, the application behaves slowly, is unresponsive, or can seem to hang.
Note
Optimal CPU use is the desired behavior for SAP HANA. Therefore, performance issues are nothing to worry about unless the CPU becomes a bottleneck. SAP HANA is optimized to consume all the memory and CPU available. The software paralyzes queries as much as possible, to ensure optimal performance. Therefore, if the CPU usage is near 100% for a query execution, it does not always mean that there is an issue.
Indicators of CPU-related Issues
CPU-related issues are indicated by alerts issued, or on cards in the SAP HANA Cockpit 2.0.
The following alerts may indicate CPU resource problems:
Host CPU Usage (Alert 5)
Most Recent Savepoint Operation (Alert 28)
Savepoint Duration (Alert 54)
The following sources alert you to high CPU consumption on your SAP HANA database:
Alert 5 (Host CPU usage) for current or past CPU usage
The displayed CPU usage on the overview screen
Choose the CPU Usage card to see detailed CPU usage. In the detailed graph, several CPU-related KPIs are shown.
On the left side of the screen, the legend shows which color represents which KPI. By default all KPIs are shown, which can make the graph appear cluttered. Use the checkboxes in the legend to show or hide KPIs.
You can display a specific time period to investigate by using the From and To fields.
Analysis of CPU-related Issues
When analyzing high CPU consumption, you must distinguish between the CPU resources consumed by SAP HANA itself, and those consumed by other, non-SAP HANA processes on the host. While the CPU consumption of SAP HANA is addressed here in detail, the CPU consumption of other processes running on the same host is not covered. Such situations are often caused by additional programs running concurrently on the SAP HANA appliance, such as anti-virus and backup software. For more information, see SAP Note: 1730928.
A good starting point for the analysis is the Database Overview screen in the SAP HANA cockpit. It displays the overall SAP HANA CPU usage, including all processes on the host, and keeps track of the maximum CPU usage that occurred over the last two hours. If SAP HANA CPU usage is low, then everything is fine. If the card shows high CPU usage, there is most likely an issue on the SAP HANA database server. You should start an investigation to find the cause of the problem.
To find out what is happening in more detail, open the Threads card from the Database Overview page. To perform CPU time analysis, follow these steps:
In the global.ini configuration file, check that the cpu_time_measurement_mode and enable_tracking parameters are set to On. This ensures that resource tracking is switched on.
Add the CPU Time column to the display, using the settings on the right of the Threads screen.
The Thread Monitor now shows, in milliseconds, the CPU time of each thread running on SAP HANA. A high CPU time for related threads indicates that an operation is causing increased CPU consumption.
To identify the expensive statements causing the high resource consumption, turn on the expensive statement trace. This trace is accessed using the Monitor Expensive Statements link in the Monitoring section of the SAP HANA database Overview screen. Start the expensive statements trace and specify a reasonable run time. If possible, add further restrictive criteria such as database user, or application user, to narrow down the amount of information traced.
Note
When resource_tracking is activated, the CPU time for each statement is shown in the CPU_TIME column.
Resolving CPU-related Issues
Resolving CPU-related issues is prioritized so that the system can return to a normal operating state. This prioritization may make it more difficult to identify the root cause of the problem.
After resolving the situation, it may not be possible to find out the actual root cause. Therefore, record the state of the system under high load for later analysis by collecting a full system info dump (see the lesson "Handling System Offline Situations", earlier in this course).
To stop the operation causing high CPU consumption, go to the Thread card. In the Client Host, Client IP, Client PID, and Application User columns (use the Settings button to add the columns) you can identify the user that triggered the operation. To resolve the situation, contact the user and clarify the actions they are performing.
As soon as this is clarified and you agree on how to resolve the situation, two options are available:
On the client side, end the process calling the affected threads.
Cancel the operation that is related to the affected threads.
To do this, select the identified thread in the Thread card and choose Cancel.
For further analysis on the root cause, open a ticket in SAP HANA Development Support and attach the full system info dump, if available.
Retrospective Analysis of CPU-related Issues
There are several ways to analyze the root cause of an issue after it is resolved.
To perform a retrospective analysis of high CPU consumption, check the CPU Usage and Alerts cards. The alert timestamp, combined with the data in the CPU Usage card, helps you to determine the time frame of high CPU consumption. If you are unable to determine the time frame, check the HOST_RESOURCE_UTILIZATION_STATISTICS statistics server table, in the _SYS_STATISTICS schema. This table provides historical host resource information for up to 30 days.
With this information, search through the trace files of the responsible process. Be careful to choose the correct host when SAP HANA runs on a scale-out landscape. The information contained in the trace files provides indications of the threads or queries that were running during the affected time frame.
If the issue recurs, due to scheduled batch jobs or data loading processes, turn on the Expensive Statements trace to record all involved statements. Also, check for background jobs running concurrently, like backups and delta merge. These jobs may cause a resource shortage when running in parallel. Historical information about background jobs can be obtained from the following system views:
M_BACKUP_CATALOG
M_DELTA_MERGE_STATISTICS
A longer history can be found in the HOST_DELTA_MERGE_STATISTICS statistics server table, in the _SYS_STATISTICS schema.