3 Root Causes And Solutions
3.1 Memory Problems
This section discusses the analysis steps that are required to identify and resolve memory related issues in the SAP HANA database.
For more general information on SAP HANA memory management, see the SAP HANA Administration Guide and the whitepaper SAP HANA Memory Usage Explained which discusses the memory concept in more detail.
It also explains the correlation between Linux indicators (virtual and resident memory) and the key memory usage indicators used by SAP HANA.
Further overview information can be found in SAP Note 1840954 – Alerts related to HANA memory consumption. This SAP Note provides information on how to analyze out-of-memory (OOM) dump files.
For more information on the SAP HANA alerts see the following documents:
● SAP HANA Administration Guide
○ Monitoring Overall System Status and Resource Usage
○ Monitoring System Performance
● Alerts 1 and 43: See, SAP Note 1898317 – How to Handle Alert ‘Check host free physical memory’
In order to understand the current and historic SAP HANA memory consumption you can use the following tools and approaches:
● Memory information in SAP HANA studio
● Memory information from logs and traces
● Memory information from SQL commands
● Memory information from other tools
Related Information
SAP HANA Administration Guide SAP HANA Memory Usage Explained SAP Note 1840954
SAP Note 1898317
3.1.1 Memory Information in SAP HANA Studio
There are a number of sources of information in SAP HANA studio that can assist you in understanding memory utilization.
To get high level information about physical memory, allocation limit, used memory and resident memory open Administration Overview
Open Landscape Services for high level information about physical memory, allocation limit and used memory for each service.
Open Administration Performance Load for high level history information about physical memory, allocation limit, used memory and resident memory.
From the Systems open the context menu of a system, select Configuration and Monitoring Open Memory Overview to drill-down into memory utilization (Physical Memory / SAP HANA Used Memory / table and database management memory).
Open Landscape Services and right click a service and choose Memory Allocation Statistics to drill-down into used memory grouped by different main components like “Statement Execution & Intermediate Results”
or “Column Store Tables” which are further divided by sub components:
When you choose a main component in the upper part of the screen its sub components are shown in the lower part.
Choose Show Graph to show historic information for component memory usage:
3.1.2 Memory Information from Logs and Traces
In case of critical memory issues you can often find more detailed information in logs and trace files.
● In the SAP HANA system alert trace files on the Diagnosis tab, try to identify memory-related errors.
Search for the strings “memory”, “allocat”, or “OOM” (case-insensitive).
● Check if an out-of memory (OOM) trace file was created.
● Investigate error messages seen on the application side that occurred at times of high memory usage. If the application is an SAP NetWeaver system, good starting points for analysis are System Log (SM21), ABAP Runtime Error (ST22), and Job Selection (SM37).
If help from SAP Customer Support is needed to perform an in-depth analysis of a memory-intensive SQL statement, the following information is valuable and should be added to the ticket:
● Diagnosis Information (full system info dump). To collect this information, see Diagnosis Information.
● Performance Trace provides detail information on the system behavior, including statement execution details. To enable this trace, see Performance Trace.
The trace output is written to a trace file perftrace.tpt, which must be sent to SAP Customer Support.
If specific SAP HANA system components need deeper investigation, SAP Customer Support can ask you to raise the corresponding trace levels to INFO or DEBUG. To do so, launch the Database Trace wizard and select the Show all components checkbox. Enter the search string, select the found component in the indexserver.ini file and change the System Trace Level to the appropriate values. Some trace components, for example, join_eval = DEBUG, can create many megabytes of trace information and require an increase of the values maxfiles and maxfilesize in the [trace] section of the global.ini file.
Send the indexserver trace file(s) to SAP Customer Support.
Internal details about SQL statement execution can be collected by enabling the Executor Trace. To do so, on the Configuration tab, edit the parameter trace in the [pythontrace] section of the executor.ini file and change its value to on. The Executor Trace provides the highest detail level and should only be activated for the short time of query execution.
Upload the trace file extrace.py to SAP Customer Support.
Related Information
Diagnosis Information [page 168]
Performance Trace [page 166]
3.1.3 Memory Information from SQL Commands
There are a number of ways to analyze memory usage based on pre-defined and modifiable SQL queries.
The System Information tab of SAP HANA studio provides a set of tabular views to display the memory consumption of loaded tables based on pre-defined SQL queries:
● The view Schema Size of Loaded Tables displays the aggregated memory consumption of loaded tables in MB for different database schemas. The aggregation comprises both Column Store and Row Store tables.
Order by the schema size column and find the largest consumers.
● The view Used Memory by Tables shows two values: the total memory consumption of all Column Store tables in MB and the total memory consumption of all Row Store tables in MB.
SAP Note 1969700 – SQL Statement Collection for SAP HANA contains several commands that are useful to analyze memory related issues. Based on your needs you can configure restrictions and parameters in the section marked with /* Modification section */.
The most important memory related analysis commands are in the following files:
● “HANA_Memory_Overview”: Overview of current memory information
Figure 1: Example: Overview of Current Memory Information
● “HANA_Memory_TopConsumers”: Current top memory consuming areas Figure 2: Example: Current Top Memory Consuming Areas
● “HANA_Memory_TopConsumers_History”: Historic top memory consuming areas
● “HANA_Tables_LargestTables”: Overview of current memory allocation by tables Figure 3: Current Memory Allocation by Table
Related Information
SAP Note 1969700
3.1.4 Memory Information from Other Tools
There are a number of tools available to analyze high memory consumption and out of memory situations.
Out-of-memory (OOM) dumps can be analyzed as described in SAP KBA 1984422 – Analysis of SAP HANA Out-of-memory (OOM) Dumps.
The tool hdbcons provides expert functionality to analyze memory issues. For more information see SAP Note 1786918 - Required information to investigate high memory consumption.
Related Information
SAP Note 1786918 - Required information to investigate high memory consumption SAP KBA 1984422 – Analysis of SAP HANA Out-of-memory (OOM) Dumps
3.1.5 Root Causes of Memory Problems
Once you have completed your initial analysis you have the information required to start the next phase of your analysis.
Based on the results from the analysis approaches you are now able to answer the following questions:
● Is it a permanent or a sporadic problem?
● Is the memory consumption steadily growing over time?
● Are there areas with critical memory consumption in heap, row store or column store?
● Is there a big difference between used memory and allocated memory?
In the following you can find typical root causes and possible solutions for the different scenarios.
3.1.5.1 Significant External Memory Consumption
If the database resident memory of all SAP HANA databases on the same host is significantly smaller than the total resident memory you have to check which processes outside of the SAP HANA database(s) are
responsible for the additional memory requirements.
Typical memory consumers are:
● Operating system (for example, caches, mapping structures)
● Third party tools (for example, backup, virus scanner)
How to identify top memory consumers from non-SAP HANA processes is out of scope of this guide. However, when you are able to identify the reason for the increased memory consumption of the external program you can check if it is possible to optimize its configuration.
3.1.5.2 Space Consumed by Large Tables
If particularly large tables consume significant amount of space in the row store or column store you should check if the amount of data can be reduced.
● SAP Note 706478 - Preventing Basis tables from increasing considerably describes archiving and deletion strategies for typical SAP tables with a technical background for example, required for communication, logging or administration).
● General recommendations for avoiding and reducing data can be found in the Data Management Guide available at: http://service.sap.com/ilm > Data Archiving > Media Library > Literature and Brochures For more information on memory management for resident table data, see: SAP HANA Administration Guide:
Managing Tables.
Related Information
SAP Note 706478
SAP HANA Administration Guide
3.1.5.3 Internal Columns in Column Store
For several reasons SAP HANA creates internal columns in the Column Store.
In some situations a cleanup is possible, for example, in the case of CONCAT attribute columns that were created in order to support joins.
For more information see SAP Note 1986747 – Internal Columns in Column Store .
Related Information
SAP Note 198674
3.1.5.4 Memory Leaks
A memory leak is a memory area (typically a heap allocator) that grows over time without any apparent reason.
If you have identified a suspicious area proceed as follows:
● Check for SAP Notes that describe the memory leak and provide a solution.
● Check if the problem is reproducible with a recent SAP HANA revision.
● If you can’t resolve the problem yourself, open a SAP customer message and use the component HAN-DB.
3.1.5.5 Large Heap Areas
Some heap areas can be larger than necessary without being a memory leak.
SAP Note 1840954 – Alerts related to HANA memory consumption contains an overview of heap allocators with a potentially large memory consumption and possible resolutions.
Related Information
SAP Note 1840954
3.1.5.6 Expensive SQL Statements
SQL statements processing a high amount of data or using inefficient processing strategies can be responsible for increased memory requirements.
See SQL Statement Analysis for information on how to analyze expensive SQL statements during times of peak memory requirements.
Related Information
SQL Statement Analysis [page 140]
Set a Statement Memory Limit [page 32]
3.1.5.7 Transactional Problems
High memory consumption can be caused by problems with transactions.
In some cases, high memory consumption is caused by wait situations, which can have different reasons.
● Long-running or unclosed cursors,
● Blocked transactions,
● Hanging threads.
As one of the negative impacts, used memory is not released any more. In particular, the number of table versions can grow up to more than 8,000,000 which is considered the amount where an action is required.
For more information, see Transactional Problems.
Related Information
Transactional Problems [page 30]
3.1.5.8 Used Space Much Smaller than Allocated Space
In order to optimize performance by minimizing the memory management overhead or due to fragmentation, SAP HANA may allocate additional memory rather than reusing free space within the already allocated memory.
This can lead to undesired effects that the SAP HANA memory footprint increases without apparent need.
The SAP HANA license checks against allocated space, so from a licensing perspective it is important to keep the allocated space below the license limit.
In order to limit the amount of allocated space you can set the parameter global_allocation_limit to a value not larger than the maximum memory that should be allocated
See Set the global_allocation_limit Parameter in the SAP HANA Administration Guide.
Related Information
SAP HANA Administration Guide
3.1.5.9 Fragmentation
Fragmentation effects are responsible for inefficiently used memory. They can occur in different areas.
In order to minimize fragmentation of row store tables you can proceed as follows:
● If the fragmentation of row store tables in the shared memory segments of indexserver processes reaches 30% and the allocated memory size is greater than 10GB, a table redistribution operation is needed.
SAP Note 1813245 - SAP HANA DB: Row Store reorganization describes how to determine fragmentation and perform a table redistribution.
Related Information
SAP Note 1813245
3.1.5.10 Large Memory LOBs
LOB (Large Object) columns can be responsible for significant memory allocation in the row store and column store if they are defined as memory LOBs.
To check for memory LOBs and switch to hybrid LOBs see SAP Note 1994962 – Activation of Hybrid LOBs in SAP HANA.
Related Information
SAP Note 1994962
3.1.5.11 Large Delta Store
The delta store can allocate a significant portion of the column store memory.
You can identify the current size of the delta store by running the SQL command:
“HANA_Tables_ColumnStore_Overview” (SAP Note 1969700 – SQL Statement Collection for SAP HANA). If the delta store size is larger than expected, proceed as described in the section Delta Merge.
Related Information
SAP Note 1969700 Delta Merge [page 54]
3.1.5.12 Undersized SAP HANA Memory
If a detailed analysis of the SAP HANA memory consumption didn’t reveal any root cause of increased memory requirements it is possible that the available memory is not sufficient for the current utilization of the SAP HANA database.
In this case you should perform a sizing verification and make sure that sufficient memory is installed on the SAP HANA hosts.
3.1.5.13 Set a Statement Memory Limit
The statement memory limit allows you to set a limit both per statement and per SAP HANA host.
Prerequisites
You have the system privilege INIFILE ADMIN.
Context
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.
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
You can view the (peak) memory consumption of a statement in M_EXPENSIVE_STATEMENTS.MEMORY_SIZE.
Note
M_EXPENSIVE_STATEMENTS.REUSED_MEMORY_SIZE is not used as of SPS 09.
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 .
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.
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:
ALTER USER <user_name> SET PARAMETER STATEMENT MEMORY LIMIT = <gb>
○ 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)
Note
To reset a user specific statement limit use the SQL statement:
ALTER USER <user_name> CLEAR PARAMETER STATEMENT MEMORY LIMIT
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).
Related Information
Parameters that Control Memory Consumption [page 34]
3.1.5.13.1 Parameters that Control Memory Consumption
The memorymanager section of the global.ini file contains parameters that allow you to control the memory consumption of SAP HANA.
Note
In a system that supports multitenant database containers, you can configure the global.ini at both the system level and the database level. Parameters configured at the system level apply to the complete system and all databases. Parameters configured at the database level apply to the specified database only.
Table 1:
INI file Section Parameter Default Description
global.ini memorymanager global_allocati
on_limit
A missing entry or a value of 0 results in the system using the default settings. The global allocation limit is calculated by default as follows: 90% of the first 64 GB of available physical memory on the host plus 97% of each further GB. Or, in the case of small phys
ical memory, physical memory minus 1 GB.
The global allocation limit limits the amount of memory that can be
global.ini memorymanager allocationlimit A missing entry or a value of 0 results in the system using the default settings. The default allocation limit is calculated in the same way as the de limit limits the amount of memory that can be used by individual processes. The value is the maximum allo
cation limit in MB.
INI file Section Parameter Default Description
global.ini memorymanager statement_memor
y_limit
0 (no limit) When reaching the statement memory
wise the system is not affected. The unit of measure is GB.
ory limit is applied if the current SAP HANA memory consumption exceeds the statement memory limit thresh
old as a percentage of the global allocation limit.