Memory Management in SAP Hana 2.0

Memory Usage in the SAP HANA Database

SAP HANA comprises of different processes that store the memory from the operating system when you begin with the SAP HANA System. The SAP?needs to manage and follow its own memory during the running and operational mode of the databases. However, it needs to be noted that you can only use one portion of the requested memory at any time.

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 the understanding of 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.

You can find detailed information about memory consumption of individual components and operations by looking into memory allocator statistics.

For more information about memory consumption with regards to SAP HANA licenses, see SAP Note 1704499?System Measurement for License Audit.

The pre-reserved memory is made of various pools that are categorized into two parts-

• Used Memory
• Resident Memory

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 (OS) is responsible for reserving memory to all processes. When SAP HANA starts up, the OS 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.

The following figure shows used memory, consisting of code, stack, and table data:

Since the code and program stack size are about 6 GB, almost all of used memory is used for table storage, computations, and database management.

Peak Used Memory

SAP HANA has a special used memory indicator called peak used memory. As the value for used memory is a current measurement, peak used memory allows you to track the maximum value for used memory over time.

You can also reset peak used memory. Resetting can be useful if you want to establish the impact of a certain workload on memory usage.

Here is a SQL script which will give the top peak memory usage size since the sap hana system started. This detail is a simple report regarding the highest used memory.

-- The following script shows peak used memory for entire sap hana system	  
-- top peak memory

select HOST, 
       round(INSTANCE_TOTAL_MEMORY_PEAK_USED_SIZE/(1024*1024*1024), 2) as "Peak Used Memory GB"
from M_HOST_RESOURCE_UTILIZATION;


| HOST    | Peak Used Memory GB   |
| ------  | --------------------- |
| LHNDB01 |                872.53 |
        

In order to point out the last greatest memory usage, get the first few top peak memory usage size occurrences since the system start up. You will be able to cross check peak memory usage with known date events such as batch execution or data load into the database.

-- Get the 10 top peak used memory details
-- Result is sorted by memory size.  ( it is not a sequential date and time list )

select top 30 HOST, 
       SERVER_TIMESTAMP, 
       round(INSTANCE_TOTAL_MEMORY_USED_SIZE/(1024*1024*1024), 2) as "Used Memory GB" 
       from _SYS_STATISTICS.HOST_RESOURCE_UTILIZATION_STATISTICS
       where hour(SERVER_TIMESTAMP) = 7 and minute(SERVER_TIMESTAMP) = 0 
       order by INSTANCE_TOTAL_MEMORY_USED_SIZE;
	   

| HOST   | SERVER_TIMESTAMP              | Used Memory GB |
| ------ | ----------------------------- | -------------- |
| linu07 | 2019-05-01 07:00:50.499000000 |           664. |
| linu07 | 2019-05-13 07:00:50.532000000 |           565. |
| linu07 | 2019-05-08 07:00:50.459000000 |           564. |
| linu07 | 2019-05-06 07:00:50.477000000 |           544. |
| linu07 | 2019-05-04 07:00:50.498000000 |           364. |
| linu07 | 2019-04-25 07:00:50.488000000 |           264. |
| linu07 | 2019-05-11 07:00:50.507000000 |           164. |
| linu07 | 2019-05-09 07:00:50.503000000 |           164. |
| linu07 | 2019-05-05 07:00:50.469000000 |           146. |
| linu07 | 2019-05-07 07:00:50.516000000 |           124. |	   
											
         

You can check SAP HANA peak used memory occurrences between 2 dates, this is when your are requested to investigate memory usage during a specific period of time. The previous script gives you different peak memory usage dates from which you can investigate further.

This following Query would be very useful in the following context: Specific Peak used memory event dates have been found and you would like to drill down the events between 2 dates. You want to be able to bring up into a report how fast memory usage has been occurring between specific date and time. The following SQL result will show peak memory usage chronologically.

--
-- result is sorted by date and time
--

do begin

DECLARE FROMSDATETIME varchar(20);
DECLARE TOSDATETIME varchar(20);
DECLARE TOTAL_HANA_MEMORY_LIMIT  integer;
DECLARE PERSONNAL_INDICATION_LIMIT integer;
DECLARE FILTER_DATA_FROM_PEAKMEMORY  integer;

