2127458 - FAQ: SAP HANA Loads and Unloads

Symptom

You are interested in details about unloads in SAP HANA environments.

Environment

SAP HANA

Cause

1. What are loads and unloads in SAP HANA environments?
2. Which indications exist for SAP HANA load and unload issues?
3. When do loads happen?
4. How can reload information for startup, host auto failover and system replication be collected?
5. When do unloads happen?
6. In which sequence are columns unloaded?
7. What are typical unload priorities for tables in SAP environments?
8. How can unloads be monitored?
9. Where is unload information recorded?
10. How granular can loads and unloads be performed?

Resolution

1. What are loads and unloads in SAP HANA environments?

When a column is loaded, it is copied from persistence into SAP HANA column store memory.
Unloads are table column displacements from column store memory.
In this SAP Note we focus on loads and unloads of columns in column store. Tables in row store are typically loaded during startup and remain in memory permanently.

2. Which indications exist for SAP HANA load and unload issues?

While loads usually don't indicate issues, unloads are critical for the following reasons: They are often indicators of memory bottlenecks and they introduce overhead because unloaded column may have to be reloaded after some time.
The following SAP HANA alerts indicate problems in the unload area:

Alert	Name	SAP Note	Description
55	Columnstore unloads	1977207	Determines how many columns in columnstore tables have been unloaded from memory. This can indicate performance issues.

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
430	Number of column store unloads (last day)
431	Time since last column store unload (days)

3. When do loads happen?

Columns are loaded into memory in the following situations:

Reason	Details
Explicit access	When a table column is accessed and it doesn't reside in memory, yet, it is loaded into memory. Exceptions: Hybrid LOB columns are not loaded into memory when they exceed the configured memory threshold (SAP Note 1994962). Paged attributes are only partially loaded into memory (SAP Note 1871386) Time spent for loading table columns during SQL statement preparation can be identified via column TOTAL_TABLE_LOAD_TIME_DURING_PREPARATION in monitoring view M_SQL_PLAN_CACHE. Time spent for loading table columns during SQL statement execution is not inidividually displayed.
Explicit load	You can use the following options to load tables into memory explicitly: Command Details LOAD "<table>" ALL SAP HANA Studio -> <system> -> Catalog -> <schema> -> <table> -> "Load into memory..." Load all table columns into memory LOAD "<table>" ("<column>") LOAD "<table>" ("column1>", ..., "<columnN>") Load the defined table columns into memory LOAD "<table>" DELTA Load the delta storage of the table into memory LOAD "<table>" HISTORY ... When the key word HISTORY is added after <table>, the load is related to the history part of a temporal table. If you want to load all tables into memory (e.g. as recommended in SAP Note 2066313), you can use the following Python script available as part of the SAP HANA installation: /usr/sap/<sid>/HDB<inst_id>/exe/python_support/loadAllTables.py This tool should only be used in exceptional situations, because loading all tables into memory can result in memory and CPU bottlenecks.
Reload after startup (explicitly configured tables)	The following commands can be used to define tables that should be loaded directly after startup: Command Details ALTER TABLE "<table>" PRELOAD ALL Set the reload flag for all columns of the table ALTER TABLE "<table>" PRELOAD ("<column>") ALTER TABLE "<table>" PRELOAD ("<column1>", ..., "<columnN>") Set the reload flag for the defined columns of the table ALTER TABLE "<table>" PRELOAD NONE Unsets the reload flag for all columns of the table You can check for tables and / or columns with an activated reload flag in the following ways: Columns IS_PRELOAD and IS_PARTIAL_PRELOAD of TABLES Column PRELOAD of TABLE_COLUMNS SQL: "HANA_Tables_ColumnStore_PreloadActive" (SAP Note 1969700) Loading columns flagged with PRELOAD has precedence over columns marked for pre-warming (see below).
Reload after startup (pre-warming based on columns previously loaded) Automatic load of columns on secondary system of system replication environment Automatic load of columns on standby node during auto host failover	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 Default Unit Details indexserver.ini -> [sql] -> reload_tables true   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. indexserver.ini -> [parallel] -> tables_preloaded_in_parallel 5   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 fulfill your needs. global.ini -> [system_replication] -> preload_column_tables true   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. You can check for tables currently part of this reload information using the following hdbcons command (SAP Note 2222218): tablepreload c -f

4. How can reload information for startup, host auto failover and system replication be collected?

The following parameter is used to control the collection of reload information:

