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 .

COMMAND:

////////////////////

with cte as (

select

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

from

m_connections c, m_prepared_statements s

where

s.connection_id = c.connection_id and c.connection_status != ‘IDLE’

order by

s.allocated_memory_size desc)

select distinct USER_NAME,SUM(UMem_GB) from cte group by USER_NAME;

//////////////////////////

Sample output:

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.

Thanks for reading!
Follow for more such posts!
Like and leave a comment or suggestion if any!