-- Update variable parameters to your requirements
-- -------------------------------
FROMSDATETIME := '2019/02/01 00:59:59';  
TOSDATETIME := '2019/06/04 23:59:59';  
TOTAL_HANA_MEMORY_LIMIT := 1500;    -- as an ex : 1500 GB ( Database Allocation limit )
PERSONNAL_INDICATION_LIMIT := 810;    -- 1000 GB : to trigger the message "<----  xx% used" which will stand out in the query result
FILTER_DATA_FROM_PEAKMEMORY := 795;   -- to limit the amount of unnecessary rows in the result
-- -------------------------------

select 	
	HOST, 
	TO_NVARCHAR(SERVER_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') as DateAndTime, 
	RTRIM(round(INSTANCE_TOTAL_MEMORY_USED_SIZE/1024/1024/1024),0)  as "Peak Memory GB",
	case  
	when round(INSTANCE_TOTAL_MEMORY_USED_SIZE/1024/1024/1024) > :PERSONNAL_INDICATION_LIMIT then
		'<----  '|| round(((INSTANCE_TOTAL_MEMORY_USED_SIZE/1024/1024/1024)/:TOTAL_HANA_MEMORY_LIMIT)*100) || '% used.'
	else
		''
	end  WatchOut
from _SYS_STATISTICS.HOST_RESOURCE_UTILIZATION_STATISTICS  
where SERVER_TIMESTAMP between :FROMSDATETIME and :TOSDATETIME
and round(INSTANCE_TOTAL_MEMORY_USED_SIZE/1024/1024/1024) > :FILTER_DATA_FROM_PEAKMEMORY
 order by "SERVER_TIMESTAMP"  asc;	

end;


-- Result example for the following situation
-- ---------------------------------------------
-- 1/ An Allocation limit of 1500 GB exist for your current tenant database   ( TOTAL_HANA_MEMORY_LIMIT := 1500; )
-- 2/ You get too many results under 790 GB, so you want to reduce result with "FILTER_DATA_FROM_PEAKMEMORY := 795"
-- 3/ To obtain a warning when the peak used memory is above a certain limit. ( PERSONNAL_INDICATION_LIMIT := 810; )

				 
HOST             DATEANDTIME     Peak Memory GB            WATCHOUT
-------  -------------------  -----------------  ------------------
linu08   2019-05-18 18:57:44                799.   
linu08   2019-05-18 18:58:44                799.   
linu08   2019-05-18 18:59:44                806.   
linu08   2019-05-18 19:00:44                815.   <----  54% used.
linu08   2019-05-18 19:01:44                811.   <----  54% used.
linu08   2019-05-25 19:54:45                804.   
linu08   2019-05-25 19:55:44                801.   
linu08   2019-05-25 19:56:44                803.   
 
         

SAP HANA Resident Memory

Resident memory is the real physical memory that processes utilize in the current state. The resident physical memory is a pool of memory used, representing the SAP HANA, operating system, and other programs.

When part of the virtually allocated memory actually needs to be used, it’s loaded or mapped to the real, physical memory of the host and becomes?resident. Physical memory is the DRAM memory installed on the host.

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's?resident?memory according to a least-recently-used algorithm to make room for other code or data. Thus, a process's?resident?memory size may fluctuate independently of its virtual memory size. In a properly sized SAP HANA appliance, there’s enough physical memory so that swapping is disabled and isn't observed.

On a typical SAP HANA appliance, the?resident?memory part of the operating system and all other running programs usually doesn’t exceed 2 GB. The rest of the memory is therefore dedicated for the use of SAP HANA.

When memory is required for table growth or for temporary computations, the SAP HANA code obtains it from the existing memory pool (Figure Above). When the pool can’t satisfy the request, the SAP HANA memory manager requests and reserves more memory from the operating system. At this point, the virtual memory size of SAP HANA processes grows.

Under?Resident Memory, you will find different memory measurements from the operating system perspective:

Here is a SQL script which will give the top peak memory usage size since the sap hana

• Database Resident?is the size of resident memory at operating system level owing to SAP HANA database processes. In case of a MDC system, Database Resident shows the memory allocated by the tenant database or the system database;
• Total Resident?is the total size of resident memory in the operating system, including other programs;
• Physical Memory?is the total physical memory available in the server.

There is no reason to be concerned about a high Database Resident memory utilization. The Database Resident memory will grow up to the Allocation Limit value defined under "SAP HANA Used Memory".?In case there is a high memory utilization in "Total Resident", investigate the memory utilization in the operating system to check other programs/processes;Keep monitoring on "Used Memory" metric in SAP HANA Studio?for a reliable way to quickly monitor your SAP HANA system memory utilization.