Checking the SQL Statements

Objectives

After completing this lesson, you will be able to:

  • Find SQL statements that consume the most resources
  • Check the threads used by SQL statements
  • Check which session is used by an SQL statement

SQL Statements Card

The SQL Statements card opens the SQL Statements applications, to monitor and analyze different types of statements in your database, including active, SQL Plan cache, and expensive statements. It lets you enable statement memory tracking.

For more information on how to use SAP HANA workload classes, see the course HA215: Using Monitoring and Performance Tools.

Using the Threads Card

Use the Threads card to monitor the number of currently active and blocked threads. You can use it to see, for example, for how long a thread has been 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 you with detailed information about the 1000 longest-running threads currently active in the database.

Note
Not all of the columns are visible by default. You can add and remove columns in the Columns dialog box, which you open by choosing the Settings icon in the table toolbar.

You can retrieve more information or customize what is being displayed, for example:

  • Filter threads by host, service, and thread type
  • Choose the sorting order by selecting the Group and Sort button and selecting the sorting parameters
  • See the call stack information on your chosen thread
  • Define columns and choose the parameters on which you want information

When you have selected a thread, you can navigate to the Sessions or Blocked Transactions page for the thread with the same connection ID.

If a thread is in a blocked transaction or is using an excessive amount of memory, you can cancel the operation executing the thread by choosing Cancel Operations in the footer toolbar.

Examine Session

Using the Session Card

Sessions Card

Use the Sessions card to monitor all sessions in your landscape. 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.

Hint
The Sessions card displays the number of active and total sessions.

Choosing the Sessions card opens the Sessions application. The Sessions page allows you to monitor all sessions in the current landscape. 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

The Save as button (1) allows you to save the data sets as a text, CSV, or HTML file.

The Navigate to … button (2) allows you to navigate from the Session application to the Threads application, or vice versa. From the selected session, you can jump directly to the threads or blocked transactions belonging to that selected session.

The Cancel Operation and Cancel Sessions buttons (3) allow you to terminate the selected running session or operation.

Log in to track your progress & complete quizzes