HANA Column store LOAD and UNLOADS and SQLs

[rajarajeswari_kaliyaperum]

COLUMN STORE TABLES LOAD AND UNLOADS

HANA will start to trigger a column store unload as soon as HANA nodes hits peak memory based on the LRU algorithm to full fill new memory requirement.Column store tables will get unloaded .We can also make a column table to be thrown out from memory more frequently by setting unload priority on those column tables.

Below has the details as of how to find out whether a table is fully loaded or partially loaded into HANA memory.If partially loaded what are the columns that have been loaded.

 

SECTION 1:Technical part and command:

Eg: BSEG which has 21 partitions with

1.TABLE load STATUS:

select SCHEMA_NAME,TABLE_NAME,LOADED from M_CS_TABLES where TABLE_NAME='BSEG' and SCHEMA_NAME = 'SAPERP';

(loaded = full/partially, part_ID = n = partition number that is loaded)

2.COLUMN loaded status of a specific table.

select SCHEMA_NAME,TABLE_NAME,LOADED from M_CS_COLUMNS where TABLE_NAME='BSEG'and SCHEMA_NAME = 'SAPERP';

(loaded = true/false)

3.Unload priority value check

SELECT SCHEMA_NAME,TABLE_NAME,UNLOAD_PRIORITY FROM TABLES WHERE TABLE_NAME = 'BSEG'and SCHEMA_NAME = 'SAPERP';

(unload_priority=0 to 9)(Tables with priority 9 gets unloaded frequently by HANA based on the memory requirement)

4.Load a table fully into memory

LOAD "SAPERP"."BSEG" ALL;

ALL = indictes HANA to load all the columns into memory

5.How to remove a table from memory

UNLOAD SAPERP.BSEG;

6.How to change the load priority of any load store table

ALTER TABLE  SAPERP.BSEG UNLOAD PRIORITY 5;

7.How many times your HANA server node unloaded the tables for all the other reasons other than MERGE

select top 20 host, substr(unload_time, 0, 10), count(*) from m_cs_unloads where reason != 'MERGE' and schema_name = 'SAP<SID>' group by host, substr(unload_time, 0, 10) order by 2 desc, 1

8.What were the reasons our table was thrown out of memory

select * from M_CS_UNLOADS where TABLE_NAME='BSEG' and SCHEMA_NAME = 'SAPERP';

select * from M_CS_UNLOADS where TABLE_NAME='BSEG' and SCHEMA_NAME = 'SAPERP';

 

SECTION 2:COLUMN STORE BASIC details

Note:Unloads are only valid for column store tables in HANA. Row store tables gets loaded into memory during start up and stays there and can never be unloaded atall!

Before I go to unload please note that column store tables in HANA gets loaded based on a parameter attached to the respective table .When I say loaded , there are 2 states for table- FULL or PARTIAL. FULL means a table is fully loaded into memory. PARTIAL means not all the columns of that table is loaded into memory. HANA load these tables at column level only when that table is accessed either by a DML or DDL statement provided if it is already not in memory .

 

So why unloads happen in HANA ? ie, Why does HANA unloads or throws out column store tables from its memory ?

In general tables in HANA gets unloaded by LRU algorithm (Least recently used) algorithm that HANA generates for itself when ever HANA memory is nearing the Allocated Limit(AL) of that service. Here,in below screen shot the server that is showing in red state in studio is going to reach it allocation limit of 1.9TB and is currently at 1.8TB. As soon as AL reaches ,to process further request HANA unloads tables from memory based on the LRU algorithm mentioned above .

Additionally 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.

Hence during this time (A span of unloading to release memory) it is acceptable to see poor performance in HANA which will go back to normal in a minute or so in a normal use case!!

 

There are also other cases where an unload happens which are – LOW MEMORY(as specified above),EXPLICIT,UNUSED RESOURCE,MERGE,SHRINK

Same studio screen shot after 2 minutes.

 

As a continuation you can read the below blog for further details with screen shot of above specified queries.

How to perform manual Load and unload of column store tables in HANA

https://blogs.sap.com/2020/09/12/how-to-perform-manual-load-and-unload-of-column-store-tables-in-han...

Thanks for reading!

Please leave your comments and questions here!!