Skip to Content
Technical Articles
Author's profile photo Elijah Ng

SQL query to check running statements/procedures in HANA memory and their memory consumption

Hi,

 

Here’s a handy SQL that I often use to check what statements or procedures are currently running in HANA memory and how much memory each of them is consuming and how long they have been running ordered by the highest consuming first.


select
c.host, c.user_name, 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 "Used Mem (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
;

 

Hope this can be helpful to everyone.

 

-Elijah

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Matt Harding
      Matt Harding

      Thanks Elijah. Nice query to keep in the back pocket for reference.

      BTW - Pretty obvious but if anyone uses the above SQL, make sure you convert the quotes as they have been "Wordified"...

      Cheers,

      Matt

      Author's profile photo Elijah Ng
      Elijah Ng
      Blog Post Author

      Thanks, Matt.  I've updated the SQL to correct the quotes.

      Author's profile photo Stewart Tan
      Stewart Tan

      Hi.

      Shouldn't the query be on M_ACTIVE_STATEMENTS rather than M_PREPARED_STATEMENTS?  The existing query seems to retrieve statements that have executed in the past.

      Thanks for the query, it is quite useful.

      BR, Stewart