Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Series on Workload Analysis for HANA Platform - 5. Performance Impact by HANA Internal Functions

TaoShen
Associate
Associate
a month ago
435

In the last blog, we discussed how network, I/O, and locks can influence HANA workloads. In this blog, we will continue by exploring how HANA's internal functions may affect performance and contribute to the overall workload.

HANA Internal Functions

The concept of HANA internal functions is somewhat abstract. For the purposes of this blog, we define internal functions as those not directly related to daily business operations but necessary for supporting the successful execution of HANA platform operations. These include, but are not limited to, functions such as merge, compression, savepoint, garbage collection, and statistics collection. Under normal circumstances, these functions run to ensure HANA operates as expected. However, there are cases where these internal functions can consume a significant portion of system resources, impacting the actual business workload running on the system.

Abnormal workloads generated by HANA internal functions can arise from various complex factors, such as impractical partitioning, improper parameter settings, or expensive queries. These workloads may appear sporadically, on a daily or hourly basis, or persist over time. It is therefore crucial to identify the symptoms of this abnormal behavior and examine each internal function's influence from different perspectives.

Here are several helpful notes addressing the behavior of specific HANA internal functions:

Again, the HANA internal function is a big topic which will not be explained in details here. In this blog, we only focus on how some of those internal functions could contribute to the HANA workload patterns and degrade the system performance.

Example 1 - High CPU consumption causing by optimize compression

Optimize compression usually be triggered after a delta merge on a specific table, to compress the data in memory. This makes it less costly for the SAP HANA database to keep data in main memory. It also speeds up searches and calculations. However, without a proper limitation on the optimize compression, this activity could use up all available threads and lead to a temporary (or last for hours!) system hang. End user could not perform their task in time manner.

The following chart shows how the optimize compression looks like on HANA thread level. This system contains 4 CPU sockets, each of them contains 54 logical CPUs. Between 1:54 and 2:28, there observed a high number of running threads in method "SortRestRangeJob". Accordingly to SAP Note 2114710, this indicates the optimize compression is working (on specific table). The trigger of this activities is usually due to a large amount of data insert into a table and triggered the delta merge.  

 

TaoShen_0-1730751290114.png

Before HANA 2.0 SPS03, there was no parameter available to directly control this behavior. To prevent such situations, the max_concurrency_hint had to be set to limit the number of job worker threads used by these internal functions. However, this could negatively affect other internal functions as well. Starting with SPS03, the parameter indexserver.ini -> [optimize_compression] -> row_order_optimizer_threads was introduced, allowing direct control over the number of threads used by the optimized compression process.

In addition to optimizing compression, delta merge activities on large tables—especially those without a proper partitioning strategy—can sometimes cause system-wide performance issues. However, these issues rarely introduce significant additional workload to the system, so we will not discuss the impact of long-running delta merges here. Since optimizing compression is often followed by these long-running delta merge activities, it is possible to modify the (automatic) merge decision function, either globally or on a table-specific basis, to ensure it runs during non-business hours, such as daily off-peak times or weekends.

Example 2 - Periodically CPU peak due to statistics server job scheduler

The statistics server is responsible for collecting historical data within HANA and storing it in the _SYS_STATISTICS schema. The statistics server is now embedded within the SAP HANA indexserver, meaning that these data collection activities contribute to the overall workload, alongside daily business operations. The STATISTICS_SCHEDULABLEWRAPPER is used to schedule collection jobs (for different monitored targets) at varying intervals. The shorter the interval, the more data is collected, which increases system resource consumption by the statistics server.

 

TaoShen_1-1730751290139.png

In a newly installed HANA instance, it is common to observe that the statistics scheduler job consumes a significant portion of the workload, often appearing at the top of the SQL list. This is normal, as no business load is running yet, so the system mainly performs internal functions, including the statistics scheduler job. However, in an established system with critical business activities, if the statistics scheduler job still accounts for more than 10% of the total workload (e.g., based on total thread consumption), this could indicate an issue that requires investigation to determine the cause of the high resource consumption by the statistics server.

