Unable to Open Alert History Information Due to Large Table _SYS_STATISTICS.STATISTICS_ALERTS_BASE

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.

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).

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.