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 the table usage monitor.
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.
In the Monitoring section here we find a number of links and one of them is Monitor table
usage.
Select it and the Table Usage app will open.
The objective here is to identify large tables which are infrequently accessed.
Large tables, which are queried a lot, of course, should be in-memory.
Small tables, which are not used a lot, are fine to keep in-memory as well, as long as
you do not have too many of them.
But large tables which are rarely used, typically, you do want to store in-memory.
From a cost perspective this does not make a whole lot of sense.
So, what do we got?
In the header, we have the system identifier (HA0), the number of tables (305) and the
number of hosts (1, in our case, single-host system).
We are running HANA on a 64 GB server but so far not much memory is occupied for table
storage.
If we go back one moment, and select Analyse Memory History from the Memory Usage tile,
we get the same information.
Almost 60 GB is allocated but Total Used Memory is only a little over 4 GB, and if we scroll
down, and select the Components tab, we can see this illustrated, most of the memory in
use is to run the system.
If we select the Tables tab, we can see the top 10 tables by Size and by Growth (last
24 hours).
So, this should provide you with some background information for table monitoring.
Back to Table Usage.
There are multiple alerts to support you with table monitoring.
If we select Go to Alerts, and then from the header, select Configure Alerts, we get a
list of all the available alerts (120), and whether they are active or not.
If we filter on table, we have, for example, Table growth of non-partitioned column-store
tables, total memory usage of column-store tables, and so on.
Let’s go back to System Overview.
Monitor table usage.
After the header, we have the filter area.
Like I said, the objective is to filter out large table with relatively little access.
So we have a selector on the number of Reads, the number of Writes, or, if you want, Reads
and Writes combined.
Greater than, Equal to, Less than, Not equal to (covers them all, I would think).
Same for size, in megabytes, giga or even terabytes.
And then display Top 50, Top 10, as you wish.
When you are satisfied with your criteria, click Go.
Reset will bring you back to the default filter suggestion.
The result will be a graph, Column Tables by Usage, with total writes on the Y-axis
and Total Reads on the X.
The size of circle gives an indication of the size of the table.
When you select the circle, you get the numbers.
Number of partitions, total reads, total writes, and size in MB.
This is the bubble chart, we can also get the raw data, in a table view.
Once you have identified large in-memory tables which are rarely read or modified, you could
consider to move them to warm storage.
In-memory being considered as hot.
The SAP HANA dynamic tiering option would be an excellent choice for warm storage, for
large tables, less frequently accessed: a more cost-effective solution.
There is a playlist about Dynamic Tiering on the Academy should you want to learn more.
I should also mention the Data Distribution Optimizer, part of SAP HANA Data Warehousing
Foundation, another option, which allows you to automate data management and data distribution
in an SAP HANA landscape, and we have some videos about this option as well.
How this works.
OK —That’s it.
Monitor Tables.
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.