Monitoring SAP HANA using Google Cloud's Agent for SAP

This planning guide focuses solely on the SAP HANA monitoring metrics collection feature of Google Cloud's Agent for SAP. For information about the agent and all its features, see Google Cloud's Agent for SAP planning guide.

On Linux, Google Cloud's Agent for SAP can help you monitor your SAP HANA instances and the underlying infrastructure. This is delivered through the collection of SAP HANA monitoring metrics, which you can enable after installing the agent on your Compute Engine instances or Bare Metal Solution servers.

When you enable the collection of the SAP HANA monitoring metrics, the agent queries your SAP HANA instances and sends the collected metrics to Cloud Monitoring. You can visualize the collected metrics using dashboards in Monitoring. You can also set up alerts in Monitoring based on metric thresholds. For information about creating dashboards and alerts, see Cloud Monitoring documentation.

For information about how to configure Google Cloud's Agent for SAP to collect the SAP HANA monitoring metrics, see Configure SAP HANA Monitoring metrics collection.

The SAP HANA monitoring metrics that Google Cloud's Agent for SAP collects and sends to Monitoring are classified by Monitoring as chargeable metrics and priced by ingested volume.

The sampling interval of your queries, which determines how frequently the agent queries your SAP HANA instances for metrics, affects the volume of metrics that get sent to Monitoring. SAP HANA monitoring metrics are slow-changing metrics that, once enabled, are collected every 5 minutes by default.

For more information about Monitoring pricing, see Google Cloud Observability pricing.

For more information about query-sampling intervals, see Sample interval for queries.

Sample cost estimate

To see a sample cost estimate for the collection of the SAP HANA monitoring metrics using Google Cloud's Agent for SAP, see Pricing example for metrics charged by bytes ingested.

About queries

For collecting the SAP HANA monitoring metrics, Google Cloud's Agent for SAP runs SQL queries on SAP HANA instances. The syntax for these queries are defined by SAP in SAP HANA SQL and System Views Reference.

Google Cloud's Agent for SAP supports the following types of queries:

Default queries
Custom queries

Default queries

The default queries are built-in queries that gather the most commonly used SAP HANA metrics.

The frequency of the default queries is determined by the global sample_interval_sec parameter set at the beginning of the hana_monitoring_configuration section in the agent's configuration file. By default this value is set to 300 seconds (5 minutes). You can modify this global default. Unlike for custom queries, you cannot set a sample interval for an individual default query.

Although you can't modify the default queries, you can disable any of the default queries that you don't need. For instructions to disable a default query, see Disable a SQL query.

For more information about the default queries, the metrics that they collect, and the underlying SQL statements that they use, see Default query reference.

Custom queries

To help you get greater insights into your SAP HANA instances, Google Cloud's Agent for SAP lets you define and run custom queries.

You define custom queries in the queries section, which is specified under the hana_monitoring_configuration section of the agent's configuration file.

For each custom query, you can specify a sample_interval_sec parameter that overrides the default sample_interval_sec parameter. For more information, see Sample interval for queries.

The example custom queries presented in this document can help you write your own queries. For more information about creating custom queries, see Defining custom queries.

Sample interval for queries

For SAP HANA monitoring metrics collection using Google Cloud's Agent for SAP, you can define a global sample interval_sec for all queries and an individual sample interval for each custom query. A sample interval determines the frequency at which the agent queries your SAP HANA instances for metrics.

The default sample interval for all queries is 300 seconds (5 minutes). You can adjust the default sample interval by specifying a different value on the sample_interval_sec parameter at the beginning of the hana_monitoring_configuration section of the agent's configuration file.

You can override the default sample interval for an individual custom query by specifying the sample_interval_sec parameter in the definition of that custom query.

Installation architecture

You can install Google Cloud's Agent for SAP on a Compute Engine instance or on a Bare Metal Solution server.

You can install Google Cloud's Agent for SAP either on the same host as SAP HANA instance or on a different host.

The agent can also monitor SAP HANA instances that are running on on-premises hosts or on hosts on another cloud platform, as long as the hosts have access to Google Cloud APIs with an appropriately configured service account.

