SAP HANA -Set a Statement Memory Limit

You can protect an SAP HANA system from excessive memory usage due to uncontrolled queries by limiting the amount of memory used by single statement executions per host. Statement executions that require more memory will be aborted when they reach the limit. By default, there is no limit set on statement memory.The statement memory limit allows you to set a limit both per statement and per SAP HANA host.


You have the system privilege INIFILE ADMIN.


  1. Enable statement memory tracking.
    In the global.ini file, expand the resource_tracking section and set the following parameters to on:
    enable_tracking = on
    memory_tracking = on
    You can view the (peak) memory consumption of a statement in M_EXPENSIVE_STATEMENTS.MEMORY_SIZE.Note
  2. Set a statement memory limit (integer values only).
    In the global.ini file, expand the memorymanager section and set the parameter statement_memory_limit .
    [box type=”info” align=”” class=”” width=””]When the statement memory limit is reached, a dump file is created with “compositelimit_oom” in the name. The statement is aborted, but otherwise the system is not affected. By default only one dump file is written every 24 hours. If a second limit hits in that interval, no dump file is written. The interval can be configured in the memorymanager section of the global.ini file using the oom_dump_time_delta parameter, which sets the minimum time difference (in seconds) between two dumps of the same kind (and the same process). Statements that exceed the limit you have set on a host are stopped by running out of memory[/box]
  3. (Optional) Set a user specific statement limit.
    To exclude certain users from the statement memory limit (for example, to ensure an administrator is not prevented from doing a backup) use the SQL statement:

    If both a global and a user statement memory limit are set, the user specific limit takes precedence, regardless of whether it is higher or lower than the global statement memory limit.
    If the user specific statement memory limit is removed the global limit takes effect for the user.
    Setting the statement memory limit to 0 will disable any statement memory limit for the user.
    The user specific statement memory limit is active even if resource_tracking is disabled.
    The parameter is shown in USER_PARAMETERS (like all other user parameters)
    [box type=”note” align=”” class=”” width=””]To reset a user specific statement limit use the SQL statement: ALTER USER CLEAR PARAMETER STATEMENT MEMORY LIMIT[/box]

  4. You can set a threshold for statement memory limit.
    In the global.ini file, expand the memorymanager section and set the parameter statement_memory_limit_threshold

    The statement memory limit will only take effect if the total memory used in the system (as per the global_allocation_limit parameter) is above the set threshold (in %).
    This means the statement_memory_limit parameter is taken into account only when total memory usage reaches the threshold. No statements have to be canceled if total memory is below the threshold. This allows expensive statements that consume more than the allowed statement memory limit to finish successfully during periods when a system runs under no load (for example, during the night).

Leave a Reply