How to find which user is consuming maximum memory in HANA
User with maximum memory consumption in HANA:
During performance issues, if we are able to narrow down the maximum memory consumption is with respect to Statement Execution rather than column store, below command will help us to detect which user is consuming maximum memory.
NOTE: This user can either be running 1 or more than 1 statements and hence this is just the SUM of memory consumed by the user’s statements as such .
with cte as (
c.host, c.user_name,c.connection_id, c.connection_status, c.transaction_id, s.last_executed_time,
round(s.allocated_memory_size/1024/1024/1024,2) as “Alloc Mem (GB)”,
round(s.used_memory_size/1024/1024/1024,2) as UMem_GB, s.statement_string
m_connections c, m_prepared_statements s
s.connection_id = c.connection_id and c.connection_status != ‘IDLE’
select distinct USER_NAME,SUM(UMem_GB) from cte group by USER_NAME;
TIPS: To find out the HANA memory distribution , navigate to below path.
HANA studio-> System information -> type mem and double click as shown below.
In below statement execution is consuming more memory and hence it is useful to narrow down to user who is executing these statements and its consumption.