Remote collection from a central Compute Engine instance

The following diagram shows the installation architecture, in which the agent is installed on a different Compute Engine instance than the Compute Engine instances or Bare Metal Solution servers running the SAP HANA instances.

SAP HANA monitoring: remote collection of metrics

Local collection on Compute Engine instance and Bare Metal Solution server

If firewall rules restrict access to your environment, then you can install the agent with the SAP HANA instance on the same Compute Engine instance or Bare Metal Solution server.

The following diagram shows the installation architecture, in which the agent is installed individually on a Compute Engine instance and on a Bare Metal Solution server.

SAP HANA monitoring: local collection of metrics

Hybrid collection architecture

You can install the agent on the Compute Engine instance that hosts your SAP HANA instance, and configure the agent to also monitor an SAP HANA instance that runs on a Bare Metal Solution server.

SAP HANA monitoring: hybrid collection of metrics

If you are monitoring SAP HANA instances in multiple Google Cloud projects, then you need to install at least one instance of Google Cloud's Agent for SAP in each Google Cloud project that contains an SAP HANA instance that you are monitoring.

High-availability configuration for the agent

If you are monitoring SAP HANA instances in a high-availability cluster, then you must install Google Cloud's Agent for SAP on a host that is not a node in that cluster, so that the agent is not impacted by failover events.

In the agent's configuration file, we recommend that you configure the monitoring against the floating IP address. With this configuration, the agent collects metrics only from the currently-active node.

SAP HANA monitoring: metrics collection in a high-availability cluster

SSL/TLS Support

If an SAP HANA instance uses the Transport Layer Secure (TLS) or Secure Sockets Layer (SSL) protocol to secure communication between the SAP HANA database and the clients that use the SAP HANA SQL interface, then you must enable the TLS/SSL support in Google Cloud's Agent for SAP. To do so, you must specify the following parameters for all such SAP HANA instances in the configuration file of Google Cloud's Agent for SAP:

Specify the parameter enable_ssl and set its value to true.
Specify the parameter host_name_in_certificate and set the hostname, as specified in the TLS/SSL certificate, as its value.
Specify the parameter tls_root_ca_file and set the path, where the TLS/SSL certificate is stored, as its value.

If SAP HANA is also configured to validate certificates, you can configure the agent to support certificate validation and specify paths and passwords for a keystore and trustore.

For a description of the agent configuration parameters related to SSL, see Configuration parameters.

Defining custom queries

Google Cloud's Agent for SAP lets you define and run custom queries.

A custom query includes the following details:

The parameters that describe the query, and the parameter values.
A SQL statement to fetch data from your SAP HANA instances.
One or more metrics that the SQL statement returns.

For information about query parameters, see Configuration parameters.

For a full list of the system views that SAP HANA makes available for querying, see the SAP HANA SQL and System Views Reference.

Note: A metric of type METRIC_CUMULATIVE is sent to Monitoring only from the second time that Google Cloud's Agent for SAP runs the corresponding query.

When you create a custom query, keep in mind that Monitoring works best for summary or aggregate data and is not intended to store high-cardinality data points. Use SQL aggregate functions such as SUM, AVG, COUNT, MIN, or MAX, to limit the number of records returned.

Because Google Cloud's Agent for SAP treats the columns in a query as an ordered list, you need to make sure to adhere to the following guidelines while defining a custom query:

The SQL statement must use all the columns that you've included in the query definition.
The column order in the SQL statement must match the column order in the query definition.
You've enabled the custom query by specifying enabled: true within a query block as mentioned in the example custom query.

For example, you can see the query definition of the example custom query named custom_memory_utilization. Its query definition includes the columns mem_used and resident_mem_used, in that order. This column order matches the column order in the SQL statement specified for the parameter sql.

To view more examples of custom queries, see Example custom queries.

Example custom queries

The following example shows two custom queries. The example queries are intended for you to use as models to create your own custom queries.

