Skip to Content
Technical Articles

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.

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
https://blogs.sap.com/2020/09/12/how-to-perform-manual-load-and-unload-of-column-store-tables-in-hana/

HANA store LOADS and UNLOADS

https://blogs.sap.com/2020/09/11/hana-column-store-load-and-unloads-and-sqls

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=’SAPSSS’;

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.

 

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!

2 Comments
You must be Logged on to comment or reply to a post.