Skip to Content
Author's profile photo John Appleby

How to use HDBAdmin to analyze performance traces in SAP HANA

Most of the time, the Plan Visualizer is sufficiently powerful to understand what is going on inside of SAP HANA when you run a query. However, sometimes you need to get to a lower level of detail to understand exactly what is going on in the calculation engine.

It is then possible to use HANA Studio to record performance traces, and analyze them with HDBAdmin. This is a fairly advanced topic, so beware!

First, let’s pick a query which runs slowly. This query takes 12 seconds, which is longer than I’d like. Admittedly, it’s a tough query, grouping 1.4bn transactions and counting over 2m distinct customers.

Screen Shot 2014-01-14 at 8.21.23 AM.png

We can double click on our HANA system in the HANA systems view, which will bring up the HANA Overview. Select Trace Configuration.

Screen Shot 2014-01-14 at 8.23.55 AM.png

Now select the little edit button, next to the Performance Trace. Give your trace a name – I called it slowquery.tpt, and optionally select your username as a restriction, if you’re sharing the system. You don’t want to be doing anything else with this user whilst you run this query. You need to select a duration – 60 seconds is enough for me.

Screen Shot 2014-01-14 at 8.25.35 AM.png

Select Finish, and immediately go and rerun your query. You can go and disable the performance trace in the same place, or you can just wait for the time to expire if you’re not doing anything else.

Now, we need to go and get some software. You need two things – an X-Windows Client and a SSH client. If you are using MS Windows then I highly recommend Xming and PuTTY – other software usually causes problems. Go ahead and download and install them. When you run Xming, you will see an X appear in your task bar. Hover over it – and you should see that it shows Xming Server:0.0. If it doesn’t show 0.0, take a note of what it says.

Screen Shot 2014-01-14 at 8.30.14 AM.png

Now, you need to fire up PuTTY. PuTTY doesn’t come with an installer – it’s just a single executable, and it will bring up a configuration window like this:

Screen Shot 2014-01-14 at 8.32.14 AM.pngPut your HANA hostname or IP in “hostname”. Then select Connection -> SSH -> X11. Tick Enable X11 forwarding and type localhost:0.0 (or whatever number Xming displayed) in the X display location. This enables putty to know that we have an X server running on our local machine, and tell HANA about this.

Screen Shot 2014-01-14 at 8.33.49 AM.png

Go back to the Session tab on the left, type a name for your session (I called it HANA) and select Save. Now select Open.

Screen Shot 2014-01-14 at 8.35.14 AM.png

PuTTY should connect to your HANA system. You MUST now login as the correct HANA admin user – hdbadm in my case. This is critical.

Screen Shot 2014-01-14 at 8.37.42 AM.png

Now we can do two things just to check things are good.

1) echo $DISPLAY – this shows us that we have the display correctly set. Note that it shows localhost:10.0 – that’s just PuTTY taking care of X11 for us. Good.

2) xclock – this is the tried and tested way to check that X11 is functioning. You may see an Xming at the bottom of your screen and have to select that before you see the clock. Close the clock once it comes up – we are now good.

Screen Shot 2014-01-14 at 8.39.16 AM.png

You can go right ahead and type ./HDBAdmin.sh, and HDBAdmin should now open.

Screen Shot 2014-01-14 at 8.41.04 AM.png

Unfortunately HDBAdmin doesn’t work out the box (not sure why) and you have to install the Emergency Support Package, the first time you run it. We do this with 4 little commands:

1) cd exe

2) sudo tar zxvf /usr/sap/HDB/SYS/global/hdb/emergency/emergencySupport.tgz .

3) sudo chown hdbadm.sapsys AttributeEnginePy.so _fuzzyPy.so executorPy.so

4) cd ..

This moves us into the executables folder, extracts the emergency support library as a superuser (you will need the root password) and then changes the permissions so the HANA user is the owner. Note that I used SID HDB, which is the name for my system. Yours will be different.

Screen Shot 2014-01-14 at 9.06.25 AM.png

Now you can go ahead and rerun ./HDBAdmin, and it will run without errors.

Screen Shot 2014-01-14 at 8.49.15 AM.png

Select More, which will prompt you to move to Advanced Mode, and then Perf. Trace. We are now in the right mode to open our query. Finally!

Screen Shot 2014-01-14 at 8.51.39 AM.png

Select the Load button, and then double click on your machine name (saphana1), and then double click on the trace folder. You should now see your performance trace appear. Select your trace and select Open.

Screen Shot 2014-01-14 at 8.52.41 AM.png

Your performance trace is now opened. Select Call plans, and you should see your slow-running query. Double click on it.

Screen Shot 2014-01-14 at 8.55.02 AM.png

Interestingly, the trace shows in this case that the cost is in merging dicts and aggregating in parallel. This system has a large number of partitions on the table (60) and there is a cost in doing a count distinct in each partition, and then remerging them. Perhaps a simpler partition strategy or one with the CUSTOMER_ID field as a hash, would reduce the cost of this query. I’ll give that a go.

Screen Shot 2014-01-14 at 8.56.30 AM.pngFinal Words

The HDBAdmin tool is a legacy tool which is incredibly powerful, but not that easy to use. The PlanViz tool inside HANA Studio is much easier to use and easier to understand.

But, if you are stuck and you need to get into the depths of query execution in HANA, HDBAdmin is a very powerful tool. Happy hunting!

