Provisioning and Administration with SAP HANA Cloud

Monitoring SQL Workload in SAP HANA Cockpit

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Use the SAP HANA Cockpit system load monitoring cards

SAP HANA Cockpit - Sessions Card

Lesson Overview

In this lesson, you will 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.

Business Case

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

SAP HANA Cockpit - Sessions Card

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

Clicking on the Sessions card title open 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 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 re
  • Empty - A historic connection that is removed after 1 hour.
Hint
The connection removal time can be configured in indexserver.ini [session] connection_history_lifetime = 60 # minutes

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

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

See the following video for selecting a specific session.

Use the Sessions Card

SAP HANA Cockpit - Threads Card

Business Case

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

SAP HANA Cockpit - Threads Card

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.

Clicking on the Threads card title open 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 options to search for specific threads. You can use the following filter options:

Service - Select threads running on a specific service.

Thread type - 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 (this depends on the system's re
  • Empty - A historic connection that is removed after 1 hour.

See the following video for selecting a specific thread.

Use the Threads Card

SAP HANA Cockpit - SQL Statements Card

Business Case

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

SAP HANA Cockpit - SQL Statements Card

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.

Clicking on the SQL Statements card title open 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 are investigating. Adjust the displayed columns according to your needs.

For a detailed investigation press 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. Click 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, application or user group.

Use the SQL Statement Card

Save progress to your learning plan by logging in or creating an account