HANA Workload Test

The workload of the SAP HANA database server is typically characterized by the preparation, compilation, and executon of query statements for data definition, system control, and data manipulation such as inserts, updates, commits, and rollbacks. One of the key indicators of the performance of the server is its ability to handle this workload! While sudden/consistent surges in the load on the server can cause queries to queue up and choke the server, an unexpected slowdown in transaction execution can adversely impact the user-perceived performance of the server. Besides, the type of statements being submitted to the server for execution also affect its processing efficiency – for instance, rollbacks are basically expensive operations, and need to be kept at a bare minimum; a large number of rollbacks can hence, greatly degrade server performance. A healthy SAP HANA database therefore, is one which is not only moderately loaded, but has little-to-no rollback statements to execute, and is capable of processing its load in minimal time! To ensure the good health of the SAP HANA database, administrators should be able to:

Proactively detect a potential overload/processing slowdown;
Isolate the exact service that is facing the brunt of this problem condition;
Understand the nature of the load on that service and identify the query types that could be contributing to the high load/slowdown

The HANA Workload test enables administrators to achieve each of the goals outlined above. This test auto-discovers the port at which each service on the server listens, and for each port reports the total load on that port (i.e., service), the type of transactions handled by that port – whether updates, commits, or rollbacks, and the rate at which that port processes the load. This way, administrators can tell which port (i.e., service) is overloaded and/or is experiencing a processing bottleneck, and what type of transactions are responsible for this problem condition – updates? commits? or rollbacks?

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 each service port of the 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.

Measurements made by the test
Measurement	Description	Measurement Unit	Interpretation
Execution Count	Indicates the total workload of this port/service during the last measurement period.	Number	This is the total count of all executed statements for data manipulation, data definition, and system control, and is a good indicator of the workload of the port/service. By comparing the value of this measure across all ports, you can identify the overloaded port/service.
Compilation Count	Indicates the number of compilations performed by this port/service during the last measurement period.	Number	Ideally, the value of this measure should be less than the value of the Execution Count measure. When the Compilation Count measure is higher than the Execution Count measure, it indicates that the same query is compiled too many times which in turn is a problem that needs to be probed immediately.
Update Transaction Count	Indicates the number of records that are updated in the SAP HANA database through this port/service during the last measurement period.	Number	Once you are able to precisely point to the overloaded service/port, you may want to compare the value of the Update Transaction Count, Commit Count, and Rollback Count measures for that service/port, to figure out which type of transaction is the maximum on that port. A high number of rollbacks is a cause for concern, as it increases processing overheads.
Commit Count	Indicates the number of transaction commits that happened in the SAP HANA database through this service/ port during the last measurement period.	Number	Once you are able to precisely point to the overloaded service/port, you may want to compare the value of the Update Transaction Count, Commit Count, and Rollback Count measures for that service/port, to figure out which type of transaction is the maximum on that port. A high number of rollbacks is a cause for concern, as it increases processing overheads.
Rollback Count	Indicates the number of transactions that are rolled back in the SAP HANA database through this service/port during the last measurement period.	Number	Once you are able to precisely point to the overloaded service/port, you may want to compare the value of the Update Transaction Count, Commit Count, and Rollback Count measures for that service/port, to figure out which type of transaction is the maximum on that port. A high number of rollbacks is a cause for concern, as it increases processing overheads.
Rollback Percent	Indicates the percentage of transactions that are rolled back in the SAP HANA database through this service/port during the last measurement period.	Percent	Compare the value of this measure across services/ports to know which service/port executed the maximum number of rollbacks. Any value higher than 30 is an indicator of bad performance, since work performed up to the rollback point is wasted. The cause of the rollbacks has to be probed.
Execution Rate	Indicates the rate at which this service/port processed the load.	Executions/Sec	Ideally, the value of this measure should be high. A low value is indicative of a processing bottleneck. You may want to compare the value of this measure across services/ports to know which service/port is the slowest in load processing.
Compilation Rate	Indicates the rate at which compilations were performed on the SAP HANA database through this service/port.	Compilations/Sec	Ideally, the value of this measure should be high. A low value is indicative of a compilation bottleneck. You may want to compare the value of this measure across services/ports to know which service/port is the slowest in compilation.
Update Transaction Rate	Indicates the rate at which the records were updated on the SAP HANA database through this service/port.	Updates/Sec	Ideally, the value of this measure should be high. A low value is indicative of problems when updating records in the database. You may want to compare the value of this measure across services/ports to know which service/port is the slowest in updating records.
Commit Rate	Indicates the rate at which transactions were committed to the database by this service/port.	Commits/Sec	Ideally, the value of this measure should be high. A decrease in this measure during the monitoring period may indicate that the applications are not doing frequent commits. This may lead to problems with logging and data concurrency. You may want to compare the value of this measure across services/ports to know which service/port seldom commits. The cause for the same has to be probed.
Rollback Rate	Indicates the rate of unit of work rollbacks since the last measurement period.	Rollbacks/Sec	Ideally, the value of this measure should be low. A high rollback rate is an indicator of bad performance, since work performed up to the rollback point is wasted. You may want to compare the value of this measure across services/ports to know which service/port performs rollback operations on the database frequently. The cause for the same has to be probed.