How to find HANA disk size,memory size,record count,memory usage of COLUMN TABLES in HANA
Simple but Important commands in HANA DB on M_TABLE_PERSISTENCE_STATISTICS Vs M_TABLES Vs M_CS_TABLES
A.Different use cases of using M_TABLE_PERSISTENCE_STATISTICS
1.Wanted to find the disk size of your HANA DB
select sum((disk_size)/1024/1024/1024) from m_table_persistence_statistics;
NOTE: Below output is in GB.
2.Wanted to find the disk size of a specific table in HANA DB. ( You can either use this query or also use studio-catalog->display->runtime information) . Below we are trying to find the list of table name and its disk size with format /BIC/B*
3.Other use cases can be like using size for specific schema.
B.Different use cases of using M_TABLES
1.Wanted to find RECORD_COUNT of any specific table along with disk size:
2.Wanted to find out disk size occupied by specific schema:
select distinct schema_name,sum((disk_size)/1024/1024/1024) as disk_GB from m_table_persistence_statistics group by schema_name,disk_size order by disk_GB;
C.Different use cases of M_CS_TABLES with respect to memory consumption.
NOTE: The memory usage in M_CS_TABLES are real time memory usage and it can vary time to time as either a column store table can be loaded fully,partially or not loaded at all.
Tables that are not currently in memory will have memory usage as -1. If you want to have more details on how to unload and load tables fully and partially , check out by below blog.
How to perform manual Load and unload of column store tables in HANA
HANA store LOADS and UNLOADS
1.Wanted to find the memory usage of any specific table (partitioned or non-partitioned)
2.Wanted to find the total memory consumption by column store tables in HANA DB:
select sum(memory_size_in_total) from M_CS_TABLES;
Note: Below output is in bytes. To get in GB use below.
select sum(memory_size_in_total)/1024/1024/1024 from M_CS_TABLES;
3.Wanted to find out the delta memory usage and main memory usage of any specific table ?
select host, count(*), round(sum(memory_size_in_total/1024/1024/1024)) as size_GB from m_cs_tables group by host order by host
5.Wanted to find out list of top most memory consumer table in HANA DB?
Here I wish to find out the list of tables which consumes more than 100 GB
with CST AS (
select SCHEMA_NAME,TABLE_NAME,ROUND(SUM(MEMORY_SIZE_IN_TOTAL/1024/1024/1024)) as memGB
group by SCHEMA_NAME,TABLE_name)
select * from CST
where memGB > 100
order by memGB desc
Please do keep me posted if you find any new important use cases on these tables.
Thanks for reading!
Follow for more such posts by clicking here and FOLLOW : https://people.sap.com/rajarajeswari_kaliyaperumal
Like and leave a comment or suggestion if any!