memory utilization for each tables in a schema

[Former Member]

Hi,

Is there a way we can find the memory utilization for each tables for a particular schema in HANA.

Thank you

Jonu

[vincenzocappelluti]

Check this:

select table_name as "Table name",

round(disk_size/1024/1024, 2) as "Disk size in MB"

from M_TABLE_PERSISTENCE_STATISTICS

where schema_name='<name_of_schema>'

and table_name in('<list_of_tables>') --optional

order by disk_size desc;

Cheers.

[former_member182302]

Adding to Vivek's reply,

for other system information related tables, have a look on the below blog:

TOTAL_SIZE_IN_MEMORY ( Main + Delta ) from M_CS_TABLES will give you the exact information you are looking out for.

Regards,

Krishna Tangudu

[Former Member]

thank you all, i have a query like this now to get the memory utilization of all column tables in schema SAP_ECC, do i need to also check if the tables are loaded, how do we find the memory usage if the table is not loaded .

select  sum(round(MEMORY_SIZE_IN_TOTAL/1024/1024, 2)) as "MB" FROM  M_CS_TABLES WHERE SCHEMA_NAME = 'SAP_ECC'

[vivekbhoj]

Hi Jonu,

If table is not loaded or partially loaded, then you can get its size on disk by:

Open Table Definition and go to Runtime Information -> it will show you size of table in disk

Don't know if any View exists for it, will check it

Regards,

Vivek

[former_member182302]

Hi Jonu,

Adding to Vivek's reply,

Is your question on how much memory is consumed in Main & Delta Memory? if you see the table details properly:

MEMORY_SIZE_IN_TOTAL     Total memory size is the sum of memory size in main, delta, and history parts

MEMORY_SIZE_IN_MAIN     Current memory consumption in main; this value varies depending on the number of attributes actually loaded

MEMORY_SIZE_IN_DELTA     Current memory consumption in delta

If you want to check the size on the disk,

SELECT DISK_SIZE FROM M_TABLE_PERSISTENCE_STATISTICS WHERE SCHEMA_NAME = <yourschema> and TABLE_NAME = <yourtablename>

Regards,

Krishna Tangudu

[vivekbhoj]

Hi Jonu,

You can get info of all column tables and their memory utilization from M_CS_TABLES View

You can get info of all row tables and their memory utilization from M_RS_TABLES View

To find all tables for a particular schema, just filter that schema

SELECT  * FROM "SYS"."M_CS_TABLES" WHERE SCHEMA_NAME = '<YOURSCHEMANAME>'

These Monitoring views are present in SYS schema

Regards,

Vivek