Memory Sizing in Hana

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 HANA provides some control over compression, via the "optimize_compression" configuration section of the index server. In general, HANA automatically selects the best compression technique by studying the data stored in each column of the table.

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.

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). 

Operating System vs. Database:

Before proceeding with analyzing the different HANA specific memory areas, it has to be clarified that indeed the database and not processes running outside the SAP Software are responsible. This involves calculating how much resident memory is used by the HANA database. On the HANA appliance, resident memory used outside HANA (OS, 3rd party processes) is typically quite small, not exceeding 2 GB. HANA Studio and DBACockpit provide 2 different views on the resident memory:

HANA Studio:

The following SQL statements are behind this output:

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.

Database Resident:

SELECT SUM(PHYSICAL_MEMORY_SIZE) FROM M_SERVICE_MEMORY

Total Resident:

SELECT T1.HOST, T1.USED_PHYSICAL_MEMORY + T2.SHARED_MEMORY_ALLOCATED_SIZE
FROM M_HOST_RESOURCE_UTILIZATION AS T1
JOIN (SELECT M_SERVICE_MEMORY.HOST, SUM(M_SERVICE_MEMORY.SHARED_MEMORY_ALLOCATED_SIZE) AS SHARED_MEMORY_ALLOCATED_SIZE
         FROM SYS.M_SERVICE_MEMORY
         GROUP BY M_SERVICE_MEMORY.HOST) AS T2
ON T2.HOST = T1.HOST;

Since the difference between 'Total Resident' and 'Database Resident' is well below 2 GB, there is no indication that processes outside the database contribute significantly to memory consumption.

DBACockpit:

 

This is effectively the output of the following statement:

SELECT HOST,
ROUND(USED_PHYSICAL_MEMORY/1024/1024/1024, 2) AS "Resident GB",
ROUND((USED_PHYSICAL_MEMORY + FREE_PHYSICAL_MEMORY)/1024/1024/1024, 2) AS "Physical Memory GB"
FROM PUBLIC.M_HOST_RESOURCE_UTILIZATION