1. Which indications exist for SAP HANA CPU problems?

The administration overview screen or the load graph of SAP HANA Studio shows a high current or historic CPU consumption.
The following SAP HANA alerts indicate problems in the CPU area:

Alert Name SAP Note Description
5 Host CPU Usage 1909670 Determines the percentage CPU idle time on the host and therefore whether or not CPU resources are running low.

SQL: “HANA_Configuration_MiniChecks” (SAP Notes 1969700, 1999993) returns a potentially critical issue (C = ‘X’) for one of the following individual checks:

Check ID Details
210 Minimum CPU rate (MHz)
211 Hosts with varying CPU rates
220 Current CPU utilization (%)
221 Peak CPU utilization (%, last day)
222 Time since CPU utilization > 95 % (h)
226 Peak system CPU utilization (%, last day)

See SAP Note 1999993 for more information related to SAP HANA Mini Checks.
A high amount of database threads are active with status ‘Running’ (see SAP Note 2000000).

2. Where do I find more information related to SAP HANA CPU analysis?

The SAP HANA Troubleshooting and Performance Analysis Guide contains – among others – information about analyzing and optimizing the SAP HANA CPU consumption.

3. Is a high CPU consumption generally an issue?

A high CPU consumption can be normal and acceptable, because SAP HANA uses available CPUs efficiently by parallelizing complex operations. Therefore, a high CPU consumption can be intended and a consequence of a massively parallelized processing of complex operations. If on the other hand simple database requests or other processes consume a high amount of CPU, this can result in a critical CPU bottleneck situation which can be avoided. This SAP Note provides information how to analyze and optimize unnecessary CPU consumption.

4. How can I collect information about the CPU consumption?

The following options exist to check the current CPU utilization of the SAP HANA database server:

  • SAP HANA Studio -> Administration -> Overview -> CPU Usage
  • SAP HANA Studio -> Administration -> Performance -> Load -> [System] CPU

The following SQL statements are available via SAP Note 1969700 in order to understand the CPU consumption of the SAP HANA hosts:

SQL statement name Description
SQL: “HANA_Resources_CPUAndMemory_History” Among others this SQL statement is able to check for current and historic CPU consumption on a granular basis. It displays the CPU utilization (in %), the number of CPUs busy with user activities, the number of CPUs busy with system activities and the number of idle CPUs.
SQL: “HANA_Hosts_Overview” This command provides information about the used CPUs including frequency and number.

Additionally you should use operating system tools (e.g. top) in order to understand which processes are responsible for the highest CPU consumption.

5. How can additional CPU resource tracking be activated?

Per default SAP HANA doesn’t actually measure the individual CPU consumption of threads and SQL statements. You can activate this measurement with the following parameters:

Parameter  Default Recommendation Description
global.ini -> [resource_tracking] -> enable_tracking
on General activation of resource tracking functionality
global.ini -> [resource_tracking] -> cpu_time_measurement_mode
fast (SPS 08 and below)
on (SPS 09 and above)
Activation of CPU specific data collection

Activated CPU tracking will populate the following columns:


Be aware that the activation of the CPU time measurement can be expensive in terms of system CPU consumption and performance, so you should pay attention for these side-effects and deactivate the time measurement if required.

6. Is it possible to capture SQL statements with a particularly high CPU consumption?

Up to Rev. 93 it is not possible to automatically capture SQL statements with a high CPU consumption.
When Rev. 94 or higher is in place and CPU tracking is activated (see above), you can capture SQL statements exceeding a defined CPU consumption via expensive statements trace (see SAP Note 2119087) using the following parameters:

Parameter Default Unit Details
global.ini -> [expensive_statement] -> enable
false Main switch, ‘true’ activates the expensive statement trace
global.ini -> [expensive_statement] -> threshold_cpu
us Threshold for minimum CPU utilization in micro seconds (Rev. >= 94)

7. What is the difference between system, user and I/O wait CPU consumption?