The system’s historical information can be viewed through _SYS_STATISTICS.STATISTICS_SCHEDULE, which provides details on job scheduling, last run times, and job execution intervals. If significant CPU spikes occur at regular intervals, this view can help identify which statistics job is contributing most to the workload. The interval for each job can be adjusted based on its usage and criticality (with a few exceptions mentioned in SAP Note 2147247). While it is possible to disable certain collection jobs, this is not generally recommended.

Starting with HANA 2.0 SPS06, a workload class is automatically created for the statistics server. This workload class includes limits on both statement memory and thread usage. With this configuration, the workload generated by the statistics server should remain within predefined thresholds and no longer represent a significant portion of the overall system workload.

Example 3 - Surge CPU consumption by periodical scheduled garbage collection jobs

There are different types of garbage collection activities in SAP HANA. Most of these are triggered automatically and periodically by default configuration. However, when a garbage collection activity is triggered during critical business hours, it can add additional workload to the system and impact normal business operations. Sometimes, this impact can be significant. Therefore, it is important to review the settings for garbage collection jobs and ensure that all non-critical garbage collection activities are scheduled to run during non-business hours.

Persistence GC Job
The persistence garbage collection (GC) job cleans up garbage in persistence areas, and can sometimes lead to increased CPU consumption and disk I/Os. In thread samples, a large number of threads in the method "GCJob" with a running status indicates that the persistence garbage collection is active. If a significant portion of system resources is consumed by this, it is advisable to reduce the concurrency of the persistence garbage collection job by adjusting the max_gc_parallelity parameter.

TaoShen_2-1730751290223.png

LOB GC Job
The LOB garbage collection job cleans disk LOBs periodically, and its frequency is controlled by the parameters indexserver.ini -> [lobhandling] -> garbage_collect_daily_schedule_s, indexserver.ini -> [lobhandling] -> garbage_collect_interval_s, and indexserver.ini -> [row_engine] -> garbage_lob_file_handler_interval. If high CPU consumption is regularly observed during LOB garbage collection activities (e.g., thread methods like GarbageLobFileHandler, RSLobGarbageRemover, or LobGarbageCollector / LobGarbageCollectorJob), consider adjusting these parameters to ensure that collection jobs run only during non-critical business hours (e.g., 1 a.m.). For more details on scheduling LOB garbage collection at specific times, refer to SAP Note 3147465 - Schedule LOB Garbage Collection Periodically at Fixed Times.

Memory GC Job
The memory garbage collection job ensures that SAP HANA reclaims unused memory. This process helps reduce memory consumption and may also decrease the frequency of MemoryReclaim locks, which can impact business performance at times. Several parameters, as described in SAP Note 2169283 - FAQ: SAP HANA Garbage Collection, can help control the frequency and thresholds for triggering memory garbage collection jobs. If configured improperly, memory utilization may be higher than necessary. Additionally, if memory consumption becomes critical and no effective memory reclaim strategy is in place, the manual command hdbcons 'mm gc -f' can be used as a workaround to reclaim memory immediately. For more details, refer to SAP Note 1999997 - FAQ: SAP HANA Memory.

There are other types of garbage collection activities as well, but the ones discussed above are the most commonly observed contributors to additional system workload.

Above all, the workloads generated by SAP HANA internal functions are sometimes avoidable and should not be considered part of the sizing results unless these workloads are unavoidable. In a well-tuned system, the workload from internal functions should not exceed 5% to 10% of total CPU and memory resources. If this threshold is exceeded, it can lead to inaccurate assessments of the overall workload pattern and potentially result in additional costs due to unnecessary hardware resource consumption.

 
🙉🙈🙊

Series on Workload Analysis for HANA Platform

This blog post is part of the 'Series on Workload Analysis for HANA Platform'. In upcoming posts, we will demonstrate the methodology of HANA workload management. Here's what you can look forward to in this series:

Stay tuned as we explore these aspects in detail, providing insights and strategies to optimize your HANA environment.

Labels in this area
Popular Blog Posts