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.
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)

  • Connect to HANA system and open SQL console in HANA studio
  • 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>
SAP HANA statement_memory_limit
If you want to disable this parameter , simply remove the parameter.

Starting SP09 we got flexibility this parameter in accordance of percentage of global allocation limit. Below parameter  make sure that the statement_memory_limit only takes effect if the overall SAP HANA memory allocation exceeds a defined percentage of the global allocation limit:

global.ini > [memorymanager] > statement_memory_limit_threshold = <percentage_of_global_allocation_limit>

Reference :