In my recent articles, I focused on using SAP HANA calculation views as a way to prevent additional persistency within your SAP Business Warehouse powered by SAP HANA landscape. Because calculation views work like stored procedures, all transition logic and results of the view are calculated on the fly (in memory) and are not stored in any persistent table in the SAP BW Data Dictionary.

Depending on the design of the calculation views, this in-memory calculation may consume a huge amount of SAP HANA memory. Imagine if the calculation view reads a DataStore object (DSO) containing several million rows.

I’d like to introduce you to a safety belt setting. It is implemented by an SAP HANA parameter that ensures that a badly designed calculation view in combination with the new SAP HANA Composite InfoProvider, the successor of the old-fashioned MultiProvider, does not stall the complete SAP HANA system. A side effect that is not well known within the SAP HANA environment is the memory consumption of such a virtual design (because no data is kept in any extra persistency staging layer) when it comes to execution of queries.

I walk you through the optional SAP HANA settings to avoid possible memory exhaustion when it comes to SAP HANA process execution. In one of my SAP HANA projects my team had the problem that under specific query execution terms, the memory use of a single query ran into total memory exhaustion and ate up to 1 TB of memory. Later, after we checked with SAP support, the problem was located inside SAP HANA and was fixed by SAP Notes. However, we still keep this safety belt, implemented by the SAP HANA parameter I cover in this article, in place. You never know what problem might occur in the future.

By default, a single SAP HANA process can consume 100 percent of the available memory of your existing SAP HANA installation. That could cause serious problems such as a stop to urgent processing of data. If you want to safeguard your system from uncontrolled expensive queries, then it’s a good idea to limit the memory consumption of a single statement per host.

I show you where and how to maintain these settings to avoid future problems. As I mentioned before, in our case, the original problem was caused by an SAP HANA defect, but I think it is a great idea to restrict any process to a maximum of, for example, 500 GB up to 1 TB (or bigger depending on your SAP HANA environment). It is better to see a dump within your SAP BW powered by SAP HANA system than to have a stalled system.

SAP HANA Parameters Offered by SAP to Safeguard Your System

Starting with SAP HANA Support Package 08, SAP introduced parameter statement_memory_limit to restrict the query at a predefined memory consumption limit. Queries or other processes are aborted with an out-of-memory (OOM) dump (‘compositelimit_oom OR [MEMORY_LIMIT_VIOLATION]’), if their execution reaches the limit specified by this parameter–statement_memory_limit.

For a detailed explanation on the SAP Help site, click the following link:

https://help.sap.com/saphelp_hanaplatform/helpdata/en/0b/20ee7e3d1440308b361bd9707af0ad/content.htm

To change any of the parameters, you need an SAP HANA database user with the INIFILE ADMIN system privilege assigned. You also need the statement memory tracking feature enabled, which I show with several illustrations. None of the changes to those parameters require a system restart. Right after your changes are in place, the safety belt works.

The settings can be changed by the help of SAP HANA studio, Eclipse Studio, or directly inside SAP GUI transactions. Within SAP HANA or Eclipse Studio, double-click your selected system (e.g., production) and navigate to the Configuration tab (Figure 1).

To change the parameters from the SAP GUI, call transaction code ST04 and navigate to the Configuration folder and the INI Files screen. Select the parameters (global.ini) to change its sub-parameters (Figure 2).

Default Settings

First, check the default settings of the parameters you are going to change to implement the extra security. I am demonstrating the changes via Eclipse Studio, but the ini files listed in the changes are the same via the SAP GUI. The screen might look a bit different, but after the settings are done, the safety belt works the same as it does if it is changed via SAP HANA or Eclipse Studio.

Within the administration view (Figure 1) of your selected SAP BW powered by SAP HANA system, navigate to the Configuration tab. After you select the tab, the system reads and displays all available ini files that store the SAP HANA configuration.

Navigate to the global.ini entry, expand the tree view, and select resource_tracking (Figure 3).

As you can see, the actual system is in a default state. Note that enable_tracking and memory_ tracking are still turned off, which means that SAP HANA-executed processes (e.g., queries) can use as much memory as the system has available. To change this behavior, the first step is to enable the tracking and the second step is to define the memory (safety belt) setting.

