HANA Memory Usage

So far we learnt about SAP HANA Architecture and Column Data Storage Vs Row Data Storage
In this article we will explain memory usage and calculation in SAP HANA.
Introduction:

SAP HANA is a leading in-memory database and data management platform, specifically developed to take full advantage of the capabilities provided by modern hardware to increase application performance. By keeping all relevant data in main memory (RAM), data processing operations are significantly accelerated.

"SAP HANA has become the fastest growing product in SAP's history." 

A fundamental SAP HANA resource is memory. Understanding how the SAP HANA system requests, uses and manages this resource is crucial to the understanding of SAP HANA. SAP HANA provides a variety of memory usage indicators, to allow monitoring, tracking and alerting.

This article explores the key concepts of SAP HANA memory utilization, and shows how to understand the various memory indicators.

Memory Concepts:

As an in-memory database, it is critical for SAP HANA to handle and track its memory consumption carefully and efficiently. For this purpose, the SAP HANA database pre-allocates and manages its own memory pool and provides a variety of memory usage indicators to allow monitoring.

SAP HANA tracks memory from the perspective of the host. The most important concepts are as follows:

Physical memory:
The amount of (system) physical memory available on the host.

SAP HANA Allocated memory
The memory pool reserved by SAP HANA from the operating system.

SAP HANA Used memory
The amount of memory from this pool that is actually used by the SAP HANA database.

Determining Physical Memory Size:

Physical memory (DRAM) is the basis for all memory discussions. On most SAP HANA hosts, it ranges from 256 gigabytes to 2 terabytes. It is used to run the Linux operating system, SAP HANA, and all other programs that run on the host. The following table lists the various ways of determining the amount of physical memory: 

You can use the M_HOST_RESOURCE_UTILIZATION view to explore the amount of Physical Memory as follows: 

Determine Available Physical Memory:

Execute the SQL query: 

select round((USED_PHYSICAL_MEMORY + FREE_PHYSICAL_MEMORY) /1024/1024/1024, 2) 

as "Physical Memory GB" 

from PUBLIC.M_HOST_RESOURCE_UTILIZATION;

Execute the Linux command:

cat /proc/meminfo | grep MemTotal

Determine Free Physical Memory:

Execute the SQL query: 

select round(FREE_PHYSICAL_MEMORY/1024/1024/1024, 2) 

as "Free Physical GB" 

from PUBLIC.M_HOST_RESOURCE_UTILIZATION;

Execute the Linux command:

awk 'BEGIN {sum = 0}; 

/^(MemFree|Buffers|Cached):/ {sum = sum + $2}; END {print sum}' /proc/meminfo

SAP HANA Allocated Memory Pool:

The SAP HANA database (across its different processes) reserves a pool of memory before actual use. 

This pool of allocated memory is pre-allocated from the operating system over time, up to a predefined global allocation limit, and is then efficiently used as needed by the SAP HANA database code. More memory is allocated to the pool as used memory grows. If used memory nears the global allocation limit, the SAP HANA database may run out of memory if it cannot free memory. The default allocation limit is 90% of available physical memory, but this value is configurable. 

To find the global allocation limit of the database, run below SQL query:

select HOST, round(ALLOCATION_LIMIT/1024/1024/1024, 2) as "Allocation Limit GB" 

from PUBLIC.M_HOST_RESOURCE_UTILIZATION

Effective Allocation Limit: 

In addition to the global allocation limit, each process running on the host has an allocation limit, the process allocation limit. Given that all processes cannot collectively consume more memory than the global allocation limit, each process also has what is called an effective allocation limit. The effective allocation limit of a process specifies how much physical memory a process can in reality consume given the current memory consumption of other processes. 

Example:

A single-host system has 100 GB physical memory. Both the global allocation limit and the individual process allocation limits are 90% (default values). This means the following:

• Collectively, all processes of the HANA database can use a maximum of 90 GB.
• Individually, each process can use a maximum of 90 GB.

If 2 processes are running and the current memory pool of process 1 is 50 GB, then the effective allocation limit of process 2 is 40 GB. This is because process 1 is already using 50 GB and together they cannot exceed the global allocation limit of 90 GB. 

