Skip to Content
Technical Articles
Author's profile photo Neha Raina

Dynamic tiering and Extended tables

What is dynamic tiering?

Let me begin by telling you what is dynamic tiering.

SAP HANA dynamic tiering is a native big data solution for SAP HANA. Dynamic tiering adds smart, disk-based extended storage to your SAP HANA database. Dynamic tiering enhances SAP HANA with large volume, warm data management capability. When you use SAP HANA to place hot data in SAP HANA in-memory tables, and warm data in extended tables, highest value data remains in memory, and cooler less-valuable data is saved to the extended store. The extended store can reduce the size of your in-memory database.

The dynamic tiering option in SAP HANA SPS 09 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.


Extended tables :

SAP HANA dynamic tiering adds the SAP HANA dynamic tiering service (esserver) to the SAP HANA system. We use this service to create the extended store and extended tables. Extended tables behave like all other SAP HANA tables, but their data resides in the disk-based extended store. The extended tables concept relates to warm data. Since warm data is not constantly accessed, this data does need to occupy space in the main memory of SAP HANA. Extended tables are tables managed by SAP HANA. Logically they are located in the SAP HANA database catalog and can be used as if they were persistent SAP HANA tables. These tables are physically located in a disk-based data storage however, which has been integrated into the SAP HANA system. The user sees the entire system as one single database.

Now enough theory lets get down to actually seeing how to convert in-memory data to extended tables. ?


We need the access to HANA studio for this purpose.

  1. First we need to check that we have a table which is an in memory table. ( use HANA studio for this )

Right click on the table NATION_DT, which is now an in-memory table, and select Use Extended Storage

  1. In the “Use Extended Storage” pop up window, keep all the options as default. Click the Movebutton to proceed.

  1. Once the conversion is complete, a pop up window will appear verifying that the change has been made, click OK to close the window.

4. Verify the table has been correctly converted. You should see that the NATION_DT table has the “EXTENDED” attribute and is now an extended table.


So we have seen how to convert an in-memory table to an extended table.✌

I have taken the scree from source : Converting Between an In-Memory Table and an Extended Table

PS: I took screens from SAP source because I am not allowed to take screens from my system.(but I assure you this method works ? )

Now lets see how to convert in memory table to extended table using SQL statement

Prerequisites :

  1. The extended store exists.
  2. If converting a HANA table to a delta-enabled extended store table, extended storage is delta-enabled.
  3. Requires the EXTENDED STORAGE ADMIN system privilege.

SQL statements :

>> ALTER TABLE t3 NOT USING EXTENDED STORAGE; // without using delta


Little information on Hot data and Warm data:

HOT data:

Data that is accessed very often, for example, for reporting or for processes in Data Warehouse Management. (Queries for Info Cubes, Data Store objects)

WARM data:

This data is no longer or rarely accessed. (Write-optimized Data Store objects of the corporate memory, or Persistent Staging Areas or write-optimized Data Store objects of the acquisition layer).

COLD data:

Data of a BW system that is no longer required, and that can be or was saved using Near-line Storage.

Advantages :

To maximize native query performance, query optimizations ship the query operations to either the hot store or extended storage. Query operations against extended storage data are pushed down to the dynamic tiering worker, minimizing the load on the SAP HANA host.

  • It allows you to offload older, less frequently accessed data to an integrated disk tier.
  • It lets you access the data in the disk tier with excellent performance.
  • It lowers the total cost of ownership (TCO) of your SAP HANA system significantly.



SAP HANA Dynamic Tiering: Administration Guide

SAP HANA Dynamic Tiering: Master Guide

SAP HANA Dynamic Tiering – Now Validated and Supported on the AWS Cloud

Hope it will help. Let me know in case you have any queries regarding this.

Thank you.

Neha Raina

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Juergen Pitz
      Juergen Pitz


      ok, whatever cool feature this Dynamic Tiering is - from looking at it, it has nothing to do with warehouse management. So why is it tagged with SAP Extended Warehouse Management? Pleeeaasse.



      Author's profile photo Neha Raina
      Neha Raina
      Blog Post Author

      Hi Juergen,

      I have updated the blog with the suggestion.

      Neha Raina

      Author's profile photo Juergen Pitz
      Juergen Pitz

      Thank you!


      Author's profile photo Wajid Khan
      Wajid Khan

      Hello Neha,

      Thanks for sharing the valuable information about Dynamic Tearing and it's feature. This information is short but important. Could you please revert me on below query?


      1. How the SAP license work for dynamic tiering?
      2. Is it possible to revert table from extended storage to main memory?
      3. As per your experience, What you will suggest to customer between Dynamic tiering or archiving?  Yes, It's depend on the requirement but ultimately we need to finalized it.


      Wajid Khan

      Author's profile photo Neha Raina
      Neha Raina
      Blog Post Author

      Glad you liked it.

      Please find the below answers for your queries.

      1. How the SAP license work for dynamic tiering?

      Yes. For SAP HANA dynamic tiering there is requirement of separate license and this will incur additional cost. Please see the below link :

       2.  Is it possible to revert table from extended storage to main memory?

      Yes, this is possible.

       3.   As per your experience, What you will suggest to customer between Dynamic tiering or archiving?  Yes, It’s depend on the requirement but ultimately we need to finalized it.

      If you have cold data then go for dynamic tiering. Dynamic tiering is a new feature available with functionally rich features. It depends on your business needs to go with.

      Thank you.
      Neha Raina



      Author's profile photo Sid Krishna
      Sid Krishna

      Neha Raina : Can you please list the difference between the Warm Storage - DT, Extended Storage & NSE?

      Author's profile photo Mahesh Dusanapudi
      Mahesh Dusanapudi

      DT or Extended Storage both are same and based on Sybase IQ technology. This will have separate Data files and log files  and you need to enable or install DT separately and also needed additional license .

      Where as NSE is completely based on HANA Technology and inbuild . No separate installation  or license is needed . But requirement is HANA version should be on HANA 2.0 SPS04

      Author's profile photo Sreekanth Surampally
      Sreekanth Surampally

      Thanks Mahesh, if DT and NSE use the same technology and processing, then can you tell me what is the use case of choosing DT with an additional license?

      Currently, Is there any limitation or an use case which is not covered by NSE for Warm Storage?





      Author's profile photo Mahesh Dusanapudi
      Mahesh Dusanapudi

      Hi Sreekanth,


      DT and and NSE not use same technology. DT is based on Sybase IQ and NSE is integrated HANA feature. DT can be separated and run on different host with any hardware and no need to be HANA certified hardware .

      NSE uses some of the memory portion of HANA and limited to scale-up until HANA 2.0 SPS04 . From HANA 2.0 SPS05 NSE can be used on scale-out as well.


      If you are planning to have warm store its better to start with NSE .




      Author's profile photo Hemanth Vasireddi
      Hemanth Vasireddi

      Hi Mahesh,

      Can you advise if the statement "From HANA 2.0 SPS05 NSE can be used on scale-out as well." is there an SAP Note for reference that this is possible




      Author's profile photo Hervian Hervian
      Hervian Hervian

      Hi Neha,


      is this technologies also available for row store tables?

      how about write performance after implementing this extended storage?