Data Tiering

The speed at which a business accesses its data varies based on many different conditions. SAP HANA Cloud provides the capability to use additional storage tiers other than memory to store data based on access speed and data density needs. SAP HANA Native Store Extension (NSE) is the disk storage option for use with data less frequently accessed than in memory. Memory is still utilized but only for the data that is being used. The SAP HANA data lake also offers high density storage at capacities far greater than possible using the other storage tiers.

Let’s look at SAP HANA Cloud NSE in more detail, with some practical examples to enhance the understanding.

SAP HANA Native Storage Extension

SAP HANA Native Storage Extension (NSE) is a general-purpose, built-in warm data store in SAP HANA Cloud which allows management of less-frequently accessed data without fully loading it into memory.

It integrates disk-based or flash-drive based database technology with the SAP HANA in-memory database for an improved price-performance ratio.

Warm data is primarily used to store read-only data that doesn’t require frequent access. The data need not reside continuously in SAP HANA memory, but is still managed as a unified part of the database ― transactionally consistent with hot data, and participating in backup and system replication operations, and is stored in lower cost stores within SAP HANA Cloud.

This image shows the difference between standard HANA in-memory storage and the storage offered with NSE:

The SAP HANA Native Storage Extension (NSE) feature for warm data storage is enabled by default in SAP HANA Cloud. Database developers may choose to assign specific tables, columns, or partitions to use NSE. SAP HANA NSE uses a dedicated in-memory buffer cache to load and unload pages of tables, table partitions or table columns.

The initial buffer cache size can be adjusted once the SAP HANA instance has been created.

Note

Changing a table from in-memory to NSE does not change the name of the table.

Try it out!

The following exercise demonstrates how to enable Native Storage Extension for a table and leverage its advantages in managing warm data.

This example uses the GX_EMPLOYEES columnar table containing 100,000 records, with its default persistency “in-memory” (hot).

Open the Database Explorer, expand Catalog -> Tables and find the GX_EMPLOYEES table.

Select the table to open the meta data screen.

Select the Runtime Information tab to view record count and memory consumption.

To free up memory, this table will be partitioned and some data will move to the NSE warm storage tier.

Open an SQL console and run the following query which creates two similarly sized partitions based on the column EMPLOYEE_GENDER.

Code Snippet

1234
/* Range Partitioning */
ALTER TABLE GX_EMPLOYEES 
PARTITION BY RANGE(EMPLOYEE_GENDER)
((PARTITION VALUES = 'm', PARTITION OTHERS));

Refresh the Runtime Information tab for the GX_EMPLOYEES table and select the Partitions tab. Two partitions have been created and Loaded Status for them is FULL, meaning both partitions are fully loaded into memory. Note the current Total Memory Consumption which is 2.8 MB for the table in memory.

Issue the following command via the SQL console to move partition 1 to NSE:

Code Snippet

123
/* Move partition 1 to NSE */
ALTER TABLE GX_EMPLOYEES
ALTER PARTITION 1 PAGE LOADABLE;

Return to the metadata screen for the GX_EMPLOYEES table and select the refresh button to update the figures.
The Loaded status of partition 1 has changed to partially loaded and total memory consumption by the table has been reduced:

The reason for the memory reduction is because the records from partition 1 are now stored as warm data on disk.

Issue the following command via the SQL console to query the total number of records in the table:

Code Snippet

12
/* Count the records */
SELECT COUNT(*) FROM GX_EMPLOYEES;

Observe that while half of the table is in memory (hot), with the other half on disk (warm), the table is still treated as a whole when queried.

Now merge partitions and change the entire table to hot (COLUMN LOADABLE).

Code Snippet

12
/* Merge partitions */
ALTER TABLE GX_EMPLOYEES MERGE PARTITIONS;

Refresh the contents of the runtime information tab for the GX_EMPLOYEES table again. Observe that the “Total Memory Consumption” has been set back to the previous standard and there are no longer any partitions.

Now move the entire table to warm storage (PAGE LOADABLE) and notice the reduction in memory consumption.

Issue the following command in the SQL console:

Code Snippet

12
/* Move entire table to NSE */
ALTER TABLE GX_EMPLOYEES PAGE LOADABLE CASCADE;

Refresh the contents of the runtime information tab for the GX_EMPLOYEES table again.

Observe that the amount of memory used by the table has significantly reduced after being moved to warm storage entirely:

As the last step, make this table “hot” again by issuing the following command:

Code Snippet

12
/* Move entire table into memory */
LOAD GX_EMPLOYEES ALL;

Refresh the metadata screen for the GX_EMPLOYEES table to view the Runtime information again. The table is now back to its fully loaded state - no partitions and with hot “in-memory” access speed. Memory consumption is back to the initial state also.

Note

The memory consumption of the table might might be larger or smaller than the figures shown in the image below - depending on whether the delta merge process has taken place by the time the screen is refreshed.

Well done!! This completes the lesson on the different options in SAP HANA Cloud for storing data in the right place based on user and storage requirements.

Next unit