SAP HANA Used Memory:

Used memory serves several purposes:

• Program code and stack
• Working space and data tables (heap and shared memory)

The program code area contains the SAP HANA database itself while it is running. Different parts of SAP HANA can share the same program code. 

The stack is needed to do actual computations. 

The heap and shared memory are the most important part of used memory. It is used for working space, temporary data and for storing all data tables. 

You can use the M_SERVICE_MEMORY view to explore the amount of SAP HANA Used Memory as follows:

Total Memory Used:

SELECT round(sum(TOTAL_MEMORY_USED_SIZE/1024/1024)) AS "Total Used MB" 

FROM SYS.M_SERVICE_MEMORY;

Code and Stack Size:

SELECT round(sum(CODE_SIZE+STACK_SIZE)/1024/1024) AS "Code+stack MB" 

FROM SYS.M_SERVICE_MEMORY;

Total Memory Consumption of All Columnar Tables:

SELECT round(sum(MEMORY_SIZE_IN_TOTAL)/1024/1024) AS "Column Tables MB" 

FROM M_CS_TABLES;

Total Memory Consumption of All Row Tables:

SELECT round(sum(USED_FIXED_PART_SIZE + 

USED_VARIABLE_PART_SIZE)/1024/1024) AS "Row Tables MB" 

FROM M_RS_TABLES;

Total Memory Consumption of All Columnar Tables by Schema:

SELECT SCHEMA_NAME AS "Schema", 

round(sum(MEMORY_SIZE_IN_TOTAL) /1024/1024) AS "MB" 

FROM M_CS_TABLES GROUP BY SCHEMA_NAME ORDER BY "MB" DESC;

Memory Consumption of Columnar Tables:

The SAP HANA database loads columnar tables into memory column by column only upon use. This is sometimes called "lazy loading". This means that columns that are never used are not loaded, which avoids memory waste. 

When the SAP HANA database runs out of allocated memory, it may also unload rarely used columns to free up some memory. Therefore, if it is important to precisely measure the total, or "worst case", amount of memory used for a particular table, it is best to ensure that the table is fully loaded first by executing the following SQL statement: 

LOAD table_name ALL.

To examine the memory consumption of columnar tables, you can use the M_CS_TABLES and M_CS_COLUMNS views. 

The following examples show how you can use these views to examine the amount of memory consumed by a specific table. You can also see which of its columns are loaded and the compression ratio that was accomplished. 

List All Columnar Tables of Schema 'SYSTEM':

SELECT TABLE_NAME AS "Table", round(MEMORY_SIZE_IN_TOTAL/1024/1024, 2) as "MB" 

FROM M_CS_TABLES WHERE SCHEMA_NAME = 'SYSTEM' ORDER BY "MB" DESC;

Show Column Details of Table "TABLE1":

SELECT COLUMN_NAME AS "Column", LOADED AS "Is Loaded", 

round(UNCOMPRESSED_SIZE/1024/1024) AS "Uncompressed MB", 

round(MEMORY_SIZE_IN_MAIN/1024/1024) AS "Main MB", 

round(MEMORY_SIZE_IN_DELTA/1024/1024) AS "Delta MB", 

round(MEMORY_SIZE_IN_TOTAL/1024/1024) AS "Total Used MB", 

round(COMPRESSION_RATIO_IN_PERCENTAGE/100, 2) AS "Compr. Ratio" 

FROM M_CS_Columns WHERE TABLE_NAME = 'TABLE1;

Note: The M_CS_TABLES and M_CS_COLUMNS views contain a lot of additional information (such as cardinality, main-storage versus delta storage and more). For example, use the following query to obtain more information: 

SELECT * FROM M_CS_COLUMNS WHERE TABLE_NAME = '"' and COLUMN_NAME = '"'

Memory Consumption of Row-Ordered Tables:

Several system tables are in fact row-ordered tables. You can use the M_RS_TABLES view to examine the memory consumption of row-ordered tables. 

For instance, you can execute the following SQL query, which lists all row tables of schema "SYS" by descending size: 

SELECT SCHEMA_NAME, TABLE_NAME, round((USED_FIXED_PART_SIZE + 

USED_VARIABLE_PART_SIZE)/1024/1024, 2) AS "MB Used" 

