SAP HANA : Adjust the data retention time of Embedded Statistics Server tables

Why ?

With the embedded statistics server the retention time for most of the histories is already 42 days.

Nevertheless there are still a few histories with a rather small default retention time. Check ID 750 ("Stat. server tables with retention < 42 days") of SQL: "HANA_Configuration_MiniChecks" (SAP Note 1969700) reports tables where the retention time may be increased to 42 days, however there are no .ini configuration parameters as such to adjust this.

---

How ?

To see all the statistic server tables, associated IDs, and current retention value execute the mini check SQL "HANA_StatisticsServer_Histories_RetentionTime" which can be found in the collection of SQL statements attached to SAP Note 1969700 - SQL Statement Collection for SAP HANA.

Then you can adjust the retention time with the following command:

UPDATE _SYS_STATISTICS.STATISTICS_SCHEDULE SET RETENTION_DAYS_CURRENT=42 WHERE ID=5006;

In this example we are setting the retention of the HOST_HEAP_ALLOCATORS table (id 5006) to 42 days.

Experience has shown that a retention time of 6 weeks (42 days) is a good compromise between size and analysis capabilities. Having 6 weeks of history available it is always possible to compare current issues with a time of the same load one month back (e.g. month's end closing).

Note : The standalone statistics server mainly used a retention time of 30 days. Rather than adjusting the retention time, it is recommended to switch to the Embedded Statistics Server (ESS).