Loads and Unloads in SAP HANA Environments Interview Questions and Answers

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.

Tables in row store are typically loaded during startup and remain in memory permanently.

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:

SQL: "HANA_Configuration_MiniChecks" (SAP docs 1969700, 1999993) returns a potentially critical issue (C = 'X') for one of the following individual checks:

When do loads happen?

Columns are loaded into memory in the following situations:

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:

In exceptional cases you can manually execute the following hdbcons command in order to collect the current load state:

tablepreload w

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.

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>'

What are typical unload priorities for tables in SAP environments?

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

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

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_UnloadsAndLoads" 
• SQL: "HANA_LoadHistory_Services"

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.

How granular can loads and unloads be performed?

The fines granularities of loads and unloads are:

How can I check for errors during column loads?

The monitoring view M_CS_LOADS doesn't contain an error information. Whenever a column load is triggered, it is recorded in M_CS_LOADS, independent if it was successful or if it failed (e.g. due to a lack of memory or an inconsistency).

If you want to monitor failed column loads, you can check the trace files for entries starting with "load failed:", e.g.:

[224524]{340032}[291/-1] 2016-02-03 18:37:39.190596 e attributes AttributeValueContainer.cpp(03563) : load failed: exception 1: no.70000000

(AttributeEngine/AttributeStoreFile.h:339)

ste::Exception type AttributeStoreFile error 'ISAPSR3T000AAattribute_203.bin': AttributeEngine: error reading file message additionalInfo $ADDINFO$

exception throw location:

1: 0x00007f43076400c7 in ste::Exception::Exception(char const*, char const*, char const*, int, char const*)+0x53 at Exception.cpp:12 (libhdbbasement.so)
2: 0x00007f42fc5c8e94 in AttributeEngine::AttributeStoreReadFile::throwError(int, char const*, long, bool)+0x130 at AttributeStoreFile.h:142 (libhdbcs.so)

You can use SQL: "HANA_TraceFiles_Content" (TRACE_TEXT = 'load failed:%') available via SAP doc 1969700 for that purpose.

Be aware that load failures with the following error codes can be a consequence of cancellations of sessions while performing a column load (e.g. when connections to the database are established at a time when the column store isn't completely loaded, yet):

6900: Attribute engine failed
6923: Attribute load failed

This is usually harmless, but you should better avoid it (e.g. by starting the SAP application servers after the column store has completely loaded).

What is a typical performance of the column store load after startup?

The actual performance of the column store load after startup depends on factors like the configured parallelism (indexserver.ini -> [parallel] -> tables_preloaded_in_parallel), the I/O performance (SAP doc 1999930) and the available system resources. In optimal scenarios, a load throughput of around 200 GB per minute is possible.

Example: (load of 8 TB column store within 40 minutes, 200 GB / minute)

[16899]{-1}[12/-1] 2016-12-04 01:35:35.302222 i TableReload      TRexApiSystem.cpp(00628) : Now reloading 45209 tables (loading up to 14 tables in parallel)
[16899]{-1}[12/-1] 2016-12-04 02:15:47.178840 i TableReload      TRexApiSystem.cpp(00659) : Finished table reloading
--------------------------
|SNAPSHOT_TIME   |USED_GB|
--------------------------
|2016/12/04 02:15|   9156|
|2016/12/04 02:14|   9126|
|2016/12/04 02:13|   9085|
|2016/12/04 02:12|   9014|
|2016/12/04 02:11|   8899|
|2016/12/04 02:10|   8700|
|2016/12/04 02:09|   8462|
...
|2016/12/04 01:41|   1586|
|2016/12/04 01:40|   1370|
|2016/12/04 01:39|   1101|
|2016/12/04 01:38|    854|
|2016/12/04 01:37|    680|
|2016/12/04 01:36|    648|
|2016/12/04 01:35|    657|
|2016/12/04 01:34|    625|
|2016/12/04 01:33|    186|