Skip to Content

Recently, a customer says that there are huge amounts of alerts shown in SAP HANA Studio/DBACOCKPIT in one of SAP HANA system which has not been monitored for long time.
Alert Priority.jpg

The alert detailed information page is hanging and does not return or returns errors listed below after clicking high priority alerts for example (the overview page is hanging in the worse situation).

Return Error.jpg

From DBACOCKPIT -> System Information -> Large Tables, I see that the size of table _SYS_STATISTICS.STATISTICS_ALERTS_BASE which contains alert history has more than 30GB.

According to note 2170779 – SAP HANA DB: Big Statistics Server Table STATISTICS_ALERTS_BASE Leads to Performance Impact on the System

Firstly, customer using embedded statistics server with MDC environment, I have to disable embedded statistics server within System DB to prevent endless delete situation (the configuration takes effect immediately, no need to restart HANA DB).

nameserver.ini [statisticsserver] active = false

Secondly, cleanup the old alerts which more than 1 day for example then check and fix the latest alerts which takes around 30 minutes for me.


DELETE FROM "_SYS_STATISTICS"."STATISTICS_ALERTS_BASE" WHERE "ALERT_TIMESTAMP" < add_days(CURRENT_TIMESTAMP, -25);






Then I see the latest alerts and their detail information and try to fix one by one. For alerts do not need to be keep for long time, I set the shorten retetion date.


update _SYS_STATISTICS.STATISTICS_SCHEDULE set RETENTION_DAYS_CURRENT = 10 where ID = 79






Thirdly, enable embedded statistics server.

nameserver.ini [statisticsserver] active = true

Last but not least, I try to persuad customer to monitor the system in their daily or weekly tasks.

Notes:

1. For more information, please refer to SAP HANA Administration Guide SPS 11 -> 2.5.1.7.3 The Statistics Service -> Data Management in the Statistics Service.

2. View _SYS_STATISTICS.STATISTICS_ALERTS is created using the data in table _SYS_STATISTICS.STATISTICS_ALERTS_BASE.

3. 2073112 – FAQ: SAP HANA Studio -> 10. What can I do if opening the overview tab in the administration console takes a long time?

Opening the overview tab in the administration console often suffers from a high amount of SAP HANA alerts. If it takes many seconds or even minutes to open the overview tab, you can check according to SAP Note 2147247 if the number of alerts in table STATISTICS_ALERTS_BASE is too high and perform a cleanup.

To report this post you need to login first.

7 Comments

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

  1. Jan Tuma

    Dear Ning,

    thank you very much for this usefully blog. I have questions related to disabling statistic server in SYSTEM DB. Is there any documentation related to this topic? Statistic server is (besides others) responsible for alert monitoring of the SAP HANA system. By disabling it you are disabling automatic monitoring of the system. Is this monitoring replaced by some other monitoring in your case?

    Jan

    (0) 
    1. Ning Tong Post author

      Hi Jan,

      I forget to write the enable embedded statistics server part. Now update.

      As mentioned in note 217077, It may be that table truncate or delete is blocked and therefore runs endlessly. In this case, please disable embedded statisticsserver before you execute truncate or delete:

      Regards,

      Ning

      (0) 
      1. Jan Tuma

        Hi Ning,

        now it is perfectly clear. Thank you. And thank you for sharing the SAP Note , but I think there must be typo as SAP Note 217077 talks about SEM_BW installation.

        Regards,

        Jan

        (0) 

Leave a Reply