If you want to perform a sizing on an existing SAP HANA Database one key resource is the CPU utilization. To understand the technical resource consumption multiple different options exist. However, till now it was very cumbersome to identify at least the main workload drivers in a top-down way. Mainly, this was done manually using the SQL Plan Cache, for example.
Now, the SAP EarlyWatch Alert (EWA) Dashboard has been extended by a new “Analyze CPU Utilization” feature which provides the application source of the top SQL statements. For all of us who perform performance and capacity management analyses, it offers a quick glance into individual contributors, aligns SQL statement hash and long text, as well as information on the tables called to identify “hotspot tables” and information on the frequency of execution. Perhaps more than the weekly view the drill down to a specific hour is helpful to understand if a particular usage is relevant from a performance and/or sizing perspective.
The functionality is easy to consume, here are the steps. The starting point is the CPU Utilization detail page in the SAP EarlyWatch Alert Dashboard that hopefully you are using already intensively.
In the detail page for the SAP HANA Database CPU utilization, choose Analyze CPU utilization, and you will get the aggregated top 10 consumers as an overview. In the example below, from a non-production system, you can see that different statements dominate the total CPU consumption on different days.
All other applications are aggregated to “other applications”. You can, however, drill down to an hour you are particularly interested in and get the top statements from that time. Just choose an hour by clicking on a bar.
CPU application sources
This will lead you to a detailed screen which offers information on the SQL hash, but also the application source. If you choose SQL statement, you will get textual information on the statement itself in a pop-up. One nice feature is that the HANA tables called by a statement are also displayed.
The metrics on the SQL Statements are to help you assess the statement a little bit further. Active time refers to the number of times the statement was in the snapshots. For example, if in one hour 100 snapshots were taken, and the statement was active in 10 of them, the active time per hour would be 10%. This indicates if a statement is expensive and not run frequently, or if it is cheap and run frequently. The average number of executions means how often a statement is called by the application. If we have 10000 executions per hour, the average number of parallel executions is 3.3. Threads per statement refers to the average number of HANA threads that are used. The lower the number of threads, the less CPU intensive the individual statement.
CPU workload analysis - details on SQL statements
Having used this feature for a while now, it has been helping me a lot in shedding light on the key CPU resource drivers. To keep the data manageable we decided to focus on the top statements only. Here’s how I am using the CPU analysis:
- Take the EWA CPU technical utilization page over 4+ weeks to understand the load pattern, possibly long-term, and average per hour.
- If there are interesting weeks or days in terms of intensive CPU consumptions, I drill down to week, day or hour, and choose the detailed information.
- If there are indeed individual SQL statements I check if they are CPU intensive because of a performance problem or because they are simply a) called frequently or b) perform heavy duty calculations.
- In some cases I do a deep dive - but in the system itself. To keep the Early Watch Alert as clean as possible, we only focus on the key drivers.
The subsequent questions I ask myself are: Is the application sizing relevant and/or would it make sense to analyze the CPU consumption for performance optimization potential?
I am very much looking forward to an exploration colleagues are currently performing: Can we add an alert if our intelligent analysis detects a potential known performance challenge?
In case you want to access the EWA Dashboard directly: https://launchpad.support.sap.com/#/ewasop/soopDetail
- SAP Managed Tags:
2 Comments
