SQL for Hana Memory Usage Checks

Total Memory Used:

SELECT round(sum(TOTAL_MEMORY_USED_SIZE/1024/1024)) AS "Total Used MB" FROM M_SERVICE_MEMORY;

---with Host Detail---

select HOST,
round(INSTANCE_TOTAL_MEMORY_USED_SIZE/1024/1024/1024,2) as "Used Memory GB" from M_HOST_RESOURCE_UTILIZATION

---with Component Detail---

SELECT host,component,
sum(used_memory_size/1024/1024/1024) as "used_mem_size"
FROM M_SERVICE_COMPONENT_MEMORY group by host,component ORDER BY sum(used_memory_size) desc

Used Memory over time :

For instance, to see the peak amount of Used Memory since the server was restarted:

select top 1 HOST, SERVER_TIMESTAMP,round(INSTANCE_TOTAL_MEMORY_USED_SIZE/1024/1024/1024, 2) as "Peak Used GB" from _SYS_STATISTICS.HOST_RESOURCE_UTILIZATION_STATISTICS order by "Peak Used GB" desc

Unloads Detail:

For instance, the following query will provide the number of unloads during a particular one-hour time-slot:

select count(*) from M_CS_UNLOADS where UNLOAD_TIME between '19.08.2013 09:00:00' and '19.08.2013 10:00:00'

Allocation Limit :

select host ,round(ALLOCATION_LIMIT/1024/1024/1024,2) as "Allocation Limit GB"  from
PUBLIC.M_HOST_RESOURCE_UTILIZATION

Code and Stack Size:

SELECT round(sum(CODE_SIZE+STACK_SIZE)/1024/1024) AS "Code+stack MB" FROM SYS.M_SERVICE_MEMORY;

Total Memory Consumption of All Column Tables:

SELECT round(sum(MEMORY_SIZE_IN_TOTAL)/1024/1024) AS "Column Tables MB" FROM M_CS_TABLES;

Total Memory Consumption of All Row Tables:

SELECT round(sum(USED_FIXED_PART_SIZE +USED_VARIABLE_PART_SIZE)/1024/1024) AS "Row Tables MB" FROM M_RS_TABLES;

Total Memory Consumption of All Column Tables by Schema:

SELECT SCHEMA_NAME AS "Schema",round(sum(MEMORY_SIZE_IN_TOTAL) /1024/1024) AS "MB" FROM M_CS_TABLES GROUP BY SCHEMA_NAME ORDER BY "MB" DESC;

Physical and Resident Memory:

To display the size of the Physical Memory and Resident part, you can use the following SQL command:

select HOST, round((USED_PHYSICAL_MEMORY + FREE_PHYSICAL_MEMORY)/1024/1024/1024, 2) as "Physical Memory GB", round(USED_PHYSICAL_MEMORY/1024/1024/1024, 2)   as "Resident GB" from PUBLIC.M_HOST_RESOURCE_UTILIZATION

Heap and Shared memory:

heap and shared memory has been determined.

SELECT TOP 3 HOST, PORT, SERVICE_NAME, HEAP_MEMORY_USED_SIZE, SHARED_MEMORY_USED_SIZE, TOTAL_MEMORY_USED_SIZE

FROM M_SERVICE_MEMORY ORDER BY SHARED_MEMORY_USED_SIZE DESC

  
 Memory Consumption of Index server:

SELECT round (value / 1024 / 1024 /1024 , 2) as "memory size indexserver in GB" FROM "SYS"."M_MEMORY" where port = '30203' and name = 'TOTAL_MEMORY_SIZE_IN_USE';

---with host & category Detail---

SELECT TOP 15 MS.HOST, MS.SERVICE_NAME,MH.CATEGORY, MH.INCLUSIVE_SIZE_IN_USE, MH.EXCLUSIVE_SIZE_IN_USE FROM M_HEAP_MEMORY MH, M_SERVICES MS WHERE MH.PORT = MS.PORT AND MH.HOST = MS.HOST AND

MS.SERVICE_NAME ='indexserver'