Skip to Content
Author's profile photo Ronald Konijnenburg

Keeping your HANA system healthy with Mini Checks

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.

Assigned Tags

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

      Good job of advertising the awesome work of Martin Frauendorfer who recently was elected a SAP HANA Distinguished Engineer.

      His SQL Scripts and the series of SAP HANA FAQ notes are definitively not to be missed when working with SAP HANA!

      - Lars

      Author's profile photo Ronald Konijnenburg
      Ronald Konijnenburg
      Blog Post Author

      Glad I could help Lars šŸ˜† . Martin's work is absolutely brilliant!!

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo prabhith prabhakaran
      prabhith prabhakaran

      Hello Ronald Konijnenburg,

      Very nice document.

      Bookmarked.

      BR

      Prabhith

      Author's profile photo Andy Silvey
      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.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Andy Silvey
      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.

      Author's profile photo Former Member
      Former Member

      Hi,

      I want to runĀ HANA_Configuration_MiniChecks_1.00.120+ but can somebody please help me on how to execute this ?

      Regards

      Qadir

      Author's profile photo Michael Healy
      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.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Ravindra Channe
      Ravindra Channe

      Hi,

      If you read the OSS note 1969700, you will get the answer :-). Still find it below.

      This warning is issued by the SAP HANA Studio (SAP NoteĀ 2073112) SQL editor when SQL statements with a length of more than 200,000 characters are executed. It means that the statement is executed as a whole and SAP HANA Studio doesn't search for semi-colon (";") characters as statement separators. This is an acceptable behavior for the statements being part of this collection, so you can ignore the warning.

      It is possible to adjust the threshold of 200,000 characters in SAP HANA Studio via "Window" -> "Preferences" -> "SAP HANA" -> "Runtime" -> "SQL" -> "Maximum Numbers of Characters for Multiple Statement Execution".

      Regards,

      Ravi

      Author's profile photo Delia Ciurgauan
      Delia Ciurgauan

      Hey,

      There are two errors listed in the initial comment:

      1. "SQL console content is too large" - as you said, this can be ignored, because HANA Studio anyhow returns the result of the SQL statement.
      2. "Memory allocation failed" - this is actually the problem. And when encountering this, HANA Studio doesn't return a result when running the MiniChecks.

      So, if anyone did some troubleshooting on the memory allocation issue while running MiniChecks and got some positive results, please comment here, because this issue is still valid even after 3 years since this post was made.

      Author's profile photo Pritesh Dubey
      Pritesh Dubey

      There is an OSS note 2901387 has been released for this specific issue. As per this, the problem might be because SAP Host Agent and SAPOSCOL are not running well or not running at all. Or the permission for /tmp directory is not properly set.

      Ā 

      Author's profile photo Sergio Anduaga
      Sergio Anduaga

      Hello Colleagues,

       

      Is there a way to save the query results on the server disk as a Log ?

      Author's profile photo Pritesh Dubey
      Pritesh Dubey

      If you are executing it from HANA studio then right click on query results and export the results to a file. Then you can convert that into a csv file to analyze the results.

      Same way you can download the results from DB02->Sql editor if you are running the query in SAP GUI.

      Author's profile photo Tatjana Schumakowa
      Tatjana Schumakowa

      Hi All,

      How can I run HANA mini checks without HANA Studio?

       

      Many Thanks

      Best Regards

       

       

      Author's profile photo R. Konijnenburg
      R. Konijnenburg
      Blog Post Author

      By using the SQL editor in ST04

      Author's profile photo Sumit Jaiswal
      Sumit Jaiswal

      Hi Tatjana,

      You can download SQLStatements.zip from note 1969700 and import the zip file in HANA studio.

      Step-wise details in 2841512 - How to: Import predefined SQL script to HANA studio or HANA cockpit