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):
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:
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 :wink:
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:
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:
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!
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.
- SAP Managed Tags:
20 Comments
