## 3.1.5.13  Setting a Memory Limit for SQL Statements
You can set a statement memory limit to prevent single statements from consuming too much memory.
## Prerequisites
To apply these settings you must have the system privilege INIFILE ADMIN.
For these options, enable_tracking and memory_tracking must first be enabled in the global.ini file. Additionally, resource_tracking must be enabled in this file if you wish to apply different settings for individual users (see Procedure below).
## Context
You can protect an SAP HANA system from uncontrolled queries consuming excessive memory by limiting the amount of memory used by single statement executions per host. By default, there is no limit set on statement memory usage, but if a limit is applied, statement executions that require more memory will be aborted when they reach the limit. To avoid canceling statements unnecessarily you can also apply a percentage threshold value which considers the current statement allocation as a proportion of the global memory currently available. Using this parameter, statements which have exceeded the hard-coded limit may still be executed if the memory allocated for the statement is within the percentage threshold. The percentage threshold setting is also effective for workload classes where a statement memory limit can also be defined.
You can also create exceptions to these limits for individual users (for example, to ensure an administrator is not prevented from doing a backup) by setting a different statement memory limit for each individual.
These limits only apply to single SQL statements, not the system as a whole. Tables which require much more memory than the limit applied here may be loaded into memory. The parameter global_allocation_limit limits the maximum memory allocation limit for the system as a whole.
You can view the (peak) memory consumption of a statement in m_EXPENSIVE_STATEMENTS.MEMORY_SIZE.
## Procedure
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
2. statement_memory_limit - defines the maximum memory allocation per statement in GB. The parameter is not set by default.
- In the global.ini file, expand the memorymanager section and locate the parameter. Set an integer value in GB between 0 (no limit) and the value of the global allocation limit. Values that are too small can block the system from performing critical tasks.
- An option is also available to apply a limit in relation to the system's effective allocation limit. To do this, set the parameter to a value of '-1'. In this case a memory limit is applied calculated as 25% of the smaller of the global_allocation_limit and the process_allocation_limit.
- A value for this parameter may also be set during installation or upgrade by hdblcm.
- The value defined for this parameter can be overridden by the corresponding workload class property STATEMENT_MEMORY_LIMIT.
- 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).
After setting this parameter, statements that exceed the limit you have set on a host are stopped by running out of memory.
3. statement_memory_limit_threshold - defines a percentage of the global allocation limit. Parameter statement_memory_limit is respected only if total used memory exceeds this threshold. The default value is 0% (of the global_allocation_limit) so statement_memory_limit is always respected.
- In the global.ini file, expand the memorymanager section and set the parameter as a percentage of the global allocation limit.
- This parameter provides a means of controlling when the statement_memory_limit is applied. If this parameter is set, when a statement is issued the system will determine if the amount of memory it consumes exceeds the defined percentage value of the overall global_allocation_limit parameter setting. The statement memory limit is only applied if the current SAP HANA memory consumption exceeds this statement memory limit threshold as a percentage of the global allocation limit.
- This is a way of determining if a particular statement consumes an inordinate amount of memory compared to the overall system memory available. If so, to preserve memory for other tasks, the statement memory limit is applied and the statement fails with an exception.
- Note that the value defined for this parameter also applies to the workload class property STATEMENT_MEMORY_LIMIT.
4. total_statement_memory_limit - a value in gigabytes to define the maximum memory available to all statements running on the system. The default value is 0 (no limit).
- This limit does not apply to users with the administrator role SESSION ADMIN or WORKLOAD ADMIN who need unrestricted access to the system. However, a check of the user's privileges allowing the administrator to by-pass the limit is only made for the first request when a connection is made. The privileged user would have to reconnect to be able to bypass the statement memory limit again (see also Admission Control).
- The value defined for this parameter cannot be overridden by the corresponding workload class property TOTAL_STATEMENT_MEMORY_LIMIT.
- There is a corresponding parameter for use with system replication on an Active/Active (read enabled) secondary server. This is required to ensure that enough memory is always available for essential log shipping activity. See also sr_total_statement_memory_limit in section Memory Management .
5. User parameters can limit memory for statements. For further information, refer to Setting User Parameters for Workload .
## Results
The following example and scenarios show the effect of applying these settings:
Example showing statement memory parameters
| Parameter               | Value                                                                                                                                                                                                                        |
|-------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Physical memory         | 128GB                                                                                                                                                                                                                        |
| global_allocation_limit | The unit used by this parameter is MB. The default value is: 90%of the first 64 GBof available physical memory on the host plus 97%of each further GB; or, in the case of small physical memory, physical memory minus 1 GB. |
| Parameter                        | Value                                        |
|----------------------------------|----------------------------------------------|
| statement_memory_limit           | 1 GB(the unit used by this parameter is GB.) |
| statement_memory_limit_threshold | 60%                                          |
## Scenario 1 :
A statement allocates 2GB of memory and the current used memory size in SAP HANA is 50GB.
- 0,9 * 128GB = 115,2 (global allocation limit)
- 0,6 * 115,2 = 69,12 (threshold in GB)
- 50 GB &lt; 69,12 GB (threshold not reached)
The statement is executed, even though it exceeds the 1GB statement_memory_limit .
## Scenario 2 :
A statement allocates 2GB and the current used memory size in SAP HANA is 70GB
- 70 GB >= 69,12 GB (threshold is exceeded)
The statement is cancelled, as the threshold is exceeded, the statement_memory_limit is applied.
## Related Information
Change the Global Memory Allocation Limit
Memory Management Setting User Parameters for Workload Managing Peak Load (Admission Control)