HANA Expensive Statements Test

Expensive statements are individual SQL queries whose execution time exceeded a configured threshold. By default, this threshold is 1 second (1000000 microseconds). Such statements, besides being a resource-drain, may also block the execution of other SQL statements, degrading the performance of the database server in the process. To avoid such eventualities, these long-running statements need to be isolated and optimized rapidly. This is where the HANA Expensive Statements test helps. This test reports the count of expensive SQL statements and the maximum duration for which such statements ran. Using the detailed diagnosis of the test, you can also identify the expensive SQL statements and attempt to fine-tune them in order to improve their performance, and consequently, enhance server performance.

Target of the test : A SAP HANA Database Server

Agent deploying the test : A remote agent

Outputs of the test : One set of results for every SAP HANA database server being monitored.

Configurable parameters for the test
Parameter Description

Test period

How often should the test be executed

Host

The host for which the test is to be configured.

Port

The port number at which the specified Host listens to. By default, this will be 30015.

User

In order to monitor a SAP HANA database server, a special database user account with Monitoring privileges has to be created in every SAP HANA database instance that requires monitoring. The syntax of the script that is used for user creation is discussed in How to Monitor SAP HANA Database Server Using eG Enterprise?.

The name of such a user has to be specified here.

Password

Enter the password of the specified User.

Confirm Password

Confirm the password by retyping it here.

IsPassive

If the value chosen for this parameter is Yes, then the SAP HANA database server under consideration is a passive server in a SAP HANA cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable” by the agent if the server is not up.

Exclude Empty Objects

Typically, the detailed diagnosis of this test reports the top-10 (by default) queries to SAP HANA in terms of execution time. In many environments, it was noticed that the less critical background jobs (eg., Create, Alter, Backup etc.) hogged this top-10 list, because they normally take a long time to execute. The crucial user queries on the other hand, were often omitted from this top-10 list, as they usually take less time to execute than the background jobs. To enable administrators to focus on expensive user queries instead of background jobs, you can use this flag. By default, this flag is set to No. This means that, to identify the top-10 queries (in terms of execution time), this test will consider even those queries for which the output does not contain the object name. Since background jobs usually do not have object names in their output, this default setting implies that such jobs will by default be considered when generating detailed metrics. To disregard these jobs, set this flag to Yes. In this case, the test will only look at the execution time of those queries that return an output containing object names to generate the top-10 expensive queries list. This automatically excludes background jobs from detailed diagnosis.

DD Records

Specify the number of expensive SQL statements for which the detailed analysis is to be reported in the DETAILED DIAGNOSIS section. By default, the value specified in this text box is 10. This indicates that detailed analysis of the top 10 expensive SQL statements executing on the SAP HANA database server will alone be listed in the DETAILED DIAGNOSIS section.

Detailed Diagnosis

To make diagnosis more efficient and accurate, the eG Enterprise suite embeds an optional detailed diagnostic capability. With this capability, the eG agents can be configured to run detailed, more elaborate tests as and when specific problems are detected. To enable the detailed diagnosis capability of this test for a particular server, choose the On option. To disable the capability, click on the Off option.

The option to selectively enable/disable the detailed diagnosis capability will be available only if the following conditions are fulfilled:

  • The eG manager license should allow the detailed diagnosis capability
  • Both the normal and abnormal frequencies configured for the detailed diagnosis measures should not be 0.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Statement Count

Indicates the total number of expensive SQL statements executed on the SAP HANA database server.

Number

A low value is desired for this measure. A gradual/sudden increase in the number of expensive SQL statements can hamper the performance of the SAP HANA database server. Some of the consequences of executing the expensive SQL statements are as follows:

  • While an expensive SQL statement is being executed, as it has to fetch more blocks, already existing blocks in the database buffer are displaced to accommodate the blocks of this statement. This impacts performance of the other SQL statements and could impact performance of subsequent requests.
  • As the database will be busy readying many blocks to satisfy expensive SQL statements, other critical database tasks are very likely to slow down, which eventually may impact the performance of the SAP HANA database server
  • While an expensive SQL statement is being executed, a work process is blocked till the completion of the request. So, it is not available for other requests which increases the wait time of other processes.

You can use the detailed diagnosis of this test to identify the expensive statements and optimize them for better performance.

Maximum Duration

Indicates the maximum time duration taken to execute an expensive SQL statement on the SAP HANA Database server.

MilliSec

A high value for this measure is a cause of concern. In order to fine tune the time duration, you may have to optimize the query by changing the query structure or adding index to the database table.