How to Perform Garbage Collection in HANA

The process defined below can be executed on daily basis during off peak hour or after every 3 days. Also it can be repeated on specific HANA Node if memory utilization on specific HANA node server is more than 85%.

The following SQL statement needs to be executed in HANA studio to know the current resident memory at the start and after each Garbage collection process to verify it's effect:

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;

Note-Take the screen shot for future verification.

Also set the Display parameter for your adm user ID on any of the HANA application server in order to start HDBAdmin.sh.

Click on "more" in the HDBAdmin tool as shown in the above screen shot.

Select Service->Console-Select the Node and then execute the following command:

mm gc -f

Note: We use only Index server nodes for execution of the above command. Please see the screen shot below for more detail.

From the screen shot above it is clear that the allocated memory has been reduced from 390GB to 304GB (Approx) on HANA server Node 02 which can be verified from the below SQL statements output:

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;

Similarly this can be run on other HANA servers like <HANA>01/<HANA>03 etc.. servers which is as follows:

From the above screen shot it is clear that the memory allocation to Index server has been reduced from 230GB to 140GB (approx.).

From the screen shot it is clear that after Garbage collection run on <HANA>03 Index server the memory allocation has been reduced from 450 GB to 370GB (approx.).

This can be verified through execution of the following SQL statement too from HANA Studio:

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;

By this way one can get rid of unwanted buffered object which is not being utilized for a longer period of time and system consider them safe to be discarded.

This process has no impact on the availability of the Objects (Tables) Loaded inside HANA DB and is not triggering any Unloading of Tables.

Source: scn.sap.com