Skip to Content
Technical Articles
Author's profile photo Rajarajeswari Kaliyaperumal
Rajarajeswari Kaliyaperumal
September 21, 2020 3 minute read

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!

Assigned Tags

      11 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Harshil Shah
      Harshil Shah

      It's very informative blog Raji. It certainly help us to find out many information during disk space and memory issue.

      Author's profile photo Vishwanath Shastri
      Vishwanath Shastri

      Informative blog , with insights

      Author's profile photo Cloud Monitor
      Cloud Monitor

      how to find DB growth on monthly basis in HANA studio

      Author's profile photo Kevin Engelkamp
      Kevin Engelkamp

      Take a monthly snapshot of this query below using a SQL procedure or BODS job.

      select CURRENT_DATE as SnapshotDate,
      t.SCHEMA_NAME, t.TABLE_NAME,
      mt2.RECORD_COUNT,
      mt2.TABLE_SIZE, sum(t.FIXED_PART_SIZE),
      ((sum(mcst.memory_size_in_total))/1024/1024/1024),
      t.TABLE_OID,
      mt.INSERT_COUNT,
      mt.DELETE_COUNT,
      mt.UPDATE_COUNT,
      mt.REPLACE_COUNT,
      mt.MERGE_COUNT,
      mt.SELECT_COUNT,
      mt.LAST_MODIFY_TIME,
      mt.LAST_SELECT_TIME

      from tables t

      left outer join M_TABLE_STATISTICS mt
      on t.SCHEMA_NAME = mt.SCHEMA_NAME AND t.TABLE_NAME = mt.TABLE_NAME

      left outer join M_TABLES mt2
      on t.SCHEMA_NAME = mt2.SCHEMA_NAME AND t.TABLE_NAME = mt2.TABLE_NAME

      left outer join M_CS_TABLES mcst
      on t.SCHEMA_NAME = mcst.SCHEMA_NAME AND t.TABLE_NAME = mcst.TABLE_NAME

      where t.schema_name like '%'

      group by t.SCHEMA_NAME, t.TABLE_NAME, mt2.RECORD_COUNT, mt2.TABLE_SIZE, t.TABLE_OID, mt.INSERT_COUNT, mt.DELETE_COUNT, mt.UPDATE_COUNT, mt.REPLACE_COUNT, mt.MERGE_COUNT, mt.SELECT_COUNT, mt.LAST_MODIFY_TIME, mt.LAST_SELECT_TIME

      Order by mt2.TABLE_SIZE DESC

      Author's profile photo sundar chelladurai
      sundar chelladurai

      Hello Raji

       

      It is very useful Blog..Much appreciated.

      Thanks.

       

      Regards

      Sundar.C

      Author's profile photo Iftikhar Khan
      Iftikhar Khan

      This Blog resolve my couple issues. Thank you!!!

      Author's profile photo Harishankar K
      Harishankar K

      This is much easier and totally works !

      https://answers.sap.com/answers/13393853/view.html

      Author's profile photo Vikram Ramanathan
      Vikram Ramanathan

      Very useful! Thanks.

      Author's profile photo Vivek Chaudhary
      Vivek Chaudhary

      Nice blog

      Author's profile photo Alex Bernaards
      Alex Bernaards

      Thanks Raja  ;o)

      Author's profile photo Jose De La Cruz
      Jose De La Cruz

      A very informative blog.

      Is there a way to find out how much memory a particular job is consuming by querying HANA tables?

      In other words, is it possible to sort and have a list, in HANA, for the top memory consuming jobs running in a  system?

      Thanks,

      Jose