Assigned tags

      18 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Thorsten Franz
      Thorsten Franz

      Awesome, John, thanks a huge lot for this post.

      Best,

      Thorsten

      Author's profile photo John Appleby
      John Appleby
      Blog Post Author

      No problems. Thanks for getting me to pull my finger out and write it. Did it fix your problem?

      Author's profile photo Former Member
      Former Member

      John

      If you ignore the ESP step and continue to Load Trace, this still seems to allow you to upload a tpt file. Do you see any issue in not installing the ESP and continuing?

      Steve

      Author's profile photo John Appleby
      John Appleby
      Blog Post Author

      I'm not sure. Maybe Lars Breddemann could comment on that. I just don't like error messages ๐Ÿ™‚

      Author's profile photo Lars Breddemann
      Lars Breddemann

      Sure ๐Ÿ™‚

      Ok, the ESP provides online access to the database in a highly privileged mode.

      It's not required to display performance trace files, but it would be required to trigger performance traces out of HDBAdmin.

      Anyhow, the HDBAdmin tool is not at all supported, not documented and provides no additional features that are not available via HANA studio but required to run SAP HANA.

      In short: there's no need to use the tool (as John already pointed out, PlanViz is way easier to work with).

      cheers,

      Lars

      Author's profile photo Selim Derouiche
      Selim Derouiche

      Hello,

      planviz does not work if we do inserts or updates

      SAP support confirms that this is a bug planned to SP 6 (next SP of SP5)

      insert with select does not have same plan as select only, for example when using constants.

      insert into T select a , 'X' from S will be a lot different from select a , 'X' from S, since we remark a big difference in memory consumption.

      Author's profile photo Kumar Mayuresh
      Kumar Mayuresh

      Thanks John for putting it up ๐Ÿ™‚ will give a try.

      Regards

      Kumar

      Author's profile photo Rama Shankar
      Rama Shankar

      Great Info and Steps - Thanks John! ๐Ÿ™‚

      Author's profile photo Krishna Tangudu
      Krishna Tangudu

      Thank you John for the detailed explanation and this saved me at my work place today ... got to see this exactly when i needed this ๐Ÿ™‚

      Regards,

      Krishna Tangudu

      Author's profile photo Krishna Tangudu
      Krishna Tangudu

      Sharing this in case if anybody is trying to do the same as explained in detailed by John above using AWS acccess from hanacloud server,

      You will not be able to SSH using hdbadm user and you will have to use "Root" user and if you try to SU to hdbadm after logging with "Root" user then the tunnel breaks and $DISPLAY value will become "Empty" and you may not be able to open the .HDBAdmin tool .

      Even if you try to set $DISPLAY value to the previous value before doing -SU it may fail due to "Authentication error".

      Hence you need to add the xauth along with setting the $DISPLAY value as shown below:

      hdbadmintrace.JPG

      Regards,

      Krishna Tangudu

      Author's profile photo John Appleby
      John Appleby
      Blog Post Author

      Why can't you login as hdbadm - just change the password and you'll be good.

      Author's profile photo Krishna Tangudu
      Krishna Tangudu

      Hi John,

      Not sure, I tried it but am unsuccessful in logging with hdbadm.

      Also they kept this "(Note: ssh connection is possible only with root user)" in the Note pad they kept in AWS which holds all the credentials related information.

      Regards,

      Krishna Tangudu

      Author's profile photo Michael Hill
      Michael Hill

      So John, very good article but not really applicable for the ones that REALLY need this information. Only our Basis guys are going to get to use this if they have to log in using hdbadmin and they NEVER have time for the peeps doing the modeling and such.

      I'm an admin, but not Basis ... I have the responsibility to make sure the stuff we roll out really performs. We have had cases where peeps put like 15 AT views connected in a unx and trying to run it brought the system down. That costs alot of down time for the rest of the users.

      What should we be using to do perf monitoring on qry's and proc's?

      Mike

      Author's profile photo John Appleby
      John Appleby
      Blog Post Author

      Well in this world of DevOps, quite a lot of modelers like me have access to this. Certainly if you are a performance guy then your team should give you access to HDBAdmin.

      In the real world I rarely use HDBAdmin - only for certain end-end scenarios - probably once every 6 months.

      I often use the PlanViz tool which will work even on complex views. Also, there is the Expensive SQL Statements analysis, which you can use to find slow SQL. Then you can run it in PlanViz. Between these two tools I can troubleshoot 95% of performance problems.

      Author's profile photo Tian Song
      Tian Song

      Hi Krishna,

      Thanks so much for the info and screen shot, I finally can use HDBAdm.sh ! Super helpful

      Author's profile photo Shiny Annie Besant
      Shiny Annie Besant

      Thanks John. Very useful

      Author's profile photo Vijayan Swaminathan
      Vijayan Swaminathan

      Very precious and informative . Thanks John

      Author's profile photo Aditya Mallavarapu
      Aditya Mallavarapu

      Hi John & Lars,

      Is it still supported to use HDBAdmin.sh ?

      2534881 - Issues while working with HDBAdmin toolย  --> This note says that it is for internal use only.

      2520774 - FAQ: SAP HANA Performance Traceย  --> This note says, that HDBadmin tool can be used and has a pointer to this blog too.

      We also have the SQL Analyzer Python tool in place.

      Request you help to understand better on which tool to be used when please .

      Thanks,

      Phani