Clarification on LOADING and Dynamic Tiering in HANA

[ssurampally]

Hi, I am trying to clarify my understanding of LOADING and Dynamic Tiering features in HANA platform,

LOADING: Typically data resides in Memory and also in Hard disk(recovery purpose). In case of insufficient memory, system will UNLOAD tables from memory to free up space. Similarly when required, tables can be loaded into Memory.

Dynamic Tiering: non frequently accessed tables are created as extended tables, only in Disk. They are not available in memory. My query can access these extended tables from disk when requested. however, they do not get loaded into Memory. Can you please confirm that, my understanding is correct on Dynamic Tiering tables never loaded into Memory...

Thanks

Sreekanth

[lbreddemann · Accepted solution]

Well, at some point, obviously, the data in dynamic tiering tables has to get into memory, don't you think?

The whole point of DT is about data management.

There's a lot of data "that just sits there" most of the times. You cannot delete it, as sometimes it is required, but most of the time it would simply allocate expensive main memory that could be used otherwise.

Now, DT offers a solution to this by offering a fully integrated option to keep the data on a separate server, that is cheaper than the HANA system and that comes with a query processing engine that is designed to work on data that is not permanently in main memory.

Since it is built into SAP HANA all data transfer between indexserver and dt server happen automatically and consistently from a transaction and query point of view. You can perform consistent backup/recovery etc..

In effect, you can use a SAP HANA with DT as a single database.

This is very different from linked databases/SDA connections, where transactions (and with that, all backup and recovery) are bound to each single database instance.

Coming back to the original question: yes, data from DT systems eventually will be transferred to the indexserver memory, but it is not permanently kept there.

[ssurampally · Accepted solution]

oh ok, that makes sense. So DT data is always loaded in index server memory but not stored there, Thank you Lars, it is very helpful.

[RobertWaywell · Accepted solution]

@Sreekanth

Close but not quite. As a disk based engine within the HANA system, the DT engine manages cache memory for processing queries or the portions of queries executed within DT. If a query only requires data stored in extended tables or the partitions of multi-store tables assigned to EXTENDED STORAGE, then the entire query will be processed by the DT engine and only the result set will be returned to the client via an index server or worker node.

We have an OpenSAP course for DT that is starting up next week that goes into more detail on how the query processing works: Introduction to SAP HANA Dynamic Tiering

[RobertWaywell]

That's a good response from Lars. I also want to specifically mention multi-store tables. A multi-store table is a partitioned HANA table with some partitions stored in DEFAULT STORAGE in-memory and some partitions stored in EXTENDED STORAGE in dynamic tiering. Multi-store tables greatly simplify application development when working with tiered data by allowing developers to interact with a single physical table instance as opposed to working with separate table instances for in-memory and tiered data.

[ssurampally]

Thanks for that Rob, I did not know that, a table with some partitions in memory and some partitions in Extended storage DT. It is a great feature to use one table instance.. Very helpful answer.