In a prior blog we provided some tips on Managing HANA memory. In this article we will further explore the improvement with ESS (Embedded Statistics Service) available on HANA from SPS 7 revision 74 or higher. As a quick recap, memory consumption by the hdbstatisticsserver can be heavily impacted by the volume and frequency of various statistics collected by the HANA statistics engine, mainly the columnar tables in _SYS_STATISTICS schema. These tables provide stats for monitoring by tools such as the HANA Studio, SAP DBA Cockpit and Solution Manager, as well as external SAP management tools like IT-Conductor and HANA Management Pack for SCOM.
Prior to HANA revision 74, the performance data are held in the persistence of the statistics server. If you ran HANA in earlier releases, and have upgraded to a minimum of revision 74, you have the option of migrating the statistics data to the persistence of the master index server and subsequently distributing the functions of the statistics server to other services. See SAP Note 1917938 - Migration of the statistics server for Revision 74 or higher for further details. The idea is that overall memory consumption of the HANA system should be reduced and after the migration, which is done online with no impact to HANA database availability, and there will no longer be a hdbstatisticsserver service on the system.
We set out to test this on our single-node lab HANA system running SPS 8 revision 80, which was originally deployed as SPS 7 revision 73 and subsequently upgraded. The procedure would work similarly for scaled-out HANA environments.
The steps are manual and various checks must be done during the migration. The details can be found in SAP Note 2092033 - Embedded Statistics Service Migration Guide. The duration of the migration would mainly depend on how much cleanup was done on the large statistics table prior to the migration. SAP recommends to truncate those tables larger than 50 MB. In our case, after cleaning up about 4 GB of persistent data, we were able to complete the migration in about an hour. Notice the screenshot below no longer shows the hdbstatisticsserver.
Below is the result of saving in memory after the statistics server was embedded, which seemed to free up ~3 GB of real memory. There may also be CPU cycle saving with one less service, but we did not conduct a test to compare.
There are a few catches to migration to ESS if you are using DBA Cockpit and/or Solution Manager for HANA monitoring. The ABAP server has to be adjusted, and you would need to plan for the extra effort, including testing. See SAP Note 1925684 - ABAP adjustments for the new Embedded Statistic Server. It wasn't clear why this was needed, but we have a pretty good idea following the migration, as we noticed all the columnar statistics tables were now renamed with a suffix of _BASE. So obviously, the ABAP code that drives DBA Cockpit and Solution Manager needed to be adjusted. The good news is that our IT-Conductor and HANA MP for SCOM tools use HANA performance views and did not require any adjustments. In fact, we automatically picked up and monitored the new tables and dropped the monitoring of the HANA hdbstatisticsserver service.
The statistics collection and data retention are controlled by many HANA jobs, and they need to be monitored as some may fail due to timeout and become inactive. In a future blog we will discuss how we can automate the monitoring and management of these jobs to ensure smooth HANA database operations providing accurate performance statistics.
Have you migrated to HANA ESS, if so what's been your experience?
Would you like to have some of this work automated and continued to be monitored for health? We invite you to sign up to see how it could easily be achieved!