Hi, I am Denys van Kempen, SAP HANA Academy, SAP Partner Engineering,
This video is part of a series where we cover what’s new with SAP HANA Cloud.
In this video, we continue with system administration and performance monitoring with SAP HANA cockpit.
This video was recorded in April 2020.
For the latest material on this topic, please check the playlist.
As with the other videos, we have posted a blog with additional information and references.
See the description of this video below for the link.
If have a question, feel free to post your comments below the video or the blog.
SAP HANA Cockpit, System Overview Filter area monitoring and Administration
In the previous video we covered the Usage cards, Performance Monitoring, Workload Analysis,
Memory Analysis, Buffer Cache monitoring, memory paging, and load units.
So, what’s left?
Well, to the right here we have the Sessions and Threads cards, Top SQL Statements, and
under Monitoring, blocked transactions, and table usage.
Let’s start with Table Usage.
This app is related to Memory Analysis.
It shows the total number of tables in the database with memory used.
This database only contains the SFLIGHT schema, some of you may know it, and a sample table.
We can filter the graph on access reads, access writes, and read/writes
On size, and how many we to have displayed: top 50, top 10
The graph shows a bubble chart.
Click the bubble for some statistics.
Alternatively, view the data in table format.
Sessions, threads, blocked transactions; they are related.
Open Sessions, and we see them listed here.
Or Select a session
and using the bottom menu bar Navigate to
Threads with the same connection ID or blocked transactions with the same connection ID
If we select threads We get the Threads view,
or app if you prefer Same related apps
We can make a selection of the type of information we want to have displayed.
We have seen this before with other views.
We have maybe 10 selected, but there are another 20 to choose from.
Threads are fairly technical Here we can see what services are involved
indexserver is the database data provisioning service is responsible for
smart data integration connecting HANA cloud with other data sources
we see a thread ID calling another thread,
SQLExexcutor calling JobWorker
Thread Methods Thread Details
with the SQL Statement Here we are looking at the statement that
is actually populating the Threads view we see the monitoring views
M_SERVICE_THREADS M_CONNECTIONS,
M_BLOCKED_TRANSACTIONS and M_TRANSACTIONS
If you have some knowledge on the internals of SAP HANA, you can request the Call Stack
Here we have ptime, the in-memory row store doing some work
and here we have TrexThreads from the Trexservice, the column store engine.
No surprise, this is advanced debugging, typically this would involve SAP Support.
From sessions and threads we can navigate to blocked transactions
Here we have a simulation.
The Threads cards shows one thread is blocked.
When we open Sessions, we see the transaction status: lock_waiting
Select and on the menu we can now navigate to the blocking connection, threads, or transactions.
In Threads, the blocked transaction is also listed.
With information about the blocking transaction Who is blocking who, blocked time, transactional
lock type, and so on We can see the SQL statement
We can cancel the operation This option is also available in the Sessions
view.
Requires additional privileges of course and we can navigate to the Blocked Transactions
view to get even more details about the issue
If you select blocked in Threads, you get to the Threads app
If you select Blocked Transactions, you get Blocked Transactions
but as mentioned, there are several options for lateral navigation using either the menu
at the top or more context-aware using navigate to on the bottom
Then we also have the related Monitor Statements app
Overview shows all sessions, active or not we can get the full SQL statement
application sap_xsac_hrtt is the HANA Runtime Tools XSA
component, better known as the Database Explorer the other session is us looking at Monitor
Statements in HANA cockpit.
We can get and set the workload class.
This enables you to define execution priority statement memory limits
query timeout and some other restrictions for a particular user or application, a mapping,
this way you can avoid that a long-running load job hogs the system.
We will cover workload classes in a subsequent video.
For the entry we can navigate to the Session which also includes, we just skipped that,
a summary section with the top 5 users and applications
There is also a Summary for the Threads app and we can save this view also as text, CSV,
or HTML back to Monitor Statements
The Active Statements tab shows active statements
what is currently running similar information
statement string workload class
and from here we can navigate to the next tab
SQL Plan cache Again, we can save this view
We can configure the plan cache, the size
Clear it, when debugging
and also here, our column selector it is available for every view
if your screen is big enough there is a lot to display
the statement string from the plan cache can be analyzed and saved as PLV file.
but we can also directly open the statement in SQL Analyzer,
we have seen this in the previous video, with the plan graph
and very detailed information about the execution plan for analysis
From here we can navigate also to Saved Plans, the PLVs,
to Statement Hints to SQL Plan Stability
and this would bring us a bit more into the domain of development,
same for Expensive Statements another tab on Monitor Statements
We will need to cook something for this one but this we will do in another video.
Did anything change, compared to HANA on-premise for all these views and cards in HANA cockpit?
No, not really.
Same thing.
Except here in Expensive statement, we could enable, configure, the expensive statement
trace.
It is a great way to fill up your file system, if you are not paying attention, as these
trace files can get huge.
For our managed service, SAP HANA Cloud, all traces that go to the file system have been
disabled.
That’s the only difference, the rest, all the views, the apps, the cards, same as before.
Thats’s it.
Thanks for watching.