Introduction

The following is a how-to guide for the migration from the old StatisticsServer to the new Embedded Statistics Service (ESS). It describes steps to do before, during, and after the migration. Please note, that some steps are not needed if you are using a HANA system based on SPS9 or later. Which steps to be omitted in this case is documented in this guide.  The SQL statements mentioned below have to be executed as SYSTEM user.

What is the Statistics Server?

The statistics server assists us by monitoring their SAP HANA system, collecting historical performance data and warning them of system alerts (such as resource
exhaustion). The historical data is stored in the _SYS_STATISTICS schema; for more information on these tables, please view the statistical views reference page on help.sap.com/hana_appliance.

What is the NEW Statistics Server?

The new Statistics Server is also known as the embedded Statistics Server or Statistics Service. Prior to SP7 the Statistics Server was a separate server process –
like an extra Index Server with monitoring services on top of it. The new Statistics Server is now embedded in the Index Server. The advantage of this is
to simplify the SAP HANA architecture and assist us in avoiding out of memory issues of the Statistics Server, as it was defaulted to use only 5% of the
total memory. 
In SP7 and SP8 the old Statistics Server is still implemented and shipped to customers, but can migrate to the new statistics service if they would like by following SAP note 1917938.

How to implement the New Statistics Server?

The following screen caps will show how to implement the new Statistics Server. We also make note of what your system looks like before and after you perform this
implementation (the steps to perform the migration are listed in SAP note 1917938 as well).

Preparation steps before migration

I. Check of schema _SYS_STATISTICS. If the check is not successful, the migration cannot be performed.

  SQL Statement:  call CHECK_TABLE_CONSISTENCY (‘CHECK’,’_SYS_STATISTICS’,null) “root” access to

II. Check if the preconditions of SAP note 1917938 for migration to ESS are fulfilled. configuration change is required to activate the new statistics server:

nameserver.ini-> [statisticsserver]->active=true

/wp-content/uploads/2015/05/1_725308.jpg

Trigger Migration

The following SQL statement triggers the migration to the ESS. After the migration has finished successfully, there is no way back to the old StatisticsServer!

alter system alter configuration (‘nameserver.ini’,’SYSTEM’) set (‘statisticsserver’,’active’)=’true’ with reconfigure

/wp-content/uploads/2015/05/2_725297.jpg

To speed up the migration, you should truncate large tables in schema _SYS_STATISTICS. That will not have an impact on the system, but you lose some
monitoring data. To identify large tables, execute the following SQL statement:

select table_name, sum(memory_size_in_total) from sys.m_cs_tables where schema_name =’_SYS_STATISTICS’ group by table_name order by 2 desc.

As a rule of thumb, all tables with memory size in main bigger than 50.000.000 bytes should be truncated. The table _SYS_STATISTICS.STATISTICS_LASTVALUES does
not need to be truncated manually, because this will be done automatically during the migration. It is best practice to truncate at least the following tables in schema _SYS_STATISTICS:

HOST_SQL_PLAN_CACHE

HOST_CONNECTION_STATISTICS

HOST_CONNECTIONS

The migration can take a while. This depends mainly on the size of the history tables to be migrated. To monitor the progress of the installation, execute the following SQL statement:

select value from _SYS_STATISTICS.STATISTICS_PROPERTIES where key = ‘internal.installation.state’

/wp-content/uploads/2015/05/3_725298.jpg

While migrating, the result of the above is “Installing, start at: <timestamp>”. Execute this statement repeatedly until the result is “Done (okay) since <timestamp>”. With this result, the migration has finished successfully. If the result is “Done (error) since <timestamp>”, the migration finished unsuccessfully, in which case you have to check note 2006652. If necessary contact the SAP support.

Optionally, you can also view the migration progress in the nameserver trace file and the master indexserver trace file. The trace component(s) to be observed have the prefix “STATS_”. During the migration, SQLScript procedures are created. If this starts, you can see the following trace entry in the nameserver trace file:

[18634]{-1}[-1/-1] 2014-11-06 16:50:49.583107 i STATS_CTRL     
NameServerControllerThread.cpp(00230) : installing…

Meanwhile, you can see in the master indexserver trace installing commands like:

           [18890]{-1}[-1/-1] 2014-11-06 16:51:09.665615 i STATS_WORKER   
               ConfigurableInstaller.cpp(00030) : installing Startup_Preamble (id is 10000)

If the installation is finished, you see the following entry in the nameserver trace file:

            [18634]{-1}[-1/-1] 2014-11-06 16:52:24.886644 i STATS_CTRL     
               NameServerControllerThread.cpp(00255) : installation done

Repeat Step1 and check now

Check of schema _SYS_STATISTICS. If the check is not successful, the migration cannot be performed.

call CHECK_TABLE_CONSISTENCY (‘CHECK’,’_SYS_STATISTICS’,null)

/wp-content/uploads/2015/05/4_725299.jpg

