While RAM prices have significantly dropped (from $6 Million per GB in 1980 to $5.5 per GB in 2013) in last few years, data has also been significantly growing (data doubles every 18 months). To meet today’s business requirements, it is vital to get instantaneous responses on current data. At the same time, it is also important to have the ability to transparently access large amounts of data when needed.

The dynamic tiering option in SAP HANA SPS09 gives the ability to keep the data in either memory or on the disk in a columnar format. Data is not duplicated. Dynamic tiering option helps users to choose memory for hot data and disk for warm data, helping to strike the right price/performance balance. To do so, you define the table as “extended table” using an SQL CREATE statement. These tables are like any other SAP HANA tables except for the fact that they are created on the disk and not in memory. From the application developer point of view, these tables can be queried and modified using standard SQL statements, like any other SAP HANA tables. You can join extended tables with in-memory tables and at any point of time, you can convert extended tables into in-memory tables and vice versa, with an SQL ALTER statement.


Dynamic tiering also works with multitenant database containers and you can configure extended storage for each tenant.

When I showed the dynamic tiering system demo at TechEd&&-code Las Vegas I got some interesting questions from people stopping at the SAP booth. Here are a few of them:

1) Is it possible to move data from in-memory table to the extended table?

Yes. You can write a procedure that moves data from in-memory table to the extended table, based on some conditions. Using the job scheduler in SAP HANA XS engine, you can then periodically run the procedure and physically move the data from the in-memory table to the extended table. You can learn more about SAP HANA XS Engine job scheduler from SAP HANA Help tutorial and video from Thomas Jung.

2) Since all in-memory tables are stored also in disk for durability (to restart when power goes off), then, what is the difference between in-memory tables and extended tables?

With extended tables, data is expected to be in the disk and algorithms used to access the data are optimized for disk-based data. With in-memory table, data is expected to be in memory and algorithms used to access data are optimized for in-memory data. When data in extended tables is updated or new data is inserted into them, the changes are made in the disk. With in-memory table, data is updated in the memory. Of course, logs are written to the disk to ensure durability in both cases.

That being said, in some cases, it is possible that data in extended tables is cached in memory and in-memory tables are not loaded in memory.

3) Does dynamic tiering supports backup and recovery?

Yes. SAP HANA manages backup for extended tables. System replication for extended tables is planned for next release.

4) How is Near-Line Storage (NLS) in SAP Business Warehouse (BW) option different from dynamic tiering?

NLS in SAP BW uses SAP IQ as a cold, read-only, historical archive. Dynamic tiering is used to store warm data. With dynamic tiering, SAP HANA gives the ability to create extended tables on the disk. These extended tables are local to SAP HANA and queried or modified like any other HANA table. You can use both dynamic tiering and NLS at the same time for SAP BW on HANA. In this case, SAP BW will handle data management for you. SAP is working on making dynamic tiering more suitable for additional use cases.

In summary, dynamic tiering is a new SAP HANA feature introduced in SPS09 that gives the ability to store frequently used data (hot) in memory and less frequently used data (warm) in the disk.

Following are some of the additional resources to learn more about dynamic tiering feature:

Please let me know if you have any additional questions.

VN:F [1.9.22_1171]
Average User Rating
Rating: 4.5/5 (17 votes cast)
SAP HANA Dynamic Tiering, 4.5 out of 5 based on 17 ratings