Skip to Content

For me, HANA is simply put a database from and for SAP. A database with special features, sure. Being a database administrator makes me think about it in a biased way. What do DBAs do with HANA? Actually this is a pretty simple list:

1. Patching, patching, patching
2. Make sure to have valid backups
3. Restore+Recovery in case of need

All other HANA specific tasks could almost be neglected. Most modern databases are made for low TCO, but SAP has taken this to some extreme level, leaving the administrator almost nothing else to do except my three tasks listed above. You can change HANA parameters for example, but it doesn’t make sense unless that is recommended by SAP.

So here I could stop my blog. But wait! Christmas time is approaching quickly, so I deem this is the perfect time to compile my personal HANA wish list. As usual there is no guarantee to be heard, but not even asking would be the worst approach.

Although HANA provides quite a lot of these monitoring views with the prefix “M”, the actual insight into the database operation is quite coarse. I really like the expensive statement tracing (in file indexserver.ini section [expensive_statement] enable=true), and I am positively surprised that the corresponding monitoring view M_EXPENSIVE_STATEMENTS even contains a column STATEMENT_ID. However, this STATEMENT_ID is not a hash value of the actual SQL statement, but some arbitrary number which makes it somewhat useless. Even worse, this STATEMENT_ID seems to be referenced only in monitoring views M_PREPARED_STATEMENTS and M_EXPENSIVE_STATEMENTS and nowhere else.

1. Wish: Why not using a hash value for the STATEMENT_ID? And please make this STATEMENT_ID available in more views, e.g. M_SQL_PLAN_CACHE.

With SAP pricing the HANA solution via memory consumption, this makes memory consumption a hot topic. The database should provide more detailed information about how much memory is used for which aspects. The most detailed breakdown I have found so far is in view M_MEMORY_OBJECTS, but this is not sufficient to get a feeling for what is normal memory consumption. After all, the numbers from M_MEMORY_OBJECTS correlate very well with other tools like e.g. top, raising my confidence in that view. Often I see numbers from HANA which contribute more to confusion about memory consumption than anything else.

2. Wish: Please provide more detailed information what the memory is actually used for.

SAP can be very proud to have a very consistent documentation. Unfortunately, in almost every aspect I am involved the documentation is also very rudimentary, simply describing the obvious. Or, not much better: It is inventing new technical slang in a very solipsistic way. If my description was not clear, then just have a look at anything Oracle Corp. has ever published on their database software. It is no wonder that for a brand new product like HANA the documentation is also “work in progress”. However, with my experience so far with technical SAP documentation I fear the current status for HANA documentation might not change significantly.

3. Wish: Please provide more concepts and background information on how the HANA database works internally with direct references to the respective monitoring views. Additionally, some more words on what the various HANA monitoring views actually show is highly appreciated.

Doubt is a serious problem for database administrators. Even more important than availability and performance is data security. Therefore HANA also needs a tool to verify the integrity of the database. I heard rumors that such features are in the pipeline, so let’s wait for HANA 1.0 SPS05.

4. Wish: SAP, please don’t forget to pack the consistency check tool into this year’s parcel!

Coming to think of it, if I combine a consistency check and documentation, what could I get? A consistency check for the various ini files! The life of the DBA could be simplified if there were such a tool, together with a more detailed explanation on the HANA parameters. Of course, we only change parameters due to SAP’s recommendations and HANA typically works well with default parameter settings. But anyway, I believe such a tool makes sense because currently HANA accepts every nonsense. (E.g. the unit is not always clear for HANA parameters. Bits? KBytes? Blocks?)

5. Wish: Please evaluate the possibility of a parameter check tool.

SAP has already announced this, but being a DBA I simply need to have a backup integration! I know that SAP provides a reference backup script in SAP note 1651055, but HANA doesn’t feel like a real database as long as it doesn’t provide a real backup integration.

6. Wish: SAP, take your required time, but please release HANA 1.0 SPS05 with the backup integration soon!