{
  "name": "custom_memory_utilization",
  "enabled": true,
  "sql": "SELECT IFNULL(SUM(TOTAL_MEMORY_USED_SIZE), 0) AS mem_used, IFNULL(SUM(PHYSICAL_MEMORY_SIZE), 0) AS resident_mem_used FROM M_SERVICE_MEMORY;",
  "columns": [
    {
      "name": "mem_used",
      "metric_type": "METRIC_GAUGE",
      "value_type": "VALUE_INT64"
    },
    {
      "name": "resident_mem_used",
      "metric_type": "METRIC_GAUGE",
      "value_type": "VALUE_INT64"
    }
  ]
}
{
  "name": "custom_total_memory_usage",
  "enabled": true,
  "sql": "SELECT IFNULL(SUM(MEMORY_SIZE_IN_TOTAL), 0) AS table_mem_used FROM M_CS_TABLES;",
  "columns": [
    {
      "name": "table_mem_used",
      "metric_type": "METRIC_GAUGE",
      "value_type": "VALUE_INT64"
    }
  ]
}

Error handling for custom queries

Google Cloud's Agent for SAP does not validate the SQL in your custom queries before submitting the queries to your SAP HANA instances.

If a custom query fails with either a SQL exception or if the configured columns in a query don't match the results that are returned by SAP HANA, then the agent writes an error to the log. In both cases, the agent continues to run the custom query and write error messages to the log until there are two consecutive failures, after which the agent stops running that query. To resume running of such a custom query, you need to restart the agent.

For information about the required SQL syntax for custom queries, see SAP HANA SQL and System Views Reference.

SAP HANA monitoring metrics

The following table describes the built-in or default SAP HANA monitoring metrics that Google Cloud's Agent for SAP can collect. The metric strings in this table must be prefixed with workload.googleapis.com/. This prefix has been omitted from the entries in the following table.

Note: A metric of type METRIC_CUMULATIVE is sent to Monitoring only from the second time that Google Cloud's Agent for SAP runs the corresponding query.

