Minimized the Statistics Server Memory

The statistics server may collect much more data than required. In order to minimize its memory footprint you should perform the following actions based on the type of the statistics server. All proposed changes have no impact on the analysis capabilities, but they can help to reduce the memory footprint significantly.

General actions:

• If there are particularly large and critical primary keys, you can drop them as described in SAP Note 2143679.
• Avoid retention times of more than 42 days in order to limit the size of the history tables. You can check the currently configured retention times and the actual data retention viaSQL: "HANA_StatisticsServer_Histories_RetentionTime" .

Standalone statistics server:

• If you are at least on revision 74 it is recommended to switch to the embedded statistics server which will no longer use these allocators.
• If you are already in an OOM situation of the statisticsserver it might be difficult to perform the cleanup operations suggested below, because they fail due to the OOM situation. In this case you can temporarily increase the SAP HANA parameter statisticsserver.ini -> [memorymanager] -> allocationlimit (e.g. to 10% or 20%) and perform the cleanup activities. Afterwards you should unset the allocationlimit parameter because the default should be sufficient when the below optimizations are implemented.
• Add "WHERE EXECUTION_COUNT > 0" to the extraction command for HOST_SQL_PLAN_CACHE as described in SAP Note 2084747.
• Manually delete existing records with EXECUTION_COUNT = 0 from HOST_SQL_PLAN_CACHE:

  DELETE FROM _SYS_STATISTICS.HOST_SQL_PLAN_CACHE WHERE EXECUTION_COUNT = 0

• Disable the data collection for HOST_CS_UNLOADS as described in SAP Note 2084747.
• Check according to SAP Note 2084747 if there are other unnecessarily large history tables and adjust the data collection if required.

Embedded statistics server:

• Manually delete existing records with EXECUTION_COUNT = 0 from HOST_SQL_PLAN_CACHE:

  DELETE FROM _SYS_STATISTICS.HOST_SQL_PLAN_CACHE_BASE WHERE EXECUTION_COUNT = 0

• If you are below Revision 85, you should perform this DELETE operation on a regular basis (e.g. weekly).
• Once you have upgraded to Revision 85 or higher, the embedded statistics server will massively reduce the collected data of HOST_SQL_PLAN_CACHE and no manual intervention is required.
• Disable the data collection for HOST_CS_UNLOADS:

  UPDATE _SYS_STATISTICS.STATISTICS_SCHEDULE SET STATUS = 'Inactive' WHERE ID = 5035

• Problems with event acknowledgements can implicitely increase the number of records in GLOBAL_INTERNAL_EVENTS_BASE. Implement the SQL statement attached to SAP Note 2126236 if appropriate in order to improve the events acknowledgement process.