SAP HANA Monitoring views and Record Handling

Dear Readers,

There are various commonly used monitoring views, where we can see the useful statistics getting collected every moment.

Problem :

But can we control the data generation in these views ? Yes. There are some parameters available within SAP HANA itself where you can control the frequency or amount of data getting generated into these views.

Solution :

I was able to collect few such information and sharing below :

— M_ACTIVE_PROCEDURES

This view contains information about currently and recently executed SAP HANA SQLScript procedures. User can use following parameters in order to control volume and retention of the data provided in the view:

1. 1. 1. • indexserver.ini -> [sqlscript] -> execution_monitoring_limit

         • indexserver.ini -> [sqlscript] -> number_of_calls_to_retain_after_execution

         • indexserver.ini -> [sqlscript] -> retention_period (in seconds)

Tracking of active procedures (i.e. populating M_ACTIVE_PROCEDURES) can be completely deactivated depending on the SAP HANA Revision level:

SAP HANA 1.0: indexserver.ini -> [sqlscript] -> execution_monitoring_level = 0

SAP HANA >= 2.0: indexserver.ini -> [sqlscript] -> monitoring_level = 0

This view contains events in context of admission control . The number of records can be limited with the following parameter setting:

indexserver.ini -> [admission_control_events] -> record_limit

The default is 1000000 (SAP HANA <= 2.00.024.00, 2.00.030) / 10000 (SAP HANA >= 2.00.024.01, >= 2.00.031).

— M_CONNECTIONS

This view contains current and recent (history) connections. The retention time and maximum number depends on the following settings:

1. 1. • indexserver.ini -> [session] -> connection_history_lifetime (default: 60, unit: minutes)

      • indexserver.ini -> [session] -> connection_history_maximum_size (default: 100000)

While current connections always have a positive connection ID, the connection ID of history connections is negative.

— M_CS_LOADS

This view is available as of SPS 08 and provides information about load operations in column store. It is based on the existing load trace files. In case of several hosts a significant amount of loads can be contained in the existing trace files. The creation, size and number of load trace files is controlled by the following parameters:

1. 1. • indexserver.ini -> [load_trace] -> enable (default: true)

      • indexserver.ini -> [load_trace] -> maxfiles (default: 10 files)

      • indexserver.ini -> [load_trace] -> maxfilesize (default: 10000000 byte)

Therfore as per as default up to 10 trace files with a size of 10 MB are created (per host and service) before the first trace file is removed.

If in case high number of M_CS_LOADS records causes trouble you can consider reducing the maxfiles and / or maxfilesize parameter (e.g. to 5 and / or 5000000) or disable the load trace completely. As a consequence less or no historic load information will be available for analysis.

M_CS_UNLOADS view gives us information about unload operations in column store. It is based on the existing unload trace files. In case of several hosts a significant amount of unloads can be contained in the existing trace files.

The creation, size and number of unload trace files is controlled by the following parameters:

1. 1. • indexserver.ini -> [unload_trace] -> enable (default: true)

      • indexserver.ini -> [unload_trace] -> maxfiles (default: 10 files)

      • indexserver.ini -> [unload_trace] -> maxfilesize (default: 10000000 byte)

So per default up to 10 trace files with a size of 10 MB are created (per host and service) before the first trace file is removed.

In some exceptional cases the high number of M_CS_UNLOADS records causes trouble ( you can reduce the maxfiles and / or maxfilesize parameter (e.g. to 5 and / or 5000000). As result less historic unload information will be available for analysis.

Further

There are many more monitoring views (mentioned below), you can find all about them here.

M_DATABASE_HISTORY

M_DELTA_MERGE_STATISTICS

M_EVENTS

M_EXECUTED_STATEMENTS

M_EXPENSIVE_STATEMENTS

M_LOAD_HISTORY_HOST, M_LOAD_HISTORY_SERVICE, SAP HANA Studio Load Graph

M_OUT_OF_MEMORY_EVENTS

M_SAVEPOINTS

M_SERVICE_THREAD_SAMPLES

M_SQL_CLIENT_NETWORK_IO

M_SQL_PLAN_CACHE

In case you have idea about some more monitoring views, feel free to comment in this blog post and I can add the content here. Till then, Happy Learning 🙂