Show TOC

Configuring the SQL Performance AnalysisLocate this document in the navigation structure

Use

The SQL performance analysis helps you investigate which SQL statements from the application are particularly cost-intensive for the database, and why this might be the case.

To collect information about the execution of SQL statements in the database, SQL monitoring needs to be enabled.

Note

As of database version 7.9, SQL monitoring is enabled by default.

SQL monitoring measures runtimes, I/O accesses, and the selectivity of SQL statements, and calculates the total costs for SQL statements that are executed repeatedly. This helps you identify those SQL statements that have a short runtime, but who are executed frequently and thus cause a heavy load in the database.

Prerequisites

  • You are logged on to the database as the database system administrator or as a database user.

    Note that to activate or deactivate extended time measurement, you have to be logged on as database system administrator.

    Note that to reset SQL monitoring, you need to be logged on as the database system administrator or as a database user of the DBA class.

  • The database is in the ONLINE operational state.

Procedure

In the context menu of the database system administrator or the logged-on database user, choose Start of the navigation path Performance Next navigation step SQL Performance Analysis End of the navigation path.

Resetting SQL Monitoring

In certain situations, for example after you have made changes to your SQL statements, it can be useful to exclude older executions of SQL statements from the default result set. To do this, choose Reset Monitoring Data.

Note that this does not delete the data that was collected about older executions of these SQL statements, and you can later toggle between both result sets.

Activating or Deactivating Extended Time Measurement

Caution

Activating extended time measurement may have a negative impact on the performance of your database.

For a thorough performance analysis, we recommend that you activate extended time measurement. If extended time measurement is activated, the system records collects additional information about SQL statements.

In the Extended Time Measurement section, choose Activate or Deactivate (depending on whether the function is currently deactivated or activated).

Configuring and Starting the Detail Collection

Caution

Activating the detail collection may have a negative impact on the performance of your database.

If the detail collection is deactivated (default setting), the system only records summarized values for the execution of SQL statements.

If the detail collection is activated, the system additionally records the following:

  • Information about the last executions of those SQL statements that meet at least one of these conditions

  • Parameters used in those SQL statements

Defining Conditions

Define your conditions in the Detail Collection section.

Note that the system only records a certain number of executions and then begins overwriting the oldest entries.

Condition

Description

Simple

Number of page accesses

The system records all SQL statements that access the number of pages specified here, or more pages.

If an SQL statement accesses a high number of pages, this could indicate an unsuitable search strategy or an overly large result set.

Runtime of the statement in ms

The system records all SQL statements with a runtime that is equal or larger than the time specified here.

Selectivity of the statement

The system records all SQL statements with a selectivity that is equal or lower than the percentage specified here.

The selectivity of an SQL statement is the ratio between the number of records that fulfill the conditions of the SQL statement and the number of records that the system has to access when executing the SQL statement.

Advanced

Constraint

For support purposes only

Enter your constraint using the same syntax as for SQL constraints.

Starting the Detail Collection

Choose Apply.

Changing Detail Collection Settings

  1. In the Detail Collection section, change your settings.

  2. Choose whether you want to delete the data that was collected with the old settings:

    • Reset Monitoring: Data that was collected by the SQL monitoring will be deleted

    • Clear Detail Collection Data: Data that was collected by the detail collection will be deleted

  3. Choose Apply.

Stopping the Detail Collection

In the Detail Collection section, choose Stop Detail Collection.

Result

At the bottom of the page, enter the number of SQL statements that you want to display and choose Get Bottleneck Candidates. To display the SQL statements that were recorded by the system, proceed as described under Displaying Bottleneck Candidates.

More Information

SAP Note 819641 Information published on SAP site (FAQ: MaxDB Performance)

Database Administration, Analyzing the Database Performance