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!

To report this post you need to login first.

14 Comments

You must be Logged on to comment or reply to a post.

      1. Steven Chand

        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

        (0) 
          1. 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

            (0) 
  1. 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

    (0) 
    1. 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

      (0) 
        1. 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

          (0) 
          1. 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

            (0) 
            1. John Appleby 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.

              (0) 

Leave a Reply