SAP HANA performance troubleshooting

Note for all HDB SQL script: 1969700       

Please go through below checks if you are facing SAP Hana performance issue.

• Check HANA revision if it is very old, upgrade it to latest version as in new version SAP provide many bug fix. 

• Check CPU and memory utilization in HANA studio or in HANA cockpit.

URL for HANA cockpit: https://<IP>:4300/sap/hana/admin/cockpit/

  

If you find any CPU and memory utilization, Check why we are getting high CPU and Memory utilization.

      Check trace files

Path to check trace file: /usr/sap/<SID>/HDB<instance Number>/<Host Name>/trace

·         Check is there any expensive statement or block transaction in HDB. 

Open HANA cockpit and check for block transaction.

• Check OOM situation (Out of Memory)

Go to trace file in HDB host path: /usr/sap/<SID>/HDB<instance Number>/<Host Name>/trace

Command to check OOM dump: ls -lrt |grep -i OOM

• Check for savepoint duration

Check if savepoint are taking time to perform.

Reason: While system perform savepoint no data can be read or write. You may face IO issue as well at the time of savepoint. 

• Check for parked worker 

Script to check park worker is:

##

SELECT
        HOST,
        PORT,
        SUM(PARKED_WORKER_COUNT) / SUM(TOTAL_WORKER_COUNT * 2) AS RATIO
FROM "M_JOBEXECUTORS"
GROUP BY HOST, PORT
HAVING SUM(PARKED_WORKER_COUNT) / SUM(TOTAL_WORKER_COUNT * 2) > 1;

##

The Above command SQL query does not return an empty result set, this situation can lead to a complete standstill of the database.

     

·         Perform minicheck

(Refer HDB script “HANA_Configuration_MiniChecks_2.00+.txt” )

·         Note for minicheck SQL statement

1999993 - How-To: Interpreting SAP HANA Mini Check Results

Copy the result of SQL query in excel sheet and look for ‘X’ in row ‘C’

• How to take RTE Dump from OS level

Log into the Linux HANA host presenting the issue as ‘SIDadm’ user;

Run command 'hdbcons';

On the hdbcons console run command below:

 > runtimedump dump

Perform above steps three time with one minute interval at the time of issue and provide the output to SAP.

Path where RTE dump save:

/usr/sap/<SID>/HDB<instance Number>/<Host Name>/trace

• Unused space in data files (%)

Refer SQL script “HANA_Disks_Overview” 

Solution:

Increased data disk space requirements Note 1870858 Reorganize the disk area using command:  ALTER SYSTEM RECLAIM DATAVOLUME.

See for reference: 1999993 - How-To: Interpreting SAP HANA Mini Check Results

Reference from SAP HANA Administration guide:

Data and Log Volumes

Each data volume contains one file (datavolume_0000.dat) in which data is organized into pages, ranging in size from 4KB to 16MB (page size class). Data is written to and loaded from the data volume page-wise. Over time, pages are created, changed, overwritten, and deleted. The size of the data file is automatically increased as more space is required. However, it is not automatically decreased when less space is required. This means that at any given time, the actual payload of a data volume (that is the cumulative size of the pages currently in use) may be less than its total size. This is not necessarily significant – it simply means that the amount of data in the file is currently less than at some point in the past (for example, after a large data load). If a data volume has a considerable amount of free space, it might be appropriate to shrink the data volume. However, a data file that is excessively large for its typical payload can also indicate a more serious problem with the database. SAP support can help you analyze your situation.