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