CPU utilization is typically separated in system CPU, user CPU and I/O wait CPU consumption:

CPU utilization type Description
User CPU consumption originating from coding outside of the operating system kernel, e.g.:

  • Expensive operation in SAP HANA like scan of large table or counting a high amount of distinct values
  • Expensive operation of non-SAP HANA product running on same host
System CPU consumption originating from the operating system kernel, e.g.:

  • Operating system background activities like memory defragmentation
  • Implicit OS calls of SAP HANA like active waits for locks or I/O requests
I/O Wait CPUs waiting for I/O requests to complete, can usually be considered as idle time

8. How can the CPU consumption of SAP HANA hosts be optimized?

The following approaches exist to optimize CPU consumption and reduce the risk of running into CPU bottlenecks:

Possible symptoms Optimization Optimization Details
High system CPU consumption caused by OS processes Optimize OS configuration Make sure that the OS recommendations of SAP Note 2000003 (“How can the configuration and performance of the SAP HANA hardware, firmware and operating system be checked?”) are implemented.
A typical reason for high CPU consumption in this context is the configuration of Transparent Huge Pages (see SAP Note 2131662). Make sure that you disable this feature based on the provided recommendations.
A more detailed analysis of activities involving system CPU consumption can be performed via OS kernel stack backtrace as described in SAP Note 2166414.
High system CPU consumption caused by SAP HANA processes Optimize OS calls triggered by SAP HANA Disable traces unless really required. For example, cpu_time_measurement_mode as described above can be responsible for a signficant system CPU overhead.
Check (e.g. via SQL: “HANA_Threads_ThreadSamples_AggregationPerTimeSlice”, SAP Note 1969700) what kind of activities happen at times of increased system CPU consumption. If background activities (e.g. garbage collectors) and / or active waiting for locks is linked to times of increased system CPU consumption, open a SAP incident for further analysis.
In order to reduce the CPU consumption caused by parallel garbage collection you can set the max_gc_parallelity parameter to a smaller value (see below).
High system CPU consumption caused by other processes Optimize OS calls of other processes Identify why application is responsible for the processes with the high system CPU utilization and optimize or reduce these calls.
High CPU consumption of non SAP HANA processes Optimize non SAP HANA software Reduce unnecessary high CPU requirements of non SAP HANA software running on SAP HANA hosts. If required, get in touch with the vendor of the software in order to ask for assistence.
High SAP HANA user CPU consumption Analyze expensive SAP HANA operations See “How can CPU intensive operations in SAP HANA be identified and optimized?” below for more details.

9. How can CPU intensive operations in SAP HANA be identified and optimized?

In order to identify CPU intensive operations within SAP HANA you can use SQL: “HANA_Threads_ThreadSamples_FilterAndAggregation” (SAP Note 1969700) in the following way:

  • If there is a specific time of high resource consumption, restrict BEGIN_TIME and END_TIME accordingly.
  • Set the filter THREAD_STATE = ‘Running’, because typically the threads in state ‘Running’ are responsible for CPU consumption.
  • Set AGGREGATE_BY to ‘STATEMENT_HASH’, so that the statement hashs of the top SQL statements are displayed.
  • If no statement has exists for most samples, you can set AGGREGATE_BY to ‘THREAD_TYPE, THREAD_METHOD, THREAD_DETAIL’ (or a subset) to understand which types of threads are responsible.

If you have identified top statement hashs, you can check if these SQL statements can be tuned. See SAP Note 2000002 for more information related to SQL optimization.
If the high CPU consumption isn’t caused by SQL statements and instead internal activities like garbage collections are responsible, you should double-check that the high CPU consumption is really critical for the system. If yes, a kernel profiler trace (SAP Notes 1804811 and 1875973) can be used to get a better insight.
Typical scenarios of high SAP HANA CPU consumption are:

