SAP HANA Monitoring Views: Control Amount of Records

View

Details

M_ACTIVE_PROCEDURES

M_ACTIVE_PROCEDURES 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:

• 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 

M_ADMISSION_CONTROL_EVENTS

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:

• 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:

• 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

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:

• 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.

M_DATABASE_HISTORY

The contents of view M_DATABASE_HISTORY are based on the following file on operating system level:
/usr/sap/<sid>/SYS/global/hdb/versionhistory.csv

Because of usually small amount of historically installed versions it is usually neither required nor recommended to perform a cleanup. In special cases you can remove entries from the CSV file manually, these adjustments will then also be reflected in M_DATABASE_HISTORY.

If a database backup is restored to a different system, the file content is lost and so you may not see the complete version history of a SAP HANA database. Starting with SAP HANA 2.0 SPS 04 an additional database-internal version history is maintained independent of M_DATABASE_HISTORY.

M_DELTA_MERGE_STATISTICS

The amount of records in the monitoring view M_DELTA_MERGE_STATISTICS can be controlled with the following parameter:

• indexserver.ini -> [mergedog] -> delta_merge_statistics_record_limit (default: 100000)

M_EVENTS

M_EVENTS provides information about SAP HANA internal events, e.g. disk full events or system replication related issues. Normally these events are retrieved and acknowledged by the statistics server. Once they are acknowledged, they disappear from M_EVENTS. A restart of SAP HANA will remove all currently existing entries.
Usually a small amount of records (< 100) can be found in M_EVENTS. If the amount of records in M_EVENTS keep growing, the acknowledgement process may not work as expected for the following reasons:

M_EXECUTED_STATEMENTS

The executed statements trace generally keeps up to 30,000 records in memory.

The following parameter can be used to persist the trace additionally to disk:

• global.ini -> [expensive_statement]-> use_in_memory_tracing (default: true)
• global.ini -> [expensive_statement]-> maxfiles (default: 10 files)
• global.ini -> [expensive_statement]-> maxfilesize (default: 10000000 bytes)

Setting use_in_memory_tracing to 'false' will directly write executed statement trace information to disk without buffering it in memory.

M_EXPENSIVE_STATEMENTS

The expensive statements trace is based on some parameters. The following one defines the size of the memory buffer:

global.ini -> [expensive_statement]-> in_memory_tracing_records (default: 30000)

Setting it to higher values will increase the data volume in M_EXPENSIVE_STATEMENTS.

The following parameter can be used to persist the trace additionally to disk:

• global.ini -> [expensive_statement]-> use_in_memory_tracing (default: true)
• global.ini -> [expensive_statement]-> maxfiles (default: 10 files)
• global.ini -> [expensive_statement]-> maxfilesize (default: 10000000 bytes)

Setting use_in_memory_tracing to 'false' will directly write expensive statement trace information to disk without buffering it in memory.

Avoid a high value for in_memory_tracing_records if you want to keep the size of M_EXPENSIVE_STATEMENTS at a reasonable level.

M_LOAD_HISTORY_HOST
M_LOAD_HISTORY_SERVICE
SAP HANA Studio Load Graph

Read SAP Document 2222110 

M_OUT_OF_MEMORY_EVENTS

The number of records in this view is generally limited to 20 entries, it can't be adjusted.

M_SAVEPOINTS

A hard-coded limit of 128 entries per host and port exists. A new savepoint will overwrite the oldest information. There is no chance for manual adjustment of the buffer size.

M_SERVICE_THREAD_SAMPLES

Service thread samples are a helpful source of information in order to understand the database load in the past. See

The amount of data in the M_SERVICE_THREAD_SAMPLES view can be affected with the following parameters:

• global.ini -> [resource_tracking] -> service_thread_sampling_monitor_max_sample_lifetime (default: 7,200 seconds, i.e. 2 hours)
• global.ini -> [resource_tracking] -> service_thread_sampling_monitor_max_samples (default: 1,500,000 samples)
• global.ini -> [resource_tracking] -> service_thread_sampling_monitor_sample_interval (default: 1 second)

Data in the underlying buffer is overwritten when either the configured life time or the configured number of samples is reached. The cyclic buffer is not reduced. So if the number of samples is lower than previous peak hours, the captured time frame is higher, so that the same number of samples remains in the buffer.

M_SQL_CLIENT_NETWORK_IO

M_SQL_CLIENT_NETWORK_IO view is populated when the SQL client network I/O trace is configured with the help of:

• indexserver.ini -> [sql_client_network_io] -> enabled (default: false)
• indexserver.ini -> [sql_client_network_io] -> buffer_size (default: 1000000 entries)

In order to keep the amount of records in M_SQL_CLIENT_NETWORK_IO at a low level you should activate the SQL client network I/O trace only temporarily. Additionally you can consider reducing the buffer_size setting.

M_SQL_PLAN_CACHE

M_SQL_PLAN_CACHE view contains prepared information for SQL statements. The cache itself and its size can be configured with the following parameters:

• indexserver.ini -> [sql] -> plan_cache_enabled (default: true)
• indexserver.ini -> [sql] -> plan_cache_size (default is version-dependent, e.g.: 2147483648 byte)