Parameter	Default	Unit	Details
global.ini -> [persistence] -> tablepreload_write_interval	3600 (Rev. 69 and below) 86400 (Rev. 70 and above)	s	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.

In exceptional cases you can manually execute the following hdbcons command (SAP Note 2222218) in order to collect the current load state:

tablepreload w

See SAP Note 1889081 for more information.

5. When do unloads happen?

The following table lists the main reasons for unloads. You can determine the reason of an unload via column REASON of monitoring view M_CS_UNLOADS.

Reason	Details
LOW MEMORY	SAP HANA automatically performs unloads when memory becomes scarce. This is typically critical for performance and should be avoided whenever possbile. The following SAP HANA parameter globally controls when unloads are performed in case of a high memory utilization: Parameter Default Unit Details indexserver.ini -> [memoryobjects] -> unload_lower_bound 0 (default algorithm)  byte 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. See SAP Note 1993128 for more information. Normally the default value provides a reasonable unload behavior and so it should only be adjusted in specific cases.
EXPLICIT	Tables can be unloaded explicitly using the following SQL command: UNLOAD "<table_name>" Alternatively you can use SAP HANA Studio for that purpose: SAP HANA Studio -> <system> -> Catalog -> <schema> -> <table> -> "Unload from memory..."
UNUSED RESOURCE	Automatic unloads when a column exceeds the configured unused retention period: Parameter Default Unit Details global.ini -> [memoryobjects] -> unused_retention_period 0 (disabled)  s Number of seconds after which an unused object can be unloaded global.ini -> [memoryobjects] -> unused_retention_period_check_interval 7200  s Check frequency for objects exceeding the retention time 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.

6. In which sequence are columns unloaded?

Usually unloads happen based on a "least recently used" (LRU) approach, so the columns having not being used for the longest time are unloaded first.
If there are tables that should in general be replaced earlier or later, you can prioritize unloads using the UNLOAD PRIORITY setting:

ALTER TABLE "<table_name>" UNLOAD PRIORITY <priority>

The priority can vary between 0 and 9. Tables with a higher priority are unloaded earlier than tables with a lower priority. SAP HANA considers both the last access time and the unload priority for the proper sequence of unloads, so both factors are important.
The unload priority of a table can be checked via:

SELECT UNLOAD_PRIORITY FROM TABLES WHERE TABLE_NAME = '<table_name>'

7. What are typical unload priorities for tables in SAP environments?

The following table unload priorities are typically used in SAP environments:

Unload priority	Table type	Details
0	Temporary tables System tables	All temporary tables (TABLES.IS_TEMPORARY = 'TRUE', created with NO LOGGING) must not be unloaded and therefore always have unload priority 0. If you want to define a higher unload priority, you receive the following error: SQL error 257: sql syntax error: invalid unload priority for temporary table, only 0 is allowed
5	Default	Per default tables are delivered with a medium unload priority of 5.
7	BW tables (DSO, PSA)	Some BW DSO (/BIC/A*) and PSA (/BIC/B*) can typically be unloaded earlier than other tables and are therefore configured with unload priority 7. Be aware that it depends significantly on the type of DSO and PSA, so it is normal that there are also DSO and PSA tables with unload priority 5.

You can use SQL: "HANA_Tables_ColumnStore_UnloadPriorities" (SAP Note 1969700) in order to evaluate existing unload priorities and check for tables with unload priorities different from the standard.

8. How can unloads be monitored?

You can monitor unloads in the following way:

• Monitoring view M_CS_UNLOADS
• SAP HANA Studio -> Administration -> Performance -> Load -> Column Unloads
• SQL: "HANA_Tables_ColumnStore_Unloads" (SAP Note 1969700)
• SQL: "HANA_LoadHistory_Services" (SAP Note 1969700, SPS 09 and higher)

9. Where is unload information recorded?

Unload information is recorded in unload trace files on disk level. M_CS_UNLOADS is based on these trace files. Typically up to 10 * 10 MB trace files can be written per host and service. As a consequence unload information is still available, even if SAP HANA is restarted.
See SAP Note 2119087 ("Unload trace") for more information.

10. How granular can loads and unloads be performed?

The following rules apply for loads:

• Loads can happen column-wise, but they can't restricted to specific partitions.
• If paged attributes are used (SAP Note 1871386) loads can happen page-wise.

The following rules apply for unloads:

• Unloads happen table-wise.

Header Data

Released On	24.01.2016 16:02:48
Release Status	Released to Customer
Component	HAN-DB SAP HANA Database
Priority	Normal
Category	How To