How to measure HANA database temperature?

[john_hawk]

How to measure HANA database temperature? Is there SQL, ABAP code or Transaction Code which identifies and aggregates Hot (always in memory), Warm (early unload) and even Cold (data in NLS near line storage or SARA archives)? The perfect solution would summarize the table sizes by temperature category, and calculate percentages of total by temperature category. We want to benchmark how well we are managing our data lifecycle.

Thanks,

John Hawk

[AndreyF · Accepted solution]

Hi John,

you can try the following, it would summarize the memory and the disk size for the column-based tables dependent on the unload_priority

select round(sum(sum_in_mem)/1024/1024/1024/1024, 2) as mem, round(sum(dsk)/1024/1024/1024/1024, 2) as dsk, unload_priority from (

select sum(memory_size_in_total) as sum_in_mem, a.table_name ,unload_priority

from M_cs_all_columns as a inner join tables as b on a.table_name = b.table_name group by unload_priority, a.table_name) as mem

inner join (select table_name, sum(disk_size) as dsk from _SYS_STATISTICS.GLOBAL_TABLE_PERSISTENCE_STATISTICS where (snapshot_id = (select max(snapshot_id) from _SYS_STATISTICS.GLOBAL_TABLE_PERSISTENCE_STATISTICS))

group by table_name)

as pers on pers.table_name = mem.table_name

group by unload_priority

Kind regards,
Andrey Farzatinov

[RolandKramer]

Hello jhawk95337

Frankly speaking, there are always some measures/KPI which can be used to figure out data(base) size and to expect to have this easy/automatically calculated in a on-premise Solution is not available as far as I know with my 25 Years of experience (BW) in this area, neither "an easy button".

Nevertheless:

How much data is in "Hot" memory? => SAP HANA Studio

How much data is in "Warm" memory (early offload)? => your manual retention time

How much data is in "Cold" storage(accessible through NLS or SARA-Archive)? => DBACOCKPIT for IQ, if IQ is used

[john_hawk]

Good to know. Thanks.

[RolandKramer]

Hello jhawk95337

Unfortunately, expecting "an easy button" in an on-premise solution in 2023 is not a development priority at this time.

Furthermore, to have the plan/function to get an alert when a table get's full or high loaded is already "lost in space".

However, BW(/4) and S/4 allows you to use a own defined retention time for this, but automatically is ans will be niche to have.

best regards Roland

[john_hawk]

Hi Roland,

In Summary, (Just to be clear) as of 10/12/2023, SAP has no features to answer the question in HANA, BW4HANA or BW on HANA in cloud or on premise solutions:

How much data is in "Hot" memory?

How much data is in "Warm" memory (early offload)?

How much data is in "Cold" storage(accessible through NLS or SARA-Archive)?

Please let me know if I understand the answer.

Thanks,

John Hawk

[RolandKramer]

Hello jhawk95337

In your question, you are mixing "Apples with Peaches".

inside HANA you cannot distinguisch between hot, warm, cold. This is handled either in BW(/4) via NLS or in S/4 via ILM. Furthermore, both Solutions can be consolidated to Hot and "Hot Frozen" (only reading is possible).

BW/4 allows the Straggler Concept, so that you can edit the data via BW in the secondary Database like IQ.

For more Information about the NLS and ILM Solution see the following online Documents:

- SAP BW NLS Solution

- SAP ILM based on SAP IQ Database

best regards Roland

[john_hawk]

Hi Roland,

Ok, I'll accept the there is no easy way to measure the volume of data in "cold" storage of NLS or SARA - archiving in BW. (But it would be nice!) But comparing the volume "hot" (non-early unload tables) data to "warm" (tables with early unload flag turned on) data is a reasonable ask. It's all in the same HANA/BW on HANA/BW4HANA system. Is there an easy way to get this information?

Yes, we can search for and download the tables which store the early unload flag, download the current table list with database sizes, relationally join them , and get the information desired.

But it's not easy.

Is there an Easy button for this information?

John Hawk