Symptom Details
High user CPU consumption caused by specific SQL statement See SAP Note 2000002 and optimize the SQL statement.
Increased system and user CPU consumption
SQLExecutor threads in status “Running” with slow access to specific table
MVCCGarbageCollector thread active or threads in CollectVersion method
The problem may be caused by a high number of changes to a specific table record in row store, which results in version consolidation overhead.
Check from application side if a particularly high amount of changes is executed against single table records and try to reduce the change frequency. If the problem is caused by UPDATE operations on table QIWKTAB you should check for a proper RFC queue configuration. For example, SMQ2 entries in “STOP” state can result in a permanent re-execution of the QIWKTAB UPDATE operations and should be avoided.
From a SAP HANA perspective the row store version consolidation of single records is further improved with SPS 09 and will also reduce the probability of performance escalations.

10. Is it possible to limit the amount of consumed CPU?

If you face a very high SAP HANA CPU consumption, you should always check in the first place if it can be limited by performance tuning approaches. There were already cases when the creation of a single SAP HANA index reduced the CPU consumption from 100 % to less than 10 %. Only if you have made sure that the high CPU consumption is required and can’t be reduced via technical tuning, you can control and limit it in the following ways:

Action Availability   Parameter Default  Restart required Details
Limit number of SQL executors general
indexserver.ini -> [sql] -> sql_executors
0 (-> Number of logical CPU cores) no SQL executors are threads, which are responsible for normal SQL request processing. The number of SQL executors can be configured with this parameter.
This parameter is less strict than the max_sql_executors parameter described below, because it is possible that the actual number of used SQL executors exceeds the configured value.
Limit maximum number of SQL executors general
indexserver.ini -> [sql] -> max_sql_executors
0 (-> No limit) no SQL executors are threads, which are responsible for normal SQL request processing. The maximum number of SQL executors can be configured with this parameter.
Make sure that max_sql_executors is always larger than the above parameter sql_executors. Otherwise you can run into terminations like:

exception  1: no.71000132  (ptime/session/tcp_receiver.cc:849)

max number of SqlExecutor threads are exceeded: current=80, max=15
Limit CPU consumption of job workers general
indexserver.ini -> [execution] -> max_concurrency
0 (-> Number of logical CPU cores) no Job workers are threads, which are responsible to process parallelized OLAP load and internal activities like savepoints or garbage collection. The maximum number of logical CPUs consumed by JobWorkers can be configured with this parameter.
If each active job worker currently consumes less than 100 % CPU, more job workers than the max_concurrency value can be activated. The defined number of logical CPUs may be exceeded if the active job workers increase their CPU consumption over time.
Limit parallelism general
indexserver.ini -> [execution] -> max_concurrency_hint
indexserver.ini -> [parallel] -> num_cores
Number of logical CPU cores (SPS <= 08)
max_concurrency (SPS >= 09)
no Similar to max_concurrency these parameters help limiting the number of used job worker threads. It defines the number of jobs to create for an individual parallelized operation. For one statement, there can be several such operations, so the total number of jobs being created for a statement will typically exceed the value of this parameter, as well as the number of worker threads being used for these jobs.
For historic reasons these two parameters exist, but they are used for the same purpose. You should generally use identical values for both parameters.
Limit garbage collection parallelism general
global.ini -> [persistence] -> max_gc_parallelity
0 (-> Number of logical CPU cores) yes Garbage collectors clean up no longer needed information on a regular basis. The parallelism of them is controlled by the max_gc_parallelity parameter. Per default a rather high parallelism is used which can result in a high system CPU consumption. By reducing the parameter you can reduce this overhead. Be aware that a small value can result in garbage collection delays, a growing persistency and “database full” situations. See SAP Note 2169283 for more information.
Assign CPUs to services as of SPS 09
daemon.ini -> [<service_name>] -> affinity
No assignment yes It is possible to limit the CPU consumption per service (e.g. statisticsserver, indexserver) by adjusting the affinity parameter in the related daemon.ini section. See the SAP HANA Troubleshooting and Performance Analysis Guide for more information related to CPU affinity adjustments.

Leave a Reply