SAP HANA CPU

Symptom

You want to understand the CPU consumption of SAP HANA.

Environment

SAP HANA

Cause

1. Which indications exist for SAP HANA CPU problems?
2. Where do I find more information related to SAP HANA CPU analysis?
3. Is a high CPU consumption generally an issue?
4. How can I collect information about the CPU consumption?
5. How can additional CPU resource tracking be activated?
6. Is it possible to capture SQL statements with a particularly high CPU consumption?
7. What is the difference between system, user and I/O wait CPU consumption?
8. How can the CPU consumption of SAP HANA hosts be optimized?
9. How can CPU intensive operations in SAP HANA be identified and optimized?
10. Is it possible to limit the amount of consumed CPU?
11. How can I determine physical CPU details on SAP HANA side?
12. How can I trigger automatic follow-up actions in case of a high CPU consumption?
13. Is the CPU consumption shown by SAP HANA always correct?

Resolution

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:

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

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:

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?

As of SAP HANA SPS 12 a CPU approximation based on the states of active threads is used. When an active thread doesn’t have to wait (e.g. for locks, I/O or network), it is supposed to consume CPU. See SAP Note 2114710 for more information related to SAP HANA threads. As a consequence, you always have CPU figures available without having to activate specific measurements. Indeed, there is no other way to activate CPU time measurement mode with SAP HANA SPS 12 and higher.

With SAP HANA SPS <= 11 CPU consumption isn’t measured per default, but you can activate CPU time measurement with the following parameters:

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.

The following monitor view columns contain CPU information:

• M_SERVICE_THREADS.CPU_TIME_SELF
• M_SERVICE_THREADS.CPU_TIME_CUMULATIVE
• M_EXPENSIVE_STATEMENTS.CPU_TIME

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:

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:

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:

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.

For more detailed information like the call stacks you can collect additional runtime information:

• SQL: “HANA_Threads_Callstacks” (SAP Note 1969700) can be used to retrieve the call stacks of the currently active threads.
• A runtime dump with call stacks and further runtime information can be created as described in SAP Note 1813020).
• Even more details can be retrieved with a kernel profiler trace (SAP Notes 1804811 and 1875973).

Additionally make sure that the SAP HANA parameters are set based on the standard recommendations (see SAP Note 2186744). For example, SAP Note 2207501 describes CPU issues if a specific undocumented parameter is activated.

Typical scenarios of high SAP HANA CPU consumption are:

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

See SAP Note 2222250 (“How can workload management be configured for CPU and threads?”) for an overview of parameters and settings that can be used to influence parallelism and CPU consumption.

11. How can I determine physical CPU details on SAP HANA side?

General CPU information can be retrieved via monitoring view M_HOST_INFORMATION or with the related command SQL: “HANA_Hosts_Overview” (SAP Note 1969700).

Example:

---------------------------------------------------------
|CPU_DETAILS|CPU_MHZ|CPU_MODEL                          |
---------------------------------------------------------
|60*2294MHz |   2294|Intel Xeon CPU E7-8880 v3 @ 2.30GHz|
---------------------------------------------------------

If hyper threading is used, the number of logical CPU cores is included in brackets after the CPU number (e.g. “60(120)*2294MHz”).

Further details like the number of CPUs per socket can’t be determined via SQL. On SAP HANA side you can alternatively use the following hdbcons command (SAP Note 2222218):

hdbcons 'jexec info'

Example:

Using 2 numa nodes
numa_features: 1
max_concurrency: 20 (cfg=0, dyn=20)
max_concurrency_hint: 20 (cfg=0)
min_concurrency_hint: 10 (cfg=0)
0 statement limiters
System info:
2 CPU sockets, 20 physical cores, 20 logical cores, 20 total possibles cores
Using global restriction to a subset of cores: [11111111 11111111 1111]
Numa node 0: usable cores=10
  workers: [11111111 11000000 0000]
  others:  [00000000 00000000 0000]
  full:    [11111111 11000000 0000]
  max_concurrency: 10, dyn=10
Numa node 1: usable cores=10
  workers: [00000000 00111111 1111]
  others:  [00000000 00000000 0000]
  full:    [00000000 00111111 1111]
  max_concurrency: 10, dyn=10
[OK]

In this example you can see that 20 physical cores are available in two sockets, so each socket contains 10 cores.

12. How can I trigger automatic follow-up actions in case of a high CPU consumption?

For an optimal root cause analysis it is important to have as much analysis data available for times of high CPU consumption as possible. In order to support the collection of analysis data (e.g. via runtime dump, SAP Note 2400007) you can schedule SAP HANASitter (SAP Note 2399979).

Example:

13. Is the CPU consumption shown by SAP HANA always correct?

In most cases you can rely on the displayed CPU figures, but in the following scenarios the information can be wrong: