Preload Column Tables Parameter in HANA

The following SAP HANA parameters control column loads during SAP HANA startup and on the secondary system of a system replication scenario based on columns loaded into memory before the shutdown.

Parameter Name       -  preload_column_tables
Default Value             -  true

About Parameter      - 

Per default SAP HANA loads the columns into the memory of the secondary system of a system replication scenario during normal uptime. This has the advantage that a reload is not required at failover time. If you want to disable this feature (e.g. because only limited memory is available on the secondary side), you can set the preload_column_tables parameter to ‘false’.

The effect of this parameter depends on the system where it is set.

Primary system: Information about loaded tables is collected and persisted in the system replication related snapshot.
Secondary system: The load information from primary is evaluated and the tables are loaded accordingly.

How to Set Parameter -  global.ini -> [system_replication] -> preload_column_tables


Additional Command - we can check for tables currently part of this reload information using the following hdbcons command.

tablepreload c -f

Reload Preloaded Tables Parameter in HANA

The following SAP HANA parameters control column loads during SAP HANA startup and on the secondary system of a system replication scenario based on columns loaded into memory before the shutdown.

Parameter Name       - reload_tables
Default Value           - true

About Parameter      - 

If set to ‘true’, SAP HANA loads columns into memory during startup, which were located in memory before shutdown. This can be considered as pre-warming in order to make sure that column loads are not required when the table is accessed the first time explicitly.

How to Set Parameter -  indexserver.ini -> [sql] -> reload_tables


Parameter Name       -  tables_preloaded_in_parallel
Default Value            -  5

About Parameter      - 

Number of tables loaded in parallel after startup .

A higher value typically results in quicker reloads, but a higher CPU consumption, so it is a trade-off between load time and resource consumption. If you want to adjust it, you should perform some tests to find an optimal value to fulfil your needs.

How to Set Parameter -  indexserver.ini -> [parallel] -> tables_preloaded_in_parallel

Tablepreload Write Interval Parameter in HANA

How can reload information for startup and system replication be collected? The following parameter is used to control the collection of reload information:

Parameter Name       -  tablepreload_write_interval

Default Value           -  3600 (HANA Revision 69 and below)
Default Value           -   86400 (HANA Revision 70 and above)

About Parameter      - 

This parameter defines the frequency of collecting table load information for reloads during startup and on the secondary system replication side.Collection of the data is disabled by setting the value to 0.

How to Set Parameter -   global.ini -> [persistence] -> tablepreload_write_interval

Additional Command   - In exceptional cases you can manually execute the following hdbcons command in order to collect the current load state.

tablepreload writeinfo

Reference From    -  SAP Note 1889081 for more information.

Unused Retention Period Parameter in HANA

Configuring a retention for unloads typically provides no advantage and increases the risk of unnecessary unloads and loads. Therefore these parameters should only be set in exceptional situations.

Automatic unloads when a column exceeds the configured unused retention period:

Parameter Name      -  unused_retention_period

Default Value           -  0 (disabled)
Unit for Parameter  - s
About Parameter     -  Number of seconds after which an unused object can be unloaded

How to Set Parameter -  global.ini -> [memoryobjects] -> unused_retention_period


Parameter Name      - unused_retention_period_check_interval

Default Value          -  7200
Unit for Parameter  -  s
About Parameter     -  Check frequency for objects exceeding the retention time

How to Set Parameter -  global.ini -> [memoryobjects] -> unused_retention_period_check_interval

Unload Lower Bound Parameter in HANA

SAP HANA automatically performs unloads when memory becomes scarce. This is typically critical for performance and should be avoided whenever possible. The following SAP HANA parameter globally controls when unloads are performed in case of a high memory utilization:

Parameter Name - unload_lower_bound

Default Value - 0 (default algorithm)

Unit for Parameter - byte

About Parameter -

This parameter can be used to define the minimum size of swappable SAP HANA objects that should remain in memory. Higher values result in less and later unloads. Normally the default value provides a reasonable unload behaviour and so it should only be adjusted in specific cases.

How to Set Parameter - indexserver.ini -> [memoryobjects] -> unload_lower_bound

Reference from : SAP Note 1993128 for more information.

SAP HANA Statement Memory Limit Threshold

The statement memory limit is applied if the current SAP HANA memory consumption exceeds the statement memory limit threshold as a percentage of the global allocation limit.

Parameter Name - statement_memory_limit_threshold

Default Value - 0% (statement_memory_limit is always respected)

About Parameter -

The properties statement_memory_limit and statement_memory_limit_threshold in the memory_manager section of the global.ini configuration file are used to limit the memory that can be allocated with respect to statement execution.

statement_memory_limit_threshold indicates what percentage of the global memory allocation limit must be in use before the specific value of statement_memory_limit is applied. If this memory limit is being applied and a statement execution exceeds it, then the statement is aborted.

Statement Memory Limit in SAP HANA

The parameter is called "Statement_memory_limit". This parameter will help ensure a single user’s statement does not cause “Out of Memory” scenarios on the HANA appliance. A system wide “Statement_memory_limit” was introduced with HANA SP8

When reaching the statement memory limit, a dump file is created with "compositelimit_oom" in the name. The statement is aborted, but otherwise the system is not affected. The unit of measure is GB.

Default : 0 (no limit)

Set system level Statement memory limit

a. Login into HANA Studio and navigate to the “Configuration” tab.
b. Open "Global.ini–>memorymanager"
c. Set "statement_memory_limit" = desired limit in MB’s


1> This limit will be system specific depending on the Global Allocation Limit, if the system limit has never been set and the goal is to only enable user specific limits set the limit to a very high percentage of the Global allocation limit. For example if global allocation limit is set to 234496 MB, set the Statement_memory_limit to 200000 MB. The system limit will never be met and basically ignored.



2> The system limit HAS to be set to enable USER limits.
3> Note setting the "Statement_memory_limit_threshold" instead or in addition to the "statement_memory_limit" will DISABLE the USER limit. This has not been determined if a bug with Rev 91 or SAP design.

Enable user level Statement memory limit :

1. Login into HANA studio.
2. Navigate to Security–>Users and select the user the limit is required for.
3. Double click the user and go to the "User Parameter" tab.
4. Click the "+" button to add a new parameter.
5. Select the "Statement Memory Limit" parameter.
6. Enter a the desired CAP in GB and save user. Below example is set to 5 MB.

Allocation limit in SAP HANA

Collectively, all processes cannot consume more memory than the global allocation limit. The allocation limit limits the amount of memory that can be used by individual processes. The value is the maximum allocation limit in MB.

Parameter Name - allocation_limit
Default Value - A missing entry or a value of 0 results in the system using the default settings. The default allocation limit is calculated in the same way as the default global allocation limit.

About Parameter -
The allocation limit limits the amount of memory that can be used by individual processes. The value is the maximum allocation limit in MB.

-- set the global_allocation_limit=2048

hdbsql=> select round(allocation_limit/1024/1024,2) allocation_limit from m_host_resource_utilization;
ALLOCATION_LIMIT
-----------------------------
13605.15


-- set the global_allocation_limit=20480

> select round(allocation_limit/1024/1024,2) allocation_limit from m_host_resource_utilization;
ALLOCATION_LIMIT
-----------------------------
20480

Global Allocation Limit in SAP Hana

The global allocation limit limits the amount of memory that can be used by the system as a whole. The value is the maximum allocation limit in MB. Note:In a system that supports multitenant database containers, the global allocation limit configured at the system layer of the global.ini file is always effective regardless of any value configured at the database layer.

Parameter Name - global_allocation_limit
Default Value - A missing entry or a value of 0 results in the system using the default settings. The global allocation limit is calculated by default as follows: 90% of the first 64 GB of available physical memory on the host plus 97% of each further GB. Or, in the case of small physical memory, physical memory minus 1 GB.

About Parameter - The global_allocation_limit parameter is used to limit the amount of memory that can be used by the database. The value is the maximum allocation limit in MB. A missing entry or a value of 0 results in the system using the default settings. The global allocation limit is calculated by default as follows: 90% of the first 64 GB of available physical memory on the host plus 97% of each further GB. Or, in the case of small physical memory, physical memory minus 1 GB.

Change the Global Memory Allocation Limit -

The SAP HANA database preallocates a pool of memory from the operating system over time, up to a predefined global allocation limit. You can change the default global allocation limit in the global.ini configuration file.

Prerequisites - You have the system privilege INIFILE ADMIN.

Procedure :

1> In the Administration editor, choose the Configuration tab.
2> The configuration files that contain the configuration information for the system are displayed.
3> Expand the global.ini configuration file and then the memorymanager section.
4> In the context menu for the global_allocation_limit parameter, choose Change.
5> The Change Configuration Value dialog box appears.
6> Enter a value for the entire system and/or individual hosts.
7> If you enter only a value for the system, it is used for all hosts. For example, if you have 5 hosts and you set the limit to 5 GB, the database can use up to 5 GB on each host (25 GB in total). If you enter a value for a specific host, then for that host, the specific value is used and the system value is only used for all other hosts. This is relevant only for multiple-host (distributed) systems.