HANA memory usage details: Part 1 Shared Memory

This is the first part of the blog series of HANA memory usage details and changes in SPS12

For now only the “used memory” part is interesting. May be you heard about “resident memory” => this is the OS view which is a not up-to-date value in cause of the deferred garbage collection / release of memory and can’t be taken as indicator.
“used memory” consists of:

• Table Data (Row Store + Column Store + System Tables)
• Code and Stack
• Database Management / Working Space (heap and shared memory)

###############
 Shared memory
###############

Content

• Specific row store components (tables, catalog, free)
• Nameserver topology

Shared Memory: Size Check

To check the size of the shared memory you can execute the following statement:

SELECT 
Service_name, TOTAL_MEMORY_USED_SIZE, SHARED_MEMORY_ALLOCATED_SIZE, 
SHARED_MEMORY_ALLOCATED_SIZE/EFFECTIVE_ALLOCATION_LIMIT*100 
as SHARED_MEMORY_ALLOCATED_PCT, 
SHARED_MEMORY_USED_SIZE/TOTAL_MEMORY_USED_SIZE*100 
as SHARED_MEMORY_USED_PCT,SHARED_MEMORY_USED_SIZE 
FROM M_SERVICE_MEMORY 
where Service_name='indexserver'

So we have about 12GB shared memory.
You can see at the procent values that this is one of the smaller memory areas in the system, because very few programs use large blocks of shared memory.

Shared Memory: Categories

If you want to know how the shared memory is splitted, you can run the following statement:
Change the Port 30003 according your instance number 3xx03

select 
CATEGORY,ALLOCATED_SIZE,USED_SIZE,FREE_SIZE 
from "SYS"."M_SHARED_MEMORY" 
where Port=30003 
order by used_size desc

You can see that the biggest part consists of the table category

Shared Memory: HANA design decision

An early SAP HANA design decision was to use “shared memory” for row-store tables (Incidentally, this memory is not really shared between processes – it is just defined as “shared memory”). As a result, when you use large row-store tables, the shared-memory footprint of SAP HANA can become very large.
If you want to reduce it just check out the row store fragmentation (1813245 – SAP HANA DB: Row store reorganization). I recommend to do this via the offline variant because it is more accurate. Since it’s guaranteed that there are no update transactions during the restart time, it achieves the maximum compaction ratio.
Before you do this check your housekeeping => 706478 – Preventing Basis tables from increasing considerably

Fragmentation check (Rev.52 or higher):

SELECT 
HOST, PORT, CASE WHEN (((SUM(FREE_SIZE) / SUM(ALLOCATED_SIZE)) > 0.30) 
AND 
SUM(ALLOCATED_SIZE) > TO_DECIMAL(10)*1024*1024*1024) 
THEN 
'TRUE' 
ELSE 
'FALSE' 
END 
"Row store Reorganization Recommended", 
TO_DECIMAL( SUM(FREE_SIZE)*100 / SUM(ALLOCATED_SIZE), 10,2) "Free Space Ratio in %",
TO_DECIMAL( SUM(ALLOCATED_SIZE)/1048576, 10, 2) "Allocated Size in MB",
TO_DECIMAL( SUM(FREE_SIZE)/1048576, 10, 2) "Free Size in MB" 
FROM 
M_RS_MEMORY 
WHERE 
( CATEGORY = 'TABLE' OR CATEGORY = 'CATALOG' ) 
GROUP BY HOST, PORT;

Starting with a fragmentation of 30% you should take a reorg in consideration.

There is a memory leak in some revisions, if you want to get rid of it just execute a reorg as workaround. Solution is to update to 112.06 / 122.02 or higher.
2362759 – Row table memory leak on SPS11 (Rev111~112.05) and SPS12 (Rev120~122.01)

To speed up the start time you can follow note 2159435 – How-To: Keeping SAP HANA Row Store in Memory when restarting
Normally after a shutdown the shared memory will be cleared. That means after a restart of the DB the RS have to be loaded completely from disk. This can take a while depending on speed of the storage and size of the Row Store.
You can define via parameter how long the row store after a shutdown should be held in memory. This happens during the shutdown via hdbrsutil (_not_ hdbnsutil). The shared memory segments will assigned to this process and transfered to indexserver during start procedure.

Back to main blog
Go next to Part 2: Heap memory
Go next to Part 3: tech. changes in SPS12

###############
Version History
###############

V1.1 Repaired destroyed pictures