How much RAM is used by queries

[Former Member]

I need to know how to find the amount of RAM memory used by a running query.

Is there any where to find this information in HANA Studio?

If not, is there a query to get it?

Is there any way to find any statistical data about the amount of RAM used by previously executed queries?

Thanks in advance for your help.

[Former Member · Accepted solution]

Hi All,

Was just about to ask/post  this question, I think the note http://service.sap.com/sap/support/notes/1999997

is not released yet.

Just out of curiosity as the note is not released yet,
Is there a performance impact if memory tracking is enabled?

Thanks,

Razal

[Former Member · Accepted solution]

Sorry, the SAP Note is currently not available. I attached the relevant content below. So far we are not aware about significant resource consumption related to the memory_tracking feature.

11. Is it possible to monitor the memory consumption of SQL statements?

You can activate the statement memory tracking feature by setting the
following parameters:

• global.ini --> [resource_tracking] --> enable_tracking = on
• global.ini --> [resource_tracking] --> memory_tracking = on

The column MEMORY_SIZE of the expensive statement trace

(M_EXPENSIVE_STATEMENTS) will then be populated with the used memory so that you

can see the actual memory requirements of SQL statements.

12. Is it possible to limit the memory that can be allocated by a single SQL
statement?

Starting with SPS 08 you can limit the memory consumption of single SQL

statements. As a prerequisite you need to have the statement memory tracking

feature enabled as described above. Additionally you have to set the following

parameter in order to define the maximum permitted memory allocation per SQL

statement:

• global.ini --> [memorymanager] --> statement_memory_limit =
  <maximum_memory_allocation_in_gb>

You should test the effects of these settings carefully in order to avoid
unexpected results (e.g. termination of backups or critical business queries).
In general it is useful to start with a rather high memory limit, e.g. 100
GB.

A statement that exceeds the configured statement memory limit will terminate
with an OOM dump ("compositelimit_oom").

[lbreddemann]

Hi Daniel,

yes, you can see how much memory query is using as of SPS 9 via the PlanVisualization.

Also, you can use the M_EXPENSIVE_STATEMENTS monitoring view.

The SQL-Statement “HANA_SQL_ExpensiveStatements_Rev60+”  from SAP Note 1969700) displays the memory allocation (MEM_USED_GB) and allows to sort by the memory size (ORDER_BY = ‘MEMORY’).

- Lars

[Former Member]

The memory information is only accessible if statement memory tracking is enabled, see SAP Note 1999997 ("Is it possible to limit the memory that can be allocated by a single
SQL statement?") for more details.

[lbreddemann]

Spot on Martin, totally forgot about that!

Thanks for chipping in.