1977101 - How to handle HANA Alert 12: 'Memory usage of name server'

Symptom

You receive HANA Alert ID 12 - 'Memory usage of name server'

Environment

SAP HANA Database

Cause

1. The number of tables loaded into memory of SAP HANA database is well above 1 million.
2. HANA issue with cleaning up shared memory.

Resolution

PROCEDURE

1. Check Alert Details.
2. Increase value of parameter size in file nameserver.ini - topology.
3. Test and validate the new configuration by checking memory consumption of nameserver and by checking for re-occurrences of alert 'Memory usage of name server'.
4. Further recommendations if countermeasures do not help - Clean up shared memory.

PATH

• SAP HANA Studio - SAP HANA Administration Console - Alerts
• SAP HANA Studio - SAP HANA Administration Console - Configuration
• SAP HANA Studio - SAP HANA Administration Console - Landscape - Services
• SAP HANA Studio - SQL Console

HOW-TO

1. Check Alert

Access Alert Tab in HANA Studio and check 'Current Alerts':

a.) What is the priority of the alert?

b.) Is an issue occurring on one host only or on multiple hosts?

Check all alerts and put a filter on Alert ID 12 and select the last 3 weeks:

c.) Is it a one-time or temporary issue or are there frequent occurrences based on historic alert data?

2. Increase value of parameter 'size' in nameserver.ini - topology

Goto Configuration Tab in HANA Studio and navigate to nameserver.ini - topology - size. Enter an increased value. Usually it helps to initially double the value from its default value 256 MB (268368504 byte) to 512 MB (536870912 byte). The parameter must not be larger than 2 GB.
If the alert occurs for multiple hosts then changes can be implemented on system level. If only a single host is affected the parameter can be changed on host level. Please note that the parameter is only changeable offline, so after the change you must restart the SAP HANA Database for the new parameter value to take effect.

3. Test and Validate new Configuration

1. Check for re-occurrences of alert 'Memory usage of name server' (Alert ID = 12). Goto Alerts Tab in HANA Studio and put a filter on Check ID = 12.
2. If no alert occurs you can manually check trends by checking memory consumption of nameserver in the Services Tab in HANA Studio - Landscape.
3. For a more detailed check you can display current data of monitoring view M_SHARED_MEMORY in schema SYS via the SQL Editor:
   
   SELECT 'NAMESERVER_SHARED_MEMORY',HOST,TO_CHAR(ROUND(MAP(SHARED_MEMORY_ALLOCATED_SIZE, 0, 0, SHARED_MEMORY_USED_SIZE / SHARED_MEMORY_ALLOCATED_SIZE * 100))) asValueFROM M_SERVICE_MEMORY WHERE SERVICE_NAME = 'nameserver'

4. Further recommendations

If the alert continues to occur even with increased parameter value, then it is recommended to create a SAP incident with application component HAN-DB for further analysis in the system. The root cause may be either in the usage of the system or in a memory leak. A service connection to HANA Studio should be configured and opened as per the SAP note 1592925
In some cases the following actions have helped:

1. Clean-up shared memory with SQL statement: ALTER SYSTEM CLEAR SQL PLAN CACHE.
   Note: This query will remove all of the SQL plans that are not currently being executed from the SAP HANA database sql plan cache. It also removes all plans having reference count of '0' from the plan cache and resets all the statistics of the remaining plans. Lastly the command also reset the contents of M_SQL_PLAN_CACHE_OVERVIEW monitoring view. This has helped when automatic removal of hidden temp tables related to SQL Plan Cache has not been performed by the database system, so that they are still existing and using shared memory. This query will remove all of un-used temp tables.
2. Restart the SAP HANA Database.

Keywords

nameserver.ini; topology; shared memory; large number of tables loaded to memory; over one million tables; size; M_SERVICE_MEMORY; M_SQL_PLAN_CACHE_OVERVIEW; SQL Plan Cache; temp tables; temporary tables;