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*

select TABLE_NAME, DISK_SIZE from M_TABLE_PERSISTENCE_STATISTICS where SCHEMA_NAME = ‘SAPSSS’ and TABLE_NAME like ‘/BIC/B%’ order by DISK_SIZE desc

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:

select * from M_TABLES where TABLE_NAME = ‘CDPOS’ and SCHEMA_NAME=’SAPEDS’;

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.

1. Wanted to find the memory usage of any specific table (partitioned or non-partitioned)

select * from M_CS_TABLES where TABLE_NAME=’CDPOS’ and SCHEMA_NAME=’SAPERP’;

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 sum(memory_size_in_total)/1024/1024/1024 as TotalMemGB,sum(memory_size_in_main)/1024/1024/1024 as TotalMain,sum(memory_size_in_delta)/1024/1024/1024 as TotalDelta from M_CS_TABLES where TABLE_NAME=’CDPOS’ and SCHEMA_NAME=’SAPSSS’;

4. Wanted to find out host level memory consumption in your scale out node.

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

from M_CS_TABLES

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.