Memory Usage in the SAP HANA Database Environment
Memory is a fundamental resource of the SAP HANA database. Understanding how the SAP HANA database requests, uses, and manages this resource is crucial to understanding SAP HANA.
SAP HANA provides a variety of memory usage indicators that allow for monitoring, tracking, and alerting. The most important indicators are used memory and peak used memory. Since SAP HANA contains its own memory manager and memory pool, external indicators such as the size of resident memory at host level and the size of virtual and resident memory at process level can be misleading when you are estimating the real memory requirements of an SAP HANA deployment.
You can find detailed information about memory consumption of individual SAP HANA components and executed operations on the SAP HANA Performance Monitor - Memory application.
Note
See SAP Note 1704499 - "System Measurement for License Audit" for more information about memory consumption with regards to SAP HANA licenses.Virtual, Physical, and Resident Memory
On a typical SAP HANA appliance, the resident memory part of the operating system and all other running programs usually does not exceed 2 GB. The rest of the memory is therefore dedicated to SAP HANA.
When memory is required for table growth or for temporary computations, the SAP HANA code obtains it from the existing memory pool. When the pool cannot satisfy the request, the SAP HANA memory manager will request and reserve more memory from the operating system. At this point, the virtual memory size of SAP HANA processes grows.
Once a temporary computation completes or a table is dropped, the freed memory is returned to the memory manager, which recycles it to its pool without informing the operating system. Therefore, from SAP HANA's perspective, the amount of used memory shrinks, but the process’s virtual and resident memory sizes are not affected. This creates a situation where the used memory value may shrink to below the size of SAP HANA's resident memory. This is normal.
Note
The memory manager may also choose to return memory back to the operating system, for example, when the pool is close to the allocation limit and contains large unused parts.SAP HANA Memory Usage and the Operating System
Because of the way SAP HANA manages memory, the relationship between Linux memory indicators and SAP HANA's own memory indicators may not correlate as expected.
From the perspective of the Linux operating system, SAP HANA is a collection of separate processes. Linux programs reserve memory for their use from the Linux operating system. The entire reserved memory footprint of a program is referred to as its virtual memory. Each Linux process has its own virtual memory, which grows when the process requests more memory from the operating system, and shrinks when the process relinquishes unused memory. You can think of virtual memory size as the memory amount that the process has requested (or allocated) from the operating system, including reservations for its code, stack, data, and memory pools under program control.
Note
SAP HANA really consists of several separate processes, so the figure shows all SAP HANA processes combined.SAP HANA Used Memory
The total amount of memory used by SAP HANA is referred to as used memory. It includes program code and stack, all data and system tables, and the memory required for temporary computations.
SAP HANA consists of a number of processes running in the Linux operating environment. Under Linux, the operating system is responsible for reserving memory to all processes. When SAP HANA starts up, the operating system reserves memory for the program code (sometimes called the text), the program stack, and static data. It then dynamically reserves additional data memory when requested by the SAP HANA memory manager. Dynamically allocated memory consists of heap memory and shared memory.
Because the code and program stack size are about 6 GB, almost all of the used memory is used for table storage, computations, and database management.
Memory is a fundamental resource of the SAP HANA database. Understanding how the SAP HANA database requests, uses, and manages this resource is crucial to understanding SAP HANA.
SAP HANA provides various memory usage indicators that enable monitoring, tracking, and alerting. The most important indicators are those for used memory and peak used memory. SAP HANA contains its own memory manager and memory pool. Certain external indicators can be misleading when estimating the real memory requirements of an SAP HANA deployment. Examples of these indicators include the size of resident memory at the host level, and the size of virtual and resident memory at the process level.
Service Used Memory
An SAP HANA system consists of multiple services that all consume memory, in particular the indexserver service, the main database service. The index server holds all the data tables and temporary results, and therefore dominates SAP HANA used memory.
Peak Used Memory
Ultimately, it is more important to understand the behavior of used memory over time and under peak loads. For this purpose, SAP HANA has a special used memory indicator called peak used memory. Because the value for used memory is a current measurement, peak used memory allows you to keep track of the maximum value for used memory over time.
You can also reset peak used memory. This can be useful if you want to establish the impact of a certain workload on memory usage. So for example, you can reset peak used memory, run the workload, and then examine the new peak used memory value.
Memory Usage of Tables
The dominant part of the used memory in the SAP HANA database is the space used by data tables. Separate measurements are available for column-store tables and row-store tables.
Note
The SAP HANA database loads column-store tables into memory column by column only upon use. This is sometimes called "lazy loading". This means that columns that are never used will not be loaded and memory waste is avoided.
When the SAP HANA database runs out of allocatable memory, it will try to free up some memory by unloading unimportant data (such as caches) and even table columns that have not been used recently. Therefore, if it is important to measure precisely the total, or worst-case, amount of memory used for a particular table, it is important to ensure that the table is first fully loaded into memory. You can do this by loading the table into memory.
Memory Usage of Expensive Statements
Every query and statement consumes memory, for the evaluation of the statement plan, caching, and, mainly the calculation of intermediate and final results. While many statement executions use only a moderate amount of memory, some queries, for instance using unfiltered cross joins, will tax even very large systems.
Expensive statements are individual SQL statements whose execution time exceeded a configured threshold. The expensive statements trace records information about these statements for further analysis. If in addition to activating the expensive statements trace, you enable per-statement memory tracking, the expensive statements trace will also show the peak memory size used to execute expensive statements.
It is possible to further protect an SAP HANA system against excessive memory usage due to uncontrolled queries by limiting the amount of memory used by single statement executions per host.
Memory Allocator Statistics
Detailed information about memory consumption can be found by looking into allocator statistics.
Allocator statistics track the memory consumption of individual components and operations in SAP HANA and may help you to analyze issues related to memory consumption. Statistics are saved in the system views M_HEAP_MEMORY (allocated memory by component) and M_CONTEXT_MEMORY (allocated memory that can be associated with a connection, a statement, or a user). Both views have a reset feature so that statistics can be captured for a specific period of time. The embedded statistics service also includes a view which tracks memory allocation per host, HOST_HEAP_ALLOCATORS.
Note
For full details of these views, see the SAP HANA SQL and System Views Reference.Allocator statistics are saved automatically for each core processor and in certain scenarios where systems have a large number of logical cores the statistics can consume a significant amount of memory. To save memory statistics logging can be reduced, to save statistics only for each node, or only for each statistics object. An example of using the lscpu command to retrieve details of the physical and logical CPU architecture is given in the section Controlling CPU Consumption.
You can configure this feature by setting values for the following two configuration parameters in the global.ini file:
- The parameter pool_statistics_striping can reduce the amount of memory consumed by the component-specific allocator statistics (rows in M_HEAP_MEMORY with the category PoolAllocator).
- The parameter composite_statistics_striping can reduce the amount of memory consumed by statement-specific allocator statistics (rows in M_CONTEXT_MEMORY).
The parameters can be set to one of the following values (the configuration can be changed online, but the change will only affect newly created statistic objects):
| Value | Effect |
|---|---|
| auto (default value) | Let the system decide the statistics strategy. By default SAP HANA will try to utilize as much memory as possible for maximum performance. |
| core | The system allocates one stripe per logical core. |
| numa | The system allocates only one stripe per NUMA node. |
| none | In this case, the system creates a single stripe per statistics object. |
Allocated Memory Pools and Allocation Limits
SAP HANA, across its different processes, reserves a pool of memory before actual use. This pool of allocated memory is preallocated from the operating system over time, up to a predefined global allocation limit, and is then efficiently used by SAP HANA as needed.
SAP HANA preallocates and manages its own memory pool, used for storing in-memory table data, thread stacks, temporary results, and other system data structures. When more memory is required for table growth or temporary computations, the SAP HANA memory manager obtains it from the pool. When the pool cannot satisfy the request, the memory manager increases the pool size by requesting more memory from the operating system, up to a predefined allocation limit.
The global_allocation_limit Parameter
The global_allocation_limit parameter is used to limit the amount of memory that can be used by the SAP HANA database. The unit for this parameter is MB.
The default value is 0, in which case the global allocation limit is calculated as follows:
- Memory rule 1: <= 10GB – physical memory on the host minus 1 GB
- Memory rule 2: >10GB to <= 64GB – 90% physical memory on the host
- Memory rule 2: > 64GB – Memory rule 2 + 97% of each further GB
Alternatively, you can define this limit as a flexible percentage of the available main memory size. If you enter a percentage value the precise value of the limit will be calculated automatically by the system. The percentage value is very useful when running SAP HANA in a virtual environment, if you then change the size of the vm-container where the system runs the allocation limit will automatically adjust to the correct percentage of the new vm-container size.
Note
Changing this parameter does not require a restart.
There is normally no reason to change the value of this parameter although, for example, on development systems with more than one SAP HANA system installed on a single host you could limit the size of the memory pool to avoid resource contentions or conflicts.
A change may also be necessary to remain in compliance with the memory allowance of your license if you purchased a license for less than the total amount of physical memory available. This is illustrated in the following examples:
To change the global memory allocation limit, you must do the following:
- Ensure you have the system privilege INIFILE ADMIN.
- Change the value of the global_allocation_limit parameter in the configuration file global.ini → section: memorymanager using SAP HANA Cockpit or SQL.
Caution
As of SPS04 and the introduction of the parameter configuration framework, SAP recommends not to edit the INI files directly when SAP HANA is online.If you only enter a value for the system, it is used for all hosts. For example, if you have five hosts and you set the limit to 5 GB, the database can use up to 5 GB on each host (25 GB in total). If you enter a value for a specific host, then for that host, the specific value is used and the system value is only used for all other hosts. This is only relevant for multiple-host (distributed) systems.
Service Allocation Limit
In addition to the global allocation limit, each service running on the host has an allocation limit: the service allocation limit. Given that collectively, all services cannot consume more memory than the global allocation limit, each service has what is called an effective allocation limit. The effective allocation limit of a service specifies how much physical memory a service can consume in reality, considering the current memory consumption of other services.
What Happens When the Allocation Limit is Reached?
Memory is a finite resource. Once the allocation limit is reached and the pool is exhausted, the memory manager can no longer allocate memory for internal operations without first giving up something else. Buffers and caches are released, and column store tables are unloaded, column-by-column, based on a least-recently-used order, up to a preset lower limit. When tables are partitioned over several hosts, this is managed on a host-by-host basis, that is, column partitions are unloaded only on hosts with an acute memory shortage.
Table (column or partition) unloading is generally not a good situation since it leads to performance degradation later when the data must be reloaded for queries that need them. You can identify pool exhaustion by examining the M_CS_UNLOADS system view.
However, it is still possible that the memory manager needs more memory than is available, leading to an out-of-memory failure. This may happen, for example, when too many concurrent transactions use up all memory, or when a particularly complex query performs a cross join on very large tables and creates a huge intermediate result that exceeds the available memory.
Memory Information in Performance Monitor
You can find the Performance Monitor, with all preselected memory indicators, in the Memory Usage card of the SAP HANA cockpit.
The Performance Monitor provides an overview of the general memory situation, with time-based statistics for the following indicators:
- Database resident memory
- Total resident memory
- Physical memory size
- Database used memory
- Database allocation limit
For all running services, it provides the following respective indicators:
- Memory used
- Memory allocation limit
Memory Information from SQL Commands
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 sections marked /* Modification section */.
The most important memory-related analysis queries are listed here. Note that some queries have version-specific variations identified in the file names:
HANA_Memory_Overview_1.00.vv
Provides an overview of current memory information.
HANA_Memory_TopConsumers_1.00.vv
Displays the areas with the current highest memory requirements: columnstore and rowstore tables, heap, code, and stack.
HANA_Memory_SharedMemory*
Shows the currently used and allocated shared memory per host and service.
HANA_Memory_TopConsumers_History_1.00.vv (+_ESS)
Displays the areas with the highest historical memory requirements: columnstore and rowstore tables, heap, code, and stack. Optionally, it can include results for the Embedded Statistics Server.
Memory Information from Logs and Traces
In the case of critical memory issues, you can often find more detailed information in logs and trace files, as follows:
Identify memory-related errors in the SAP HANA system alert trace files. Search for the strings memory, allocat, or OOM.
Note
The search is not case-sensitive.
Check if an Out of Memory (OOM) trace file was created.
In SAP HANA Cockpit, the number of Out of Memory (OOM) events are displayed on the Memory Usage card. Use the Analyze Memory History application to investigate the root cause of the OOM. You can find the Analyze Memory History application by choosing the More button in the Memory Usage card.
Select the Out of Memory Events tab to display on lower chart the number of unique out-of-memory events that have occurred in the time range specified in the header. The number of events shown depends on your selected time range, not the vertical selection bar. The list shows the following information on the OOM events:
- Occurrences: The number of times a specific OOM event has been triggered
- Last Occurrence: The time and date of the most recent occurrence of the OOM event
- Last Reason: The parameter that triggered the most recent occurrence of the OOM event
- Statement: The SQL statement related to the OOM event
- Statement Hash: The unique identifier for the OOM event. To open the Workload Analyzer and investigate the event, choose the OOM identifier.
Hint
When investigating from the SYSTEMDB, if an event has a corresponding OOM dump file, you can select View Trace to launch the Dump Viewer in the SAP Database Explorer.In the Memory Statistics charts you can choose to display historical data for a time range between 24 hours and six weeks. To display a date range longer than six weeks (42 days), you can use SQL to update the RETENTION_DAYS_CURRENT value in the table _SYS_STATISTICS"."STATISTICS_SCHEDULE.
If you need help from SAP Customer Support to perform an in-depth analysis, add the following valuable information to the ticket:
Diagnosis information (full system info dump)
Performance trace, which provides detailed information on the system behavior, including statement execution details
The trace output is written to the trace file perftrace.tpt, which must be sent to SAP Customer Support.
If specific SAP HANA system components need deeper investigation, SAP Customer Support may ask you to raise the corresponding trace levels to INFO or DEBUG. To do this, following these steps:
Launch the Database Trace wizard.
Select the Show All Components checkbox.
Enter the search string.
Select the found component in the indexserver.ini file.
Change the system trace level to the appropriate values.
Some trace components, for example join_eval = DEBUG, can create many megabytes of trace information. They require an increase of the values maxfiles and maxfilesize in the trace section of the global.ini file.