We can use below methods to Analyse HANA database Performance (Memory /CPU).
Generating a runtime dump
Creating a kernel profiler trace
Using the HANA Dump Analyzer too
We will take about Runtime dump. remember one thing this in itself requires resources and may therefore worsen the situation. Hence it better to get Full System info Dumps using SAP HANA cockpit.
There are a number of ways of recording runtime dumps:
1: SAP HANA cockpit
Alerting & Diagnostics --> Manage full System information dumps --> Diagnosis File
choose either a zip file from the list or create new one via Collect Diagnostics
2: From OS level using fullSystemInfoDump.py
1) Login as <sid>adm and run commands below:
..> cdpy
../python_support> python fullSystemInfoDump.py --rtedump --tenant=<tenant> --sets=3 --interval=5 --services=indexserver
The command above creates a set of 3 runtime dumps, for the indexserver, 5 minutes apart for the specified tenant for all hosts (hosts can be specified with --hosts=<hosts>)
To see a full list of options:
../python_support> python fullSystemInfoDump.py --help
2) Wait for the collection to finish.
The latest runtime dump collection can then be downloaded from:
/usr/sap/<SID>/SYS/global/sapcontrol/snapshots> ls -latr | grep 'fullsystem*'
2) From the OS Level using hdbcons
1) Log into the linux HANA host presenting the issue as sidadm user;
2) Run command 'hdbcons';
3) On the hdbcons console run command below:
> runtimedump dump
This will create a runtimedump for the host you logged in. The generated file will be under traces directory with naming like 'indexserver....rtedump.trc'.
4) Attach generated trace file to the OSS Message
3: From HANA Studio - SQL Editor
1) Log into HANA with a user with enough privileges to run procedure SYS.MANAGEMENT_CONSOLE_PROC;
2) Execute procedure SYS.MANAGEMENT_CONSOLE_PROC as a prepared statement;
3) Fill in parameter 1 with 'runtime dump dump' and parameter 2 with '<hostname>:<indexserver port>' (without quotes) and execute the statement;
4) Download the generated rtedump file and attach it to the message.
The screenshots below depict an example of how to record that dump in Studio:
4) From HANA Studio - Trace Collection
SAP HANA Studio SP8 adds the possibility of recording one or multiple samples of runtime dump based on a certain time frequency.
For instance, we can record a set of 5 runtime dumps, 1 every minute for every host or for a particular host only. Here are the steps:
1) Go to Administration -> Diagnosis Files;
2) Select "Diagnosis Information -> Collect...";
3) Set the option 'Create and collect one or multiple sets of runtime environment (RTE) dump file(s)';
4) Set the hosts, amount of samples (sets) and frequency on which the runtime dumps will be recorded;
5) Finish.
5) Using SQL Commands (available starting with HANA 2.0 SPS04):
ALTER SYSTEM CREATE RUNTIMEDUMP AT '<host>:<port>';
-- OR
CALL FULL_SYSTEM_INFO_DUMP_CREATE('--tenant=<SBID> --rtedump --sets=1 --services=indexserver --interval=1')
6) Known issues related to rtedump creation
2040144 - SAP HANA DB: Collecting Diagnosis Information from the SAP HANA Studio can cause TLB shootdowns