Metric	Metric type	Description
`sap/hanamonitoring/column/memory/total_size`	`METRIC_GAUGE`	Specifies the sum of the MEMORY_SIZE_IN_MAIN and MEMORY_SIZE_IN_DELTA columns from the SAP HANA. This metric is collected by the default query `column_queries`, which uses the SAP HANA system view `M_CS_ALL_COLUMNS`.
`sap/hanamonitoring/component/memory/total_used_size`	`METRIC_GAUGE`	Specifies service-specific memory usage by logical component from SAP HANA. This metric is collected by the default query `component_queries`, which uses the SAP HANA system view `M_SERVICE_COMPONENT_MEMORY`.
`sap/hanamonitoring/system/connection/total`	`METRIC_GAUGE`	Specifies the number of idle and running connections of the system. This metric is collected by the default query `connection_query`, which uses the SAP HANA system view `M_CONNECTIONS`.
`sap/hanamonitoring/host/cpu/usage_time`	`METRIC_CUMULATIVE`	Specifies the CPU time spent in milliseconds. This metric is collected by the default query `cpu_queries`, which uses the SAP HANA system view `M_HOST_RESOURCE_UTILIZATION`.
`sap/hanamonitoring/system/alert/total`	`METRIC_GAUGE`	Specifies the number of current alerts from the system statistics: "1", "2", "3", "4", or "5". This metric is collected by the default query `alert_query`, which uses the SAP HANA system view `STATISTICS_CURRENT_ALERTS` of the `_SYS_STATISTICS` schema.
`sap/hanamonitoring/host/memory/total_size`	`METRIC_GAUGE`	Specifies the physical memory on the host. This metric is collected by the default query `host_queries`, which uses the SAP HANA system view `M_HOST_RESOURCE_UTILIZATION`.
`sap/hanamonitoring/host/memory/total_used_size`	`METRIC_GAUGE`	Specifies the used physical memory on the host. This metric is collected by the default query `host_queries`, which uses the SAP HANA system view `M_HOST_RESOURCE_UTILIZATION`.
`sap/hanamonitoring/host/swap_space/total_size`	`METRIC_GAUGE`	Specifies the total swap memory on the host. This metric is collected by the default query `host_queries`, which uses the SAP HANA system view `M_HOST_RESOURCE_UTILIZATION`.
`sap/hanamonitoring/host/swap_space/total_used_size`	`METRIC_GAUGE`	Specifies the used swap memory on the host. This metric is collected by the default query `host_queries`, which uses the SAP HANA system view `M_HOST_RESOURCE_UTILIZATION`.
`sap/hanamonitoring/host/instance_memory/total_used_size`	`METRIC_GAUGE`	Specifies the amount of memory from the memory pool that is currently in actual use by SAP HANA processes. This metric is collected by the default query `host_queries`, which uses the SAP HANA system view `M_HOST_RESOURCE_UTILIZATION`.
`sap/hanamonitoring/host/instance_memory/total_peak_used_size`	`METRIC_GAUGE`	Specifies the peak memory from the memory pool that was in use by SAP HANA processes since start of the instance (sample based value). This metric is collected by the default query `host_queries`, which uses the SAP HANA system view `M_HOST_RESOURCE_UTILIZATION`.
`sap/hanamonitoring/host/instance_memory/total_allocated_size`	`METRIC_GAUGE`	Specifies the size of the memory pool for all SAP HANA processes. This metric is collected by the default query `host_queries`, which uses the SAP HANA system view `M_HOST_RESOURCE_UTILIZATION`.
`sap/hanamonitoring/host/instance_code/total_size`	`METRIC_GAUGE`	Specifies code size, including shared libraries of SAP HANA processes. This metric is collected by the default query `host_queries`, which uses the SAP HANA system view `M_HOST_RESOURCE_UTILIZATION`.
`sap/hanamonitoring/host/instance_shared_memory/total_allocated_size`	`METRIC_GAUGE`	Specifies the shared memory size of SAP HANA processes. This metric is collected by the default query `host_queries`, which uses the SAP HANA system view `M_HOST_RESOURCE_UTILIZATION`.
`sap/hanamonitoring/system/replication_data_latency/total_time`	`METRIC_GAUGE`	Specifies the data replication latency of the SAP HANA high-availability system. This metric is not fetched if your SAP HANA deployment runs only a single node. This metric is collected by the default query `replication_query`, which uses the SAP HANA system view `M_SERVICE_REPLICATION`.
`sap/hanamonitoring/rowstore/memory/total_size`	`METRIC_GAUGE`	Specifies the rowstore memory size, consisting of both the fixed and variable parts. This metric is collected by the default query `rowstore_query`, which uses the SAP HANA system view `M_RS_TABLES`.
`sap/hanamonitoring/schema/memory/total_size`	`METRIC_GAUGE`	Specifies the total memory size as the sum of memory size in the main, delta, and history parts. This metric is collected by the default query `schema_type_queries`, which uses the SAP HANA system view `M_CS_TABLES`.
`sap/hanamonitoring/schema/record/total`	`METRIC_GAUGE`	Specifies the record count. This metric is collected by the default query `schema_type_queries`, which uses the SAP HANA system view `M_CS_TABLES`.
`sap/hanamonitoring/schema/memory/estimated_max_total_size`	`METRIC_GAUGE`	Specifies the estimated maximum memory consumption, in total, for the fully loaded table. Data for open transactions is not included. This metric is collected by the default query `schema_queries`, which uses the SAP HANA system view `M_CS_TABLES`.
`sap/hanamonitoring/schema/record/last_compressed_total`	`METRIC_GAUGE`	Specifies the number of entries in main during the last optimize compression run. This metric is collected by the default query `schema_queries`, which uses the SAP HANA system view `M_CS_TABLES`.
`sap/hanamonitoring/schema/read/total_count`	`METRIC_CUMULATIVE`	Specifies the number of read accesses on the table or partition. This is not the number of `SELECT` statements against this table. A `SELECT` statement might involve several read accesses. This metric is collected by the default query `schema_queries`, which uses the SAP HANA system view `M_CS_TABLES`.
`sap/hanamonitoring/schema/write/total_count`	`METRIC_CUMULATIVE`	Specifies the number of write accesses on the table or partition. This is not the number of DML and DDL statements against this table. A DML or DDL statement might involve several write accesses. This metric is collected by the default query `schema_queries`, which uses the SAP HANA system view `M_CS_TABLES`.
`sap/hanamonitoring/schema/merge/total_count`	`METRIC_CUMULATIVE`	Specifies the number of delta merges done on the table or partition. This metric is collected by the default query `schema_queries`, which uses the SAP HANA system view `M_CS_TABLES`.
`sap/hanamonitoring/service/memory/total_used_size`	`METRIC_GAUGE`	Specifies the amount of memory in use from the memory pool. This metric is collected by the default query `service_queries`, which uses the SAP HANA system view `M_SERVICE_MEMORY`.
`sap/hanamonitoring/service/logical_memory/total_size`	`METRIC_GAUGE`	Specifies the virtual memory size from the operating system perspective. This metric is collected by the default query `service_queries`, which uses the SAP HANA system view `M_SERVICE_MEMORY`.
`sap/hanamonitoring/service/physical_memory/total_size`	`METRIC_GAUGE`	Specifies the physical resident memory size from the operating system perspective. This metric is collected by the default query `service_queries`, which uses the SAP HANA system view `M_SERVICE_MEMORY`.
`sap/hanamonitoring/service/code/total_size`	`METRIC_GAUGE`	Specifies the code size, including shared libraries. This metric is collected by the default query `service_queries`, which uses the SAP HANA system view `M_SERVICE_MEMORY`.
`sap/hanamonitoring/service/stack/total_size`	`METRIC_GAUGE`	Specifies the stack size. This metric is collected by the default query `service_queries`, which uses the SAP HANA system view `M_SERVICE_MEMORY`.
`sap/hanamonitoring/service/heap_memory/total_allocated_size`	`METRIC_GAUGE`	Specifies the heap part of the memory pool. This metric is collected by the default query `service_queries`, which uses the SAP HANA system view `M_SERVICE_MEMORY`.
`sap/hanamonitoring/service/heap_memory/total_used_size`	`METRIC_GAUGE`	Specifies the amount of pool heap memory that is in use. This metric is collected by the default query `service_queries`, which uses the SAP HANA system view `M_SERVICE_MEMORY`.
`sap/hanamonitoring/service/shared_memory/total_allocated_size`	`METRIC_GAUGE`	Specifies the shared memory part of the memory pool. This metric is collected by the default query `service_queries`, which uses the SAP HANA system view `M_SERVICE_MEMORY`.
`sap/hanamonitoring/service/shared_memory/total_used_size`	`METRIC_GAUGE`	Specifies the amount of pool shared memory that is in use. This metric is collected by the default query `service_queries`, which uses the SAP HANA system view `M_SERVICE_MEMORY`.
`sap/hanamonitoring/service/compactor/total_allocated_size`	`METRIC_GAUGE`	Specifies the part of the memory pool that can potentially (if unpinned) be freed during a memory shortage. This metric is collected by the default query `service_queries`, which uses the SAP HANA system view `M_SERVICE_MEMORY`.
`sap/hanamonitoring/service/compactors/total_freeable_size`	`METRIC_GAUGE`	Specifies the memory that can be freed during a memory shortage. This metric is collected by the default query `service_queries`, which uses the SAP HANA system view `M_SERVICE_MEMORY`.
`sap/hanamonitoring/service/memory/allocation_limit`	`METRIC_GAUGE`	Specifies the maximum memory pool size (configurable value). This metric is collected by the default query `service_queries`, which uses the SAP HANA system view `M_SERVICE_MEMORY`.
`sap/hanamonitoring/service/memory/effective_allocation_limit`	`METRIC_GAUGE`	Specifies the effective maximum memory pool size, considering the pool sizes of other processes (computed value). This metric is collected by the default query `service_queries`, which uses the SAP HANA system view `M_SERVICE_MEMORY`.
`sap/hanamonitoring/system/transaction/total_count`	`METRIC_CUMULATIVE`	Specifies the number of update, commit, and rollback transactions. This metric is collected by the default query `transaction_query`, which uses the SAP HANA system view `M_WORKLOAD`.
`sap/hanamonitoring/transactions/blocked`	`METRIC_GAUGE`	Specifies the number of blocked transactions in the database, grouped by database name, host, lock type, and service name. This metric is supported from version 3.5 of the agent. This metric is collected by the default query `blocked_transaction_query`, which uses the SAP HANA system views `M_DATABASES`, `M_SERVICES`, and `M_BLOCKED_TRANSACTIONS`.
`sap/hanamonitoring/backups/data`	`METRIC_GAUGE`	Numeric response code that indicates the state of the last complete data backup. `0`: Data backup executed unsuccessfully. `1`: Data backup executed successfully. This metric includes the following labels: `backup_id`, which specifies the ID of the last complete data backup. `entry_type_name`, which specifies the type of last complete data backup. For example: `entry_type_name: complete data backup`. This metric is supported from version 3.5 of the agent. This metric is collected by the default query `data_backup_state_query`, which uses the SAP HANA system view `M_BACKUP_CATALOG`.
`sap/hanamonitoring/backups/snapshot`	`METRIC_GAUGE`	Numeric response code that indicates the state of the last complete disk snapshot based data backup. `0`: Data backup executed unsuccessfully. `1`: Data backup executed successfully. This metric is supported from version 3.6 of the agent. This metric includes the following labels: `backup_id`, which specifies the ID of the last complete disk snapshot based data backup. `entry_type_name`, which specifies the type of last complete disk snapshot based data backup. For example: `entry_type_name: complete data snapshot`.
`sap/hanamonitoring/backups/log`	`METRIC_GAUGE`	Specifies the state of the last 20 log backups. This metric is supported from version 3.5 of the agent. This metric is collected by the default query `log_backup_state_query`, which uses the SAP HANA system view `M_BACKUP_CATALOG`.
`sap/hanamonitoring/memory/unloads`	`METRIC_GAUGE`	Specifies the total number of table unloads that occurred due to low memory. This metric is supported from version 3.5 of the agent. This metric is collected by the default query `memory_unloads_query`, which uses the SAP HANA system view `M_CS_UNLOADS`.
`sap/hanamonitoring/disk/writetime`	`METRIC_GAUGE`	Specifies the average write time for SAP HANA data and log volumes. This metric is supported from version 3.5 of the agent. This metric is collected by the default query `avg_write_time_queries`, which uses the SAP HANA system views `M_VOLUMES` and `M_VOLUME_IO_DETAILED_STATISTICS`.
`sap/hanamonitoring/disk/readtime`	`METRIC_GAUGE`	Specifies the average read time for SAP HANA data volumes. This metric is supported from version 3.5 of the agent. This metric is collected by the default query `avg_read_time_queries`, which uses the SAP HANA system views `M_VOLUMES` and `M_VOLUME_IO_DETAILED_STATISTICS`.

Viewing metrics in Monitoring

Google Cloud provides custom dashboards that help you visualize the SAP HANA monitoring metrics collected by Google Cloud's Agent for SAP. See the dashboards/google-cloud-agent-for-sap directory in the GoogleCloudPlatform/monitoring-dashboard-samples repository on GitHub.

For information about these dashboards, including installation instructions, see View the collected metrics.

For information about finding metrics data in Monitoring and configuring alert notifications, see Metrics in Monitoring.

Default query reference

The built-in default queries of the Google Cloud's Agent for SAP return the following information from SAP HANA.

If you don't need the metrics provided by a particular built-in or default query, then you can disable it. For instructions, see the appropriate guide:

For Compute Engine instances, see Disable a query.
For Bare Metal Solution servers, see Disable a query.

`column_queries`

The built-in query column_queries returns information about the usage of memory by columns from SAP HANA M_CS_ALL_COLUMNS system view.