Hello and welcome to the SAP HANA Academy.
I am Denys van Kempen and the topic of this video tutorial series is SAP HANA 2.0
new features.
In this video tutorial, we will discuss what’s new for administration.
In the previous three videos about administration we talked about system administration,
database backup and recovery, and high availability or system replication to be more precise.
The topic of this video, part 4, is performance monitoring and analysis
and will conclude this series.
What’s new in the SAP HANA platform 2.0 is documented in the release notes,
the What’s New Guide, and for performance management the three main topics mentioned are
Capture and Replay, Workload Analyzer and SQL Analyzer.
The first two tools have been improved, enhanced.
SQL Analyzer is new.
So let’s start with that.
There are a couple of ways how we can access the SQL Analyzer although there is no direct
link or tile.
If you are a developer using the SAP HANA Web-based Development Workbench and you want
to run some SQL statements if you then select the dropdown arrow next to the run icon,
the green arrow, you can see that we have the option to run with ‘Performance Analysis’.
Execute the statement and in the result tab we now have a button to ‘Run plan analysis’.
Open Plan Analysis and you will see three sections: Plan Overview, Operator List, and
SQL Query.
Plan Overview shows the operators and data flow relevant to the plan.
The Operator List shows the different operators, for example: table scan, hash join, column
search, and the SQL Query tab shows the executed query.
For the administrator, we have the HANA cockpit, and in the cockpit, there are three different
access paths to the SQL Analyzer.
Connect to your system and select Execute SQL in the Database Explorer section.
Select your system and again, from the Run menu, we can select Analyze SQL.
Again we have three sections, or tabs: Overview, Operator List, and Tables Used.
The overview shows compilation and execution times, the Dominant Operators, Tables Used,
Result Record Count, Distribution, if any, here we are running the statement on a single node,
and the Memory Allocated.
Operator List then shows more information about the different operators, we already
mentioned them, and Tables Used relevant statistics about the tables involved.
Another access path is either through ‘Expensive Statements’ or ‘Plan trace’ in the Administration
link list.
In Plan Trace, first sort on Execution Time, or filter on the schema or user, for example.
Then, select the More link in the SQL column.
This will display the entire statement with an additional link to open the statement in
SQL Analyzer.
And then we have the same view as in the Database Explorer.
This works exactly the same in the Expensive Statements view.
According to the What’s New Guide, the SQL Analyzer tool is similar to the Plan Visualizer tool
in the SAP HANA studio but if we take a quick look at this tool, then I cannot really
agree with that statement as the Studio Plan Visualizer does exactly that, visualize
a plan, making it visible, showing the two tables scans at the bottom, the hash join to get
the inner join, left and right, joined to another scan, left, right and so on until
we get the result set.
Returning to SQL Analyzer, this indeed does provide the same data but the visualisation
is quit different.
But, who knows, we might also get a plan visualisation in the next version or so.
Next topic on the agenda is Capture and Replay.
This functionality was introduced in the summer, with SAP HANA 1.0 SPS 12.
There is a video in the SAP HANA Administration playlist about how it works, so if you are
not yet familiar, please take a look.
Here I will just show you what has changed.
Three topics.
First one concerns Capture Workload.
In the HANA cockpit, this is in the section Performance Management.
Select to open.
Select Start New Capture, and then in the Configure New Capture screen, we can set the
conditions when the capture may be overwritten.
When the time exceeds a certain number of hours and or minutes.
When the disk usage exceeds a given percentage.
This allows you, for example, to let captures run pretty much unattended and allow them
to be overwritten say every 24 hours, every day, plus set an additional condition
that disk space usage should not exceed a given percentage.
Very convenient.
Next, we have Compare Replay.
After you have done your preprocessing and executed the replay, you can now easily compare
replays.
You can use this, for example, to test the effect of different system parameters, indexes,
data distribution, hardware, and so on.
Just open Replay Workload.
Select Replay List.
Click on Compare Replays Select the baseline replay.
Do you want to compare apples with pears, or pears with apples?
Select the target replay, and click Compare Replays.
This will give us a nice report Here, with a tolerance ratio of 10%,
all statements are comparable.
Which is a good thing, because I am running this demo sample on exactly the same computer,
with me as the only user, so it would be strange to get any other result, but of course in
real life this would be different.
Load shows us CPU and memory usage, statements per seconds, all comparing baseline with target.
Baseline is the solid line and target the dotted line.
This graph is also new in HANA 2.
Then, let’s return to our cockpit.
Next topic is the Workload Analyzer.
We now have two versions.
The one from HANA 1 is called “Analyze Workload based on Thread Samples” and the new one,
introduced with HANA 2, is called “Analyze Workload based on Engine Instrumentation”.
Let’s start with the new one.
Select a capture from the Workload List and select Load Capture.
You can select multiple captures at a time.
Then when a capture is loaded, you can select the capture and open the Workload Analyzer,
and here we see a chart visualising the system resource usage, CPU, memory and so on.
We see the client making the connection.
Here we have HANA studio.
Authentication, Connect, can the statement hash and there is a whole list of KPI’s
that you can get.
The HANA 1 version, based on Thread Samples, as mentioned.
You can select your KPI, and here we have the same list.
And from the header we can easily switch to the Performance Monitor for live statistics,
which is closely related, of course.
For those familiar with the previous HANA 1 Performance Monitor will notice some minor
changes,
same for the Threads. On the summary, you can get the Call Stack
of a statement. Similar changes as well to Sessions, Expensive
Statements, SQL Plan Cache and the Plan Trace.
You can open the new SQL Analyzer directly from the Plan Trace.
We just looked at that.
OK, so much for what’s new for administration with SAP HANA 2.0, part 4, performance monitoring
and analysis.
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 we also have a page on Facebook and Google+.
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.