How to limit query from consuming all HANA memory

By default , single SAP HANA query can consume 100% of available memory. If you want to safeguard your system from uncontrolled expensive queries then it’s a good idea to limit the memory consumption of single statement per host.

Starting HANA SP08 SAP introduced parameter statement_memory_limit to restrict the query at predefined memory consumption limit.

Queries will be aborted with OOM dump ("compositelimit_oom OR [MEMORY_LIMIT_VIOLATION] ") , if it reaches the limit specified by this parameter – statement_memory_limit .

Requirement: You want to limit query from consuming limitless HANA memory.

Prerequisite: You need database user with INIFILE ADMIN system privilege assigned.You also need statement memory tracking feature enabled. You need to change following parameters , these parameters do not need restart.

Enable Resource Tracking

a. Login into HANA Studio and navigate to the "Configuration" tab.
b. Open "Global.ini–>resource_tracking"
c. Set parameter "enable Tracking" and "memory_tracking" to "on"


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



This will populate the MEMORY_SIZE column of expensive statement trace( M_EXPENSIVE_STATEMENTS)

Step:

1> Connect to HANA system and open SQL console in HANA studio
2> Execute following SQL

alter system alter configuration ('global.ini','SYSTEM') SET ('memorymanager', 'statement_memory_limit') = 100 with reconfigure; // This SQL will set the limit to 100 GB.



OR change following parameter.

Administration perspective > Configuration > global.ini > [memorymanager] -> statement_memory_limit = (maximum_memory_allocation_in_gb)

Example of memory cap encountered:

a. A SQL statement was executed which required more the 5 GIGs of working memory which was the CAP set for the test user. The below error message is given to the user.



b. An addition a OOM file diagnosis file is generated at the time the memory limit is encountered.