Finalizing Steps after successful migration

After successful migration, check if all ESS objects (collectors and checks) are running. For this, execute the following SQL statement:

select * from “_SYS_STATISTICS”.”STATISTICS_SCHEDULE” where status = ‘Disabled’ and statusreason = ‘timeout’

/wp-content/uploads/2015/05/5_725300.jpg

The returned result set should be empty. However, if it is not empty, some objects ran into timeout. They can be switched on again by using the following SQL
statement:

update “_SYS_STATISTICS”.”STATISTICS_SCHEDULE” set status = ‘Idle’ where status = ‘Disabled’ and statusreason = ‘timeout’

With SPS9 or later, timed out objects will be re-enabled automatically after a certain time, so switching them on again is not necessary in this case.

In case of a scale-out system, all tables in schema _SYS_STATISTICS have to be moved to the master indexserver. With SPS9 or later, this is done automatically during
migration, for other systems implement SAP note 2091256. Moving of tables could take a while, so repeatedly execute the following SQL statement until the
returned result set is empty:

select table_name,location from SYS.M_TABLE_LOCATIONS where schema_name = ‘_SYS_STATISTICS’ and location != (select host||’:’||port from sys.m_services
where service_name=’indexserver’ and detail=’master’)

/wp-content/uploads/2015/05/6_725301.jpg

Check OLD Statistic Server Service

1. Check if the old StatisticsServer service has been removed by executing the following SQL statement:

select * from sys.m_services where service_name=’statisticsserver’

/wp-content/uploads/2015/05/8_725303.jpg

  The result set of the above should by empty, otherwise the service is still running, in which case you have to contact SAP support.

2. Check if the volume of the old StatisticsServer service has been removed by executing the following SQL statement:

select * from sys.m_volumes where service_name = ‘statisticsserver’

/wp-content/uploads/2015/05/9_725304.jpg

3. Check if ESS is active by executing the following SQL statement:

select value from “PUBLIC”.”M_INIFILE_CONTENTS” where file_name =’nameserver.ini’ and layer_name = ‘SYSTEM’ and section = ‘statisticsserver’ and key = ‘active’

/wp-content/uploads/2015/05/10_725305.jpg

The result of the above should be “true”, otherwise the ESS has been disabled, in which case you have to contact SAP support.

4. Check if all collectors and checks are running. Execute the following SQL statement:

select * from _sys_statistics.statistics_schedule where status != ‘Inactive’

/wp-content/uploads/2015/05/11_725306.jpg

In the result set you have to check the column LATEST_START_SERVERTIME. The values of this column should be not null (after initial start of ESS this can take a while), and show the actual time. Furthermore, no objects should be disabled due to timeout. If so, re-enable them by executing the SQL statement mentioned above.

Recommendation for systems which are below HANA 1.0 SPS 9

For systems not on at least SPS9 it is recommended that you check on a regular basis (e.g. once a week) if all collectors and checks are still on schedule by executing the following SQL statement:

select * from “_SYS_STATISTICS”.”STATISTICS_SCHEDULE” where status = ‘Disabled’ and statusreason = ‘timeout’

/wp-content/uploads/2015/05/12_725307.jpg

If the above result set is not empty, re-enable the timed out objects as described above. Furthermore, you get an internal alert for each collector or check that has been disabled.

Solution Manager User Configuration

If you work with the Solution Manager, you have to execute the following SQL statements (cmp. note 1917938):

grant execute on schema _SYS_STATISTICS to <Solution Manager user>

grant update on _SYS_STATISTICS.STATISTICS_SCHEDULE to <Solution Manager user>

Helpful SAP Notes

2092033 Embedded Statistics Service Migration Guide

1917938 Migration of the statistics server for Revision 74 or higher

2006652 SAP HANA Statistics Server – Switch to Embedded Statistics Service fails

2031635 SAP HANA Statistics Server – mail sending does not work completely

2091256 HANA Statistics Server – Embedded Statistics Server is deactivated automatically

To report this post you need to login first.

8 Comments

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

    1. Lucas Oliveira

      Hello,

      Why would you want to have execute privileges over the _SYS_STATISTICS procedures?

      As far as I know, most of the procedures (if not all) are used internally for collecting data and registering alerts on the system so it would not make sense to anybody else but the internal user _SYS_STATISTICS to have those rights. What’s your scenario?

      BRs,

      Lucas de Oliveira

      (0) 
        1. Lucas Oliveira

          If I understood correctly, you don’t need to execute anything. You need export and then drop your _SYS_STATISTICS objects. Anyhow, I can’t help much here since I don’t have a system with the standalone version.

          (0) 
  1. Denis Rossi

    Thanks for your time very eficient doc, take into account the ” must be replace by ‘ for example into :

    select * from _sys_statistics.statistics_schedule where status != ‘Inactive’

     

    Thanks at all

    Denis Rossi

     

    (0) 

Leave a Reply