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