how to check Memory Usage in HANA

Here, I explained about how to check Memory Usage detail in hana database using sql queries.

1- How to find out the top 100 memory usage in HANA database between a specified date and time .

select top 100 round(TOTAL_MEMORY_USED_SIZE/1024/1024/1024, 2) as
"Used Memory GB", HOST, SERVER_TIMESTAMP  from _SYS_STATISTICS.HOST_SERVICE_MEMORY where SERVER_TIMESTAMP between '14.04.2016 08:00:00' and '16.04.2016 16:00:00' order by TOTAL_MEMORY_USED_SIZE desc

2- How to check the peak memory usage .

select top 1 HOST, SERVER_TIMESTAMP, round(TOTAL_MEMORY_USED_SIZE/1024/1024/1024, 2) as "Used Memory GB" from _SYS_STATISTICS.HOST_SERVICE_MEMORY where SERVICE_NAME = 'indexserver'
order by TOTAL_MEMORY_USED_SIZE desc

3- How to find the schema name, current memory usage, max memory usage (if all tables and all their columns were loaded into memory simultaneously) and record count in each schema .

select schema_name, round(sum(memory_size_in_total/1024/1024/1024),1) as "Current MEM GB",
round(sum(estimated_max_memory_size_in_total/1024/1024/1024),1) as "MAX MEM GB",
sum(record_count)  from "SYS"."M_CS_TABLES"  group by schema_name  order by "Current MEM GB" desc

4- How to get the total table count in a schema.

SELECT  count(*) "TABLE_NAME" FROM "SYS"."M_TABLES" where "SCHEMA_NAME" = 'SAPHP'

5- How to get the total memory usage of a schema .

For column tables -

SELECT TABLE_NAME AS "Table",sum(round(MEMORY_SIZE_IN_TOTAL/1024/1024, 2)) as "MB" FROM  M_CS_TABLES WHERE SCHEMA_NAME = 'HANADBXY'

For Row tables -

select round(sum(USED_FIXED_PART_SIZE + USED_VARIABLE_PART_SIZE)/1024/1024) as "Row Tables MB Used" from M_RS_TABLES where "SCHEMA_NAME" = 'HANADBXY'