Skip to Content

There are various options to checks the performance of a BW on HANA system. Ofcourse you can look at the various transactions which have been around for many, many years:


  • Check the workload of processes with transactions SM50 , SM51, SM66
  • Check the CPU load with transaction OS06
  • Check the system workload with transaction ST03 or ST03N
  • Check the number of logged on users logged with transactions SM04 or AL08
  • Check the database performance with ST04 (similar to looking at the HANA Studio, Administration Console):

/wp-content/uploads/2015/05/1_704975.png

It’s also best practice to take a deep dive into the Early Watch Reporting as it gives a ton of information on the health of your system. I’m still surprised on how often companies don’t make that a regular practice. It can safe you a ton of misery in the future if you do regular checks.

Dealing with a HANA appliance can be somewhat different. Hooking it up to solution manager is an option, but sometimes you just want to do some quick ad hoc checking to get a feeling on the status of the system. This is where the “SQL Statement collection for SAP HANA” comes into play and more specifically, the Mini Checks.

Note “1969700 – SQL statement collection for SAP HANA” comes with a nifty zip file with a ton of SQL statements which can be used to collect information on the health of the system:

/wp-content/uploads/2015/05/2_704976.png

For this blog I want to zoom in on the Mini Checks as they execute a ton of checks including an indication if you have a potential issue or not. To top it off, also note numbers are mentioned. What more can you wish for đŸ˜‰


As you can see in the above screenshot, there are various versions of the Mini Checks:


  • SQL: “HANA_Configuration_MiniChecks_SSS” (minimum set of checks that should work in any environment with the standalone statistics server)
  • SQL: “HANA_Configuration_MiniChecks_Rev70+_SSS” (extended set of checks that only works as of revision 70 and with the standalone statistics server)
  • SQL: “HANA_Configuration_MiniChecks_Rev74+_ESS” (comprehensive set of checks that only works as of revision 74 and with the embedded statistics server)
  • SQL: “HANA_Configuration_MiniChecks_Rev90+_ESS” (comprehensive set of checks that only works as of revision 90 and with the embedded statistics server)


In general you should use the version that fits best to your system environment, so that the most comprehensive set of checks is executed. Therefore it is important to check upfront your HANA revision number and whether or not you are using a standalone or embedded statistics server. The statistics server assists you with monitoring the SAP HANA system, collecting historical performance data and warning you of system alerts (such as resource exhaustion). The historical data is stored in the _SYS_STATISTICS schema.


The new Statistics Server is also known as the embedded Statistics Server or Statistics Service. Prior to SP7 the Statistics Server was a separate server process – like an extra Index Server with monitoring services on top of it. The new Statistics Server is now embedded in the Index Server. The advantage of this is to simplify the SAP HANA architecture and assist in avoiding out of memory issues of the Statistics Server, as it was defaulted to use only 5% of the total memory. SP7 and SP8 still uses the old server, but you can migrate to the new service by implementing note 1917938.

As an example, I’m firing off the “HANA_Configuration_MiniChecks_SSS” statements on my HANA box. A couple of seconds later I get my output in a neatly formatted table:

/wp-content/uploads/2015/05/3_704977.png

In order to be able to analyse the results further, you can export your results to a flat file and import the results in Excel. Filtering on areas which deviate (filter on “X” on column value “C”) gives the areas to focus on:

/wp-content/uploads/2015/05/5_704978.png

Right of the bat we can see a couple of issues in my test system. Backups are not regularly executed (910-945) and disk size is an issue (250). These two can be related as backups might fail due to stuck situations or an overflown file system.

Also it shows I need to do an optimization of compression (560) and my log files sizing is way to small (1610 and 765).

Furthermore it seems auto merge is switched off for a lot of tables. I could deep dive further into which tables are affected and if I need to take action by using another SQL statement which was supplied in the original zip file: “HANA_Tables_ColumnStore_AutoMergeDisabled”

After execution, I get a list of affected tables, including the statement to alter the table settings, how is that for convenience!

/wp-content/uploads/2015/05/6_704979.png

You can find more information on the checks in note 1999993 – SAP HANA Mini Checks. It is the Holy Grail related to HANA Mini Checks.


Thank you for reading. I hope it helps in keeping your HANA system healthy!

Ronald.

To report this post you need to login first.

10 Comments

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

  1. KRISHNA YALAMANCHI

    Here is what i found on the HANA Administration guide http://hcp.sap.com/content/dam/website/saphana/en_us/Technology%20Documents/SAP_HANA_Administration_Guide_en.pdf ( Page 66) available out of the box from SPS 08.

    Many of these metrics listed on this article which I had listed are based on the current transactions which also existed before HANA.

    Question for this thread is – Is there a document which calls out here are the metrics to collect & monitor  your HANA based OLAP system, HANA based OLTP system?

    (0) 
  2. Andy Silvey

    Hi All,

    I’ve got a question for you.

    Is it documented anywhere formally by SAP, the recommendation for the maximum size of a DataFile on a HanaNode ?

    Let’s say there’s a 2tb HanaNode, is it written anywhere by SAP what the recommended maximum size should be fo DataFiles ?

    And consequently, what DataFile size threshold to set and monitor as part of automated monitoring, daily checks and mini checks.

    2ndly, for anyone else reading this blog, I link it to this complimentary blog:

    HANA Daily Monitoring Template

    Best regards,

    Andy.

    (0) 
    1. Amerjit CHAHAL

      Hi Andy,

      I’ve not seen anything that would lend itself to a “recommendation” but you will find the filesize restrictions in the following link.

      Persistent Data Storage in the SAP HANA Database – SAP HANA Administration Guide – SAP Library

      You’ll see that for a EXT3 f/s the maximum filesize is 2TB and f/s size is 16TB. Once you hit the 2TB filesize, HANA will create a new file. If you have a system running GPFS then this restriction is irrelevant (filesize = 8EB).

      For monitoring, I’d be monitoring f/s usage rather than filesizes.

      Hope the above goes some way towards answering what you were asking.

      Kind Regards,

      Amerjit

      (0) 
      1. Andy Silvey

        Hi Amerjit,

        thanks, I am talking about memory usage, I mean if we have a 2tb Node then what is the largest DataFiles we can have with regards to percentage of memory used.

        We know, it is possible to crash a Node if the DataFiles use too much of the available memory, if the DataFiles are a too high percentage of the available memory.

        Hence, what is the official guidance from SAP, regarding maximum usage of memory and consequently maximum size of DataFiles.

        Best regards,

        Andy.

        (0) 
    1. Michael Healy

      Its very straight forward…..go to sap note 1969700 > download the SQL scripts > go to the script called HANA_Configuration_MiniChecks_1.00.120+ > Copy the text > go HANA Studio > Open the SQL editor > Right click and paste in the SQL text > click execute > voila.

      (0) 
  3. Shaik Mohammed Qadir Hussain

    Thanks Michael. It worked but while executing HANA_Configuration_MiniChecks_1.00.120+  it says the below thing. anything can be done for this to get the results ?

    Could not split statements; SQL console content is too large

    Could not execute ‘SELECT /* [NAME] – HANA_Configuration_MiniChecks_1.00.120+ [DESCRIPTION] – General SAP HANA checks …’ in 11.857 seconds .
    SAP DBTech JDBC: [3584]: distributed SQL error: [9] Memory allocation failed

    (0) 

Leave a Reply