Hello and welcome to the SAP HANA Academy.
The topic of this video tutorial series is SAP HANA Administration and in this video
we will take a look at Workload Analysis.
This video has been recorded on release SAP HANA cockpit 2.0 SP08 from October 2018.
For earlier or later releases, please check the playlist on the SAP HANA Academy.
Hi, I am Denys van Kempen.
I am connected here to the SAP HANA cockpit, connected to a tenant database, HA0, and we
are looking at System Overview.
We can access Workload Analysis from all the Usage tiles, CPU, Memory and Disk.
Should you have opened Performance Monitor or Memory Analysis, you can access Workload
Analysis from these apps as well.
In the header we see our connection, the name of the app, of the page, with lateral navigation.
Alerts.
CPU Usage, Memory Usage, and Disk Usage do not bring us back to the Overview page but
load the Performance Monitor with the appropriate chart view.
This topic is covered in the Monitoring Performance tutorial video where we also talked about
the Performance Comparison app.
We have the apps for Capture and Replay, and also for Memory Analysis, which is also covered
in a tutorial on this playlist.
To the right, we can select the page refresh rate and the link to the documentation: SAP
HANA Administration with SAP HANA Cockpit.
OK —then we have our graph.
In the header, we have the presets.
Here you can quickly select a time period range: last 5 minutes, last hour, today, and
so on, but you can also define a custom range, maybe the duration of a particular batch job,
for example.
We can navigate to previous period data and back again.
We can export and import data, a complete snapshot of a number of views.
This will be exported into a zip file.
Primarily intended, I would say, for support purposes (SAP Support) but you might also
have a dedicated team which does the analysis of performance issues separate from the people
who just do system monitoring, could be outsourced.
OK — graph settings.
We have a couple of Key Performance Indicator presets: default, memory, CPU, disk and you
can add your own: Create New
Select the host and/or the services.
Nameserver is the system database, indexserver the tenant, plus a couple of support services
and the XS and XSA processes, should you have this up and running, and then we get all the
KPIs: Host KPIs and Service KPIs.
Host KPIs would come from Linux operating system tools like top, for example.
Service KPIs come from HANA.
Alerts allow you to define alert markers for the graph.
Select the Category and the Priority.
This makes it a bit easier to find the hot spots, the contention areas.
Then, we have the first graph.
This gives us an overview of the selected time period.
The default view, shows for each service all lot of statistics,
You can also just focus on CPU or Memory, for example.
Using the graph, we can make a selection, zoom in, for the area below the graph, with
Top SQL Statements, Background Jobs, Timeline and Threads.
You can modify the size and move it around.
Alternatively, we can also use filters.
This is more advanced.
Select Start and End Time and then you can filter on thread type, thread state, lock
type, lock state, and so on.
This is just a selection.
You can add or remove them here.
Maybe add database user, application user, connection ID.
Which you can then save as a view (Connection), make it your default view, if you want, and
then in Manage Views, you can rename it, or delete it.
Show Filter Bar, toggles the selectors on and off.
OK —let’s go back to our graph.
Info returns System Information, most of it fairly low level.
CPU and Memory come from Linux sysinfo.
Build would be for support purposes.
Below the graph, we can zoom in on our workload.
Top SQL Statements by Lock Wait Time.
Number of Waiting Threads per time interval, here 15 minutes.
Legend shows the statements hash.
You can select it in the graph.
Returns Statement Hash (the identifier of the SQL Statement), Number of Waiting Threads.
SELECT statements you can Open in SQL Analyzer
Below we get some statistics for this statement: Result Record Count, Total Execution Time,
Plan Memory Size, with the actual statement to the right.
Background Jobs shows us what else is going on, on the system and here we have a Delta
Merge operations.
Timeline — takes some time to populate this view — shows the duration of threads.
SAP XSA C HRTT, HANA Runtime Tools is the SAP HANA Database Explorer, which is executing
the statement, here.
Statement Hash and Accessed Table returns Statement Information, we have already seen
this from the Top SQL Statement tab, but here we also find tables accessed, objects accessed,
and the statement.
You can copy the statement and, again, for SELECT statements, open in SQL Analyzer.
Then, finally, we also have the Threads tab.
Primary Dimension shows the same dimensions we have already seen like Thread Type, State
Lock, Statement Hash, Database User, and so on, and in the graph, we then get the relevant
statistics for this dimension.
Thread Type shows SqlExecutor, JobWorker, WorkerThread.
On the Secondary Dimension we then get application name but you could also select Database User,
or whatever makes sense to you.
Thread State shows CPU state: Running, Job Execution Wait, Mutex Wait, Semaphore.
And maybe you want to view this by Thread Method on the Secondary Dimension.
Lots and lots of statistics, lots of data, what does it all mean?
Well, I can recommend SAP article 21 14 710; FAQ SAP HANA Threads and Thread Samples.
Main thread types (7).
Assign: thread method, thread detail and the task performed by the thread with an additional
SAP Note explaining known issues about this particular one.
This goes on and on and on.
It is a reference, and a very good one.
There is a similar FAQ on SAP HANA CPU: 21 000 40
Another one, SAP HANA Memory.
And Lock Analysis
I will add the links to the articles to the description of this video below.
Of course, we also have the SAP HANA Troubleshooting and Performance Analysis Guide.
That’s a good read, as well.
OK —That’s it.
Workload Analysis.
Thanks for watching.
You can find more video tutorials on our YouTube channel.
If you would like to be informed about new video tutorials, please subscribe to our channel.
You can connect with us on LinkedIn or follow us on Twitter, as well for updates, and if
you are watching this video on YouTube, do not hesitate to leave your comments to the
video page and, if you like, give us your vote on this video.
Thank you for watching.