Maybe this is least important on my wish list, but I’ll state it nevertheless. Lightweight HANA to HANA replication would be a really cool feature. I do not mean disaster recovery scenarios with heavy replication, but for system migrations via sending logs over WAN connections. A “near zero downtime” migration from one datacenter to another is the ultimate goal for any migration consultant. Only very few HANA instances are productive yet, so this is more like an outlook of the future.

7. Wish: I wished that migrations could be done almost without interruption.

So now I kept the best for the end. In my opinion HANA has not only one but two size metrics. The classical metric is “how much data is stored inside the database” and can be expressed in the number of used bytes in the datafiles. Then there is the SAP license metric with the amount of used memory, which contains not only data but also temporary and auxiliary data structures. It is easy to query the database for these metrics, so one might wonder why these numbers are not also shown in transaction DB50, but anyway this is not my point. I really wonder why the database doesn’t keep a history of these metrics. One of the first things HANA customers might do is create scripting solutions to gather and keep such information. I deem is so important that I believe it is only a matter of time until the HANA database will provide it.

8. Wish: Please provide HANA database size and memory usage metric histories inside the database.

That’s all for this year. I am hoping for a wide adoption of SAP HANA in the SAP universe. So next year I might return with even more wishes!

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Ravindra Channe

    Hi Mark,

    Great Blog. Hope these wishes are fulfilled in SP05.

    I also wish for a good and easy to use debugging tool to debug the Procedure code. Not sure when shall we expect that.

    Regards

    Ravi

    (0) 
    1. Mark Förster Post author

      Hi Ravi,

      thanks for the kind words. The best way to find out would be to attend SAP TechEd and identify the right person to ask. And of course to find some colleagues or friends who support your idea. Too bad I cannot attend TechEd this year. Writing a blog won’t be anywhere as effective as bothering influential SAP staff in person.

      Regards,

      Mark

      (0) 
    1. Mark Förster Post author

      Hello Chaim,

      now that was quick response from SAP on my wish list, thanks for the link! I am glad I asked, because I wouldn’t have found that document on my own.

      Regards,

      Mark

      (0) 
    2. Ravindra Channe

      Hi Chaim,

      Thanks for the nice document you have put together. In my experience it gets difficult to reconcile different figures from the information as the information comes from different sources. Row store and column store (data part of HANA) may come from what has been allocated and what has been actually used. In a layman’s term if I see that used memory at any point of time is 300 GB, then I am likely to see row store tables (M_RS_TABLES) as 30 GB, Column store tables from M_CS_TABLES as another 40 GB. The remaining 130 GB might have been allocated but not completely used. It could also be partially used in the Temporary Computations, but it is very difficult to identify where and how it is being used.

      Secondly it is difficult to identify which query execution is currently occupying how much memory as most of the time, the memory utilization is investigated when the Query execution fails with out of memory error. So it would really help to understand what is occupying memory at a given time and how can I reconcile the figures from different sources.

      Regards,

      Ravi

      (0) 
      1. Chaim Bendelac

        Hi Ram,

        QPic is an internal SAP HANA tool; similar functionality (and superior in some ways) has been introduced in the “Query Plan Visualization” tool, that is part of the HANA Studio.

        (0) 
  2. Wolfgang Auer

    Hello Mark,

    >> Please provide HANA database size and memory usage metric histories inside the database <<

    Do you know the HANA StatisticsServer? There are several system tables regarding memory that are historised by the StatisticsServer.

    http://help.sap.com/hana/html/sys_statistics_views.html

    HOST_HEAP_ALLOCATORS

    HOST_RESOURCE_UTILIZATION_STATISTICS

    HOST_SERVICE_MEMORY

    GLOBAL_ROWSTORE_TABLES_SIZE

    HOST_COLUMN_TABLES_PART_SIZE

    Regards, Wolfgang

    (0) 

Leave a Reply