Monitoring SQL Workload in SAP HANA Cockpit

In this lesson, you'll learn how to monitor the database sessions using the Sessions Card provided in the SAP BTP version of the SAP HANA cockpit.

You can monitor the database threads using the Threads Card provided in the SAP BTP version of the SAP HANA cockpit.

You can also use the SQL Statements app provided in the SAP BTP version of the SAP HANA cockpit.

As a database administrator, you want to analyze the database sessions connected to identify which applications or which users are currently generating the workload.

Analyzing the sessions connected to your SAP HANA database helps you identify which applications or which users are currently connected to your system, as well as what they're doing in terms of SQL execution.

In the SAP HANA cockpit Database Overview page, search for the Sessions card (1).

The Sessions card (2) shows an overview of the total and running sessions in your SAP HANA Cloud database instance.

Selecting the Sessions card title opens the Sessions app, which, by default, gives a detailed breakdown (3) of all session n your SAP HANA Cloud database instance. You can see the following information:

• Active/inactive sessions and their relation to applications
• Whether a session is blocked and, if so, which session is blocking it
• The number of transactions that are blocked by a blocking session
• Statistics such as average query runtime and the number of DML and DDL statements in a session
• The operator currently being processed by an active session

Use the filter (1) options to search for specific sessions. You can use the following filter options:

Connection Status:

• Running: A statement is executing.
• Idle: No statements are currently executing on this connection.
• Queuing: The connection is currently queued. The status changes to RUNNING when it is dequeued (this depends on the system's resource consumption).
• Empty: A historic connection that is removed after one hour.

The Transaction Status column can show the following different states: INACTIVE, ACTIVE, PRECOMMITTED, ABORTING, PARTIAL_ABORTING, or ACTIVE_PREPARE_COMMIT depending on the state of the transaction.

You can also exclude your own transaction and/or filter on a specific application.

Select the Columns (2) button to add or remove columns to display only the information you require.

See the following video for selecting a specific session.

As a database administrator, you want to analyze the database threads to identify long running threads or blocked threads.

Use the Threads app to monitor the longest-running threads active in your system. You can use it to see, for example, how long a thread is running or if a thread is blocked for a prolonged period.

Analyzing the threads running in the SAP HANA database can be helpful when analyzing the current system load.

You can identify which statements or procedures are being executed and at what stage they are, who else is connected to the system, and if there are any internal processes running as well. The Threads card provides information about the number of currently active and blocked threads in the database.

In the SAP HANA cockpit Database Overview page, search for the Threads card (1).

The Threads card(2) shows an overview of the active and blocked threads in your SAP HANA Cloud database instance.

Selecting the Threads card title opens the Threads page, which, by default, gives a detailed breakdown (3) of all threads in your SAP HANA Cloud database instance. You can see the following information:

The Threads page allows you to monitor the longest-running threads in your current system. You can retrieve more information or customize what is being displayed, for example:

• Filter threads according to Host, Service, Thread Type, Connection Status, Transaction Status, Owner, or Application.
• Choose the sorting order by checking the Group and Sort box and selecting the sorting parameters.
• See the call stack information on your chosen thread.
• Define columns, and choose the parameters you want information on.

Use the filter (1) options to search for specific threads. You can use the following filter options:

Service: Select threads running on a specific service.

Thread type: For more information on thread types, methods, and details, see SAP Note 2114710 – FAQ: SAP HANA Threads and Thread Samples.

Connection Status:

• Running: A statement is executing.
• Idle: No statements are currently executing on this connection.
• Queuing: The connection is currently queued. The status changes to RUNNING when it is dequeued
• Empty: A historic connection that is removed after one hour.

Select the Columns (2) button to add or remove columns to display only the information you require.

See the following video for selecting a specific thread.

As a database administrator, you want to analyze current SQL statements running in the SAP HANA database to identify possible performance problems.

Analyzing the current most critical statements running in the SAP HANA database can help you identify the root cause of poor performance, CPU bottlenecks, or out-of-memory situations.

In the SAP HANA cockpit Database Overview page, search for the SQL Statements card (1).

The SQL Statements card (2) shows an overview of the active SQL Statements in your SAP HANA Cloud database instance.

Selecting the SQL Statements card title opens the SQL Statements page, which, by default, gives an overview and analysis of the most critical statements running in the database.

Using the SQL Statement (1) search field, you can quickly look up a specific SQL statement.

On the SQL Statements page, there are four views (2) available:

• Overview: An overview and analysis of the most critical statements running in the database.
• Active Statements: A list of all statements currently running in the system.
• SQL Plan Cache: Insights into the workload of the SAP HANA database through a list of all statements currently cached in the SAP HANA database
• Expensive Statement Trace: Analysis of individual SQL queries whose execution time is above a configured threshold.

Using the Settings button, you can add and remove columns from the display. Which columns are needed depends on the situation you're investigating. Adjust the displayed columns according to your needs.

For a detailed investigation, select the More (1) button to see the full SQL statement. You can copy the SQL statement for further analysis or documentation purposes.

You can manage workload in SAP HANA by creating workload classes and workload class mappings. Select the _SYS_DEFAULT (2) workload class to create a new workload class to dynamically manage the resource consumption of the selected query. A workload restriction can be mapped to a specific schema, object, application or database user, or application or user group.