First, enable the tracking and memory tracking by changing those two parameters.

Just double-click the enable_tracking parameter and change the value to on. Confirm by clicking the Save button (Figure 4). Proceed the same way with the second parameter, memory_tracking, and change its value to on as well. Confirm by clicking the Save button.

Now you should see the two altered parameters within your global.ini file as shown in Figure 5 and in this list:

• global.ini > [resource_tracking] > enable_tracking = on
• global.ini > [resource_tracking] > memory_tracking = on

The last step after enabling the memory tracking of SAP HANA processes itself is to populate the parameter value about your desired threshold or safety belt. The values I use in this article are just for demonstration purposes. Depending on your existing SAP HANA memory, you have to decide on an appropriate size. A good rule of thumb is to use, for example, 50 percent of the total memory.

Adding this parameter value populates the MEMORY_SIZE column of the expensive statement trace (M_EXPENSIVE_STATEMENTS) with your chosen limit. To change the default value (which is blank with your desired safety-belt value), stay in the global.ini file, navigate to the memorymanager entry, and expand its settings in a tree view (Figure 6).

As you can see the parameter statement_memory_limit has no value specified. To change the parameter, proceed as described before by double-clicking the value and changing the value (e.g., to 200 GB).

As mentioned before, this value is just for demonstration purposes and the real value depends on your SAP HANA memory. Confirm by clicking the Save button. The result is displayed immediately in Eclipse (Figure 7).

The same can be done via transaction code ST04 and its parameter changes.

Alternatively, if you are familiar with SQL script you can directly change all the parameters with the help of SQL script commands. Execute transaction code ST04 and navigate to Diagnosis and SQL Editor. Enter the statement shown in Figure 8 in the command frame of the transaction and execute the statement either by clicking the execute icon or pressing the F8 key.

The code changes the parameter to the desired 500 GB value. (I am changing the value of 200 GB I just entered to 500 GB to demonstrate the different ways to change the safety-belt value.)

Execute the following SQL command:

alter system alter configuration ('global.ini','SYSTEM') SET ('memorymanager', 'statement_memory_limit') = '500' with reconfigure;

This SQL command sets the limit from the just-entered 200 GB to 500 GB. (Remember, this is just a demonstration value. Your real value, for example, could be 1,000 GB when running a 2,000 GB SAP HANA system.)

To see the result of the upper SQL command, follow Eclipse menu path Administration perspective > Configuration > global.ini > [memorymanager] > statement_memory_limit  (Figure 6)  to find the new value (maximum_memory_allocation_in_gb), which has been set to 500 GB.

After the adopted settings are made via the SQL command, the desired entry in Eclipse changes from the default (or the test case with 200 GB) to the desired value, in my example, 500 GB, as shown in Figure 9.

As you can see, the setting has been changed to the desired value.

Example of Memory Cap (Safety Belt)

In a test case I set up in our test environment, I forced a process (query) to consume more than the allowed 500 GB of working memory, which was the cause for creating an error entry for the trace user. An error message is given to the user. In this case when executing the safety belt, you see a trace file entry such as SAPDBTech JDBC: [2048]: column store error: searchtable error: [9] Memory allocation failed. An additional OOM diagnosis file is generated at the time the memory limit is encountered. The diagnosis file indicates the execution of the memory allocation alert (failure) that has been triggered by the given parameter. You find it in the index server Diagnosis Files tab (Figure 10).

Listing all available files in the Diagnosis Files view (Figure 10) shows a lot of entries. Check for the latest file starting with the prefix indexserver_hdb to check if any error occurred (Figure 11).

Checking the index server trace file by double-clicking the file shows that a memory allocation failed error was thrown because the requested memory tried to exceed my 500 GB safety belt (Figure 12). The entry shown in Figure 12 is an example that happened in the productive environment. As long as you do not have any memory problems hitting the safety-belt threshold described in this article, you do not find such an entry in your diagnosis file for indexserver.

As you can see, there are a couple of valuable parameters that might be considered when it comes to a reliant SAP HANA environment. This parameter change is optional. It is not a must-have, but in severe cases of a faulty design of calculation views or buggy query execution caused by various reasons, it can keep your SAP BW powered by SAP HANA system healthy and protected.