FROM M_RS_TABLES 

WHERE schema_name = 'SYS' ORDER BY "MB Used" DESC, TABLE_NAME

Memory Consumption Configuration:

By default, SAP HANA can pre-allocate up to 90% of the available physical memory on the host. There is normally no reason to change the value of this variable, except in the case where a license was purchased for less than the total of the physical memory. In this case, you should change the global allocation limit to remain in compliance with the license. 

Example 1:

You have a server with 512GB, but purchased an SAP HANA license for only 384 GB. Set the global_allocation_limit to 393216 (384 * 1024 MB).

Example 2:

You have a distributed HANA system on four hosts with 512GB each, but purchased an SAP HANA license for only 768 GB. Set the global_allocation_limit to 196608 (192 * 1024 MB on each host).

Resident memory:

Resident memory is the physical memory actually in operational use by a process. 

Over time, the operating system may "swap out" some of a process' resident memory, according to a least-recently-used algorithm, to make room for other code or data. Thus, a process' resident memory size may fluctuate independently of its virtual memory size. In a properly sized SAP HANA appliance there is enough physical memory, and thus swapping is disabled and should not be observed. 

To display the size of the Physical Memory and Resident part, you can use the following SQL command: 

select HOST, round((USED_PHYSICAL_MEMORY + FREE_PHYSICAL_MEMORY)/1024/1024/1024, 2) as "Physical Memory GB", 

round(USED_PHYSICAL_MEMORY/1024/1024/1024, 2) as "Resident GB" 

from PUBLIC.M_HOST_RESOURCE_UTILIZATION

Memory Sizing:

Memory sizing is the process of estimating, in advance, the amount of memory that will be required to run a certain workload on SAP HANA. To understand memory sizing, you will need to answer the following questions: 

1. What is the size of the data tables that will be stored in SAP HANA? 

You may be able to estimate this based on the size of your existing data, but unless you precisely know the compression ratio of the existing data and the anticipated growth factor, this estimate may only be partially meaningful. 

2. What is the expected compression ratio that SAP HANA will apply to these tables?

The SAP HANA Column Store automatically uses a combination of various advanced compression algorithms (dictionary, LRE, sparse, and more) to best compress each table column separately. The achieved compression ratio depends on many factors, such as the nature of the data, its organization and data-types, the presence of repeated values, the number of indexes (SAP HANA requires fewer indexes), and more. 

3. How much extra working memory will be required for DB operations and temporary computations?

The amount of extra memory will somewhat depend on the size of the tables (larger tables will create larger intermediate result-tables in operations like joins), but even more on the expected work load in terms of the number of users and the concurrency and complexity of the analytical queries (each query needs its own workspace). 

SAP Notes 1514966, 1637145 and 1736976 provide additional tools and information to help you size the required amount of memory, but the most accurate method is ultimately to import several representative tables into a SAP HANA system, measure the memory requirements, and extrapolate from the results. 

SAP HANA Studio:

You can view some of the most important memory indicators on the Overview tab of the SAP HANA studio administrative perspective: 

For even more details, check out the new Memory Overview feature of the SAP HANA studio. To access it, right click on a system in the Systems View, and select "Open Memory Overview" in the context menu, as follows: 

This will open the Memory Overview, which looks as follows: 

Note: To view the Memory Overview, you need Monitoring privileges. E.g. use the following SQL statement (replace 'youruser' with the actual user name): call GRANT_ACTIVATED_ROLE('sap.hana.admin.roles::Monitoring','youruser') 

Summary:

SAP HANA maintains many system views and memory indicators, to provide a precise way to monitor and understand the SAP HANA memory utilization. The most important of these indicators is Used Memory and the corresponding historic snapshots. In turn, it is possible to drill down into very detailed reports of memory utilization using additional system views, or by using the convenient Memory Overview from the SAP HANA studio. 

Since SAP HANA contains its own memory manager and memory pool, external indicators, like the host-level Resident Memory size, or the process-level virtual and resident memory sizes, can be misleading when estimating the real memory requirements of a SAP HANA deployment.

Click here to go to Next Chapter.  2.4 System Generated Schemas