SAP HANA Memory Usage – What should I really monitor?

With so many memory areas of SAP HANA, What should I really Monitor with respect to Memory usage of HANA database?

Below picture is key in understanding the memory allocation sequence.

hanamemory

You can control the behaviour of total memory allocated to SAP HANA database by using allocation limit parameter (global.ini -> memorymanager -> global_allocation_limit)

Unless you have additional software running on the HANA server or the HANA licensed memory is less than you physical memory, global_allocation_limit need not be set.

Default value is 0 and most of the times this is not set assuming that the server will be only used by HANA database. This means HANA DB can use the complete memory of the server.

globallmt

Memory allocation in HANA Database implements a pool concept. That is, memory is pre-allocated (reserved) from the operating system for performance reasons. By default, the HANA database will allocate up to approximately 90% of the available physical memory.

This will be used for queries that will need additional space at once. This will be RESERVED and not released back to Operating System.

Hence the reserved memory does not indicate any memory issues with the database.

Key figure to be monitored is the active memory usage of the HANA database:

memused

Same has been clarified in below SAP Note:

2081473 – HANA Resident Memory : High Memory Usage

You can monitor the current usage of memory by each tenant by using below query:

SELECT * FROM “SYS_DATABASES”.”M_SERVICE_MEMORY”

tenmem

Total memory usage of the whole database can also be monitored with HANA_MEMORY_OVRVIEW (SAP Note 1969700 ).

memoverview

You can also monitor the history of total memory usage using _SYS_STATISTICS, like example below:

memusedgg

To check if the SAP HANA Memory parameters are set optimally, you can use HANA_Configuration_Parameters_1.00.70+ (SAP Note 1969700).

Important SAP Notes:

1969700 – SQL Statement Collection for SAP HANA

1840954 – Alerts related to HANA memory consumption

2175606 – HANA: How to set allocation limit for tenant databases

2081473 – HANA Resident Memory : High Memory Usage

 

Advertisements

Use of Virtual/Secondary host name with SAP HANA

There are scenarios where you do not connect to SAP HANA database instance directly via Host IP address or Physical hostname but via a Virtual IP or NAT address.

The main connection between NAT and the IP is established via DNS or a local entry in local host file.

But internally SAP HANA tries to make a connection to the Tenant DB using physical IP by default. Same has been depicted below with an example of SAP HANA Studio.

If you check the properties of a tenant database from studio you will see the following:

Even though you connect to DB using NAT IP (10.253.80.9) it is internally redirected to original IP (10.249.28.20) to make additional connections.

In this case when you try to make connection to a tenant database via webdispatcher or sql clients you will not be able to communicate to tenant DB.

studio1

You can also verify the same by querying M_HOST_INFORMATION.

studio2

This behaviour is controlled by parameter “public_hostname_resolution” under global.ini.

stud3

To change the behaviour you will have to change the parameter to “no” so that system is not forced anymore to use IP address of the Network interface. Instead you can map the hostname to required IP address as required.

settono

Note: This change does not required a DB restart.

Now you can map the hostname with required IP address on client machine to connect to the database:

Changes can be observed again with studio again.

afterc

 

 

SAP HANA Cockpit 2.0 – Installation and configuration

SAP HANA Cockpit is a Web-Based  tool for administration, monitoring and maintenance of SAP HANA systems.

Below we discuss installation steps and some key points with respect to cockpit:

  1. Download the required SAP file from SAP Market Place.

    cockpitdown

  2. Extract the SAR file and trigger installation using hdblcm.
    Note that installer installs a HANA instance (Express edition) of its own.
    More details here

    co1

  3. Verify the details, enter the password and continue. Like any HANA installation user SYSTEM is created on the DB which will be used to login to Cockpit also.
    Along with SYSTEM, the other key user created is COCKPIT_ADMIN.

    co2.png
    Note that 51021 and 51023 are the default ports for Cockpit and Cockpit manager respectively.

  4. First it is required to login to Cockpit manager to assign yourself required roles using user COCKPIT_ADMIN.
    co3
    CO4
  5. Now you are ready to login to the HANA Cockpit.
    co5
  6. If you want to add additional users or resources (HANA systems) to cockpit, you can do so via SAP HANA Cockpit manager.
    co6
    co7

 

Important SAP Notes:

2496215 – You’re not authorized to open the SAP HANA Cockpit

2618617 – Launching SAP HANA Cockpit 2.5.5 in Firefox

2496215 – You’re not authorized to open the SAP HANA Cockpit

 

How to check HDB startup issues

#HDB info showed that not all index servers are started.

hdb info

Since this is multitenant database, looked in following directory for the error logs:

/usr/sap/<SYSTEM SID>/HDB<Instance Number>/<Host Name>/trace/DB_<TENANT SID>/.

For SAP HANA single database, trace files can be found at:

/usr/sap/<SYSTEM SID>/HDB<Instance Number>/<Host Name>/trace/

Found the issue, resolved and restarted to get all my index servers running again.

hdbin

R3trans and hdbuserstore

When you change the password of connecting user of SAP HANA database, same should be updated in the key file of the HDBUSERSTORE on the Application Server.

You should be logged in with <SIDADM> for this procedure.  Take care of this specially if you application server and database are running on same server.

#> hdbuserstore SET DEFAULT <hostname>:3<instance Number>15 <user> <password>

Note: User option -i along with the command if you want to enter the options (mainly password) interactively.

Example:

hdbuserstore SET DEFAULT hanahost:30015 SYSTEM Hana@123

Once updated check the list using the below command.

#> hdbuserstore list

DATA FILE : /home/appadm/.hdb/hanahost/SSFS_HDB.DAT
KEY FILE : /home/appadm/.hdb/hanahost/SSFS_HDB.KEY

KEY DEFAULT
ENV : HANAHOST:30015
USER: SYSTEM

You can now check the connectivity with the DATABASE using R3trans -d.

If R3trans -d reports error, check the connectivity with hdbsql using the key created above.

#> hdbsql -U <KEY>      #Key is “DEFAULT” in our example.

This should connect to the database without any issues.

You might receive error sql code  rcSQL = -10709 in case of connectivity issues. Please check the host name and port again in case of issues.

In case of any issues/questions please leave a comment below. Happy learning.