Memory Management in the Column Store

The column store is the part of the SAP HANA database that manages data organized in columns in memory.  The column store is optimized for read operations but also provides good performance for write operations. This is achieved through 2 data structures: main storage and delta storage. The main storage contains the main part of the data. Here, efficient data compression is applied to save memory and speed up searches and calculations.

Write operations on compressed data in the main storage would however be costly. Therefore, write operations do not directly modify compressed data in the main storage. Instead, all changes are written to a separate data structure called the delta storage. The delta storage uses only basic compression and is optimized for write access. Read operations are performed on both structures, while write operations only affect the delta.

The purpose of the delta merge operation is to move changes collected in the delta storage to the read-optimized main storage. After the delta merge operation, the content of the main storage is persisted to disk and its compression recalculated and optimized if necessary.

A further result of the delta merge operation is truncation of the delta log. The delta storage structure itself exists only in memory and is not persisted to disk. The column store creates its logical redo log entries for all operations executed on the delta storage. This log is called the delta log. In the event of a system restart, the delta log entries are replayed to rebuild the in-memory delta storages. After the changes in the delta storage have been merged into the main storage, the delta log file is truncated by removing those entries that were written before the merge operation.

Loading and Unloading of Data in the Column Store

The SAP HANA database aims to keep all relevant data in memory. Standard row tables are loaded into memory when the database is started and remain there as long as it is running. They are not unloaded.
Column tables, on the other hand, are loaded on demand, column by column when they are first accessed. This is sometimes called lazy loading. This means that columns that are never used are not loaded and memory waste is avoided. This is the default behavior of column tables. In the metadata of the table, it is possible to specify that individual columns or the entire table are loaded into memory when the database is started.

Under normal circumstances, the SAP HANA database automatically manages the loading and unloading of tables into and from memory independently, the aim being to keep all relevant data in memory. However, you can manually load and unload individual tables and table columns if necessary.
For example:

• To precisely measure the total or “worst case” amount of memory used by a particular table (load)
• To actively free up memory (unload)

If you have a particular table which should never be unloaded then you can set an unload priority. Also, vice versa, if there is a table which you know will be least used you can set the priority that this table is unloaded first.

Unload priority of a table: 0 – 9:

0 .. means not unloadable,
1 .. means latest unload and
9 .. means earliest unload

All tables under the _SYS* schemas have unload priority as 0 and those loaded on demand usually have priority as 5.

You can set a priority using the following ALTER command:

ALTER TABLE TABLE_NAME UNLOAD PRIORITY 2;

The database may actively unload tables or individual columns from memory, for example, if a query or other processes in the database require more memory than is currently available. It does this based on a least recently used algorithm.
You can also configure columns to allow access to the main storage one page at a time instead of requiring the whole column to be in memory. This enables you to save memory and query a single value in the main storage when certain individual columns or the entire table reside on disk. To enable this feature, specify column description clauses PAGE LOADABLE or COLUMN LOADABLE in the <column_desc> of a CREATE TABLE or ALTER TABLE statement.

You can investigate table unloads by using the SQL statement “HANA_Tables_ColumnStore_UnloadsAndLoads” that comes within SQL Statements.zip attachment of the note 1969700 – SQL Statement Collection for SAP HANA. In the modification section of this SQL, adjust to the appropriate begin time and end time (usually same day) and choose aggregation criteria ‘none’.

The most important information is the timestamp, schema name, table name and the reason.

Unload Reason	Description
Low Memory	Unload triggered by memory management
Explicit	Unload triggered explicitly by an user
Unused resource	Unload triggered by system due to configured parameter global.ini [memoryobjects] unused_retention_period

The main reason for your analysis should be unloads due to low memory.

In case you find out the reason for the unloads is due to low memory then it could be either one of the following reasons:

1. The system is not properly sized.
2. The table distribution is not optimized.
3. Temporary memory shortage due to exceptional activity (e.g. expensive SQL, mass activity).