Procedure documentationConfiguring and Starting SQL Performance Monitoring Locate this document in the navigation structure


Note Note

This function is not available for all SAP MaxDB database versions.

End of the note.

Several reasons are possible for poor performance of your database (and therefore your application as well). Your hardware might not be powerful enough, for example, or other applications may be taking up too many system resources.

SQL performance analysis in Database Studio lets you investigate which SQL statements that the application triggers 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, you need to activate SQL monitoring. SQL monitoring measures runtimes, I/O accesses, and the selectivity of SQL statements, and calculates the total costs for repeatedly executed SQL statements. This lets you identify SQL statements that have short runtimes individually, but whose frequent execution results in a heavy load.

Recommendation Recommendation

Only activate SQL monitoring if you need a targeted analysis of SQL performance problems. Once you have solved the issue at hand, deactivate it again.

End of the recommendation.

If you activate Detail Collection as well, then the system records the last executions of SQL statements that meet the criteria you define, as well as the parameters used. If you do not activate the Detail Collection, the system only records collective values for the execution of SQL statements.


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

  • The database is in the ONLINE operational state.


Configuring and Starting SQL Performance Monitoring
  1. In the context menu of the database system administrator or the logged-on database user, choose   Performance   SQL Performance Analysis  .

  2. To start SQL monitoring (including Detail Collection) directly with the default values, choose Start Detail Collection.

    To start SQL monitoring with different settings, proceed as follows:

    1. Choose Advanced.... .

      Monitoring Settings





      Collects summary information about all executions of an SQL statement and accumulates this information for each SQL statement.

      Initialize Monitor Tables

      Deletes the collected information.

      Detail Collection

      Collects information about single executions of a specific SQL statement. If the SQL statement has parameters, it records the values of those parameters as well.

      The system records all executions that fulfill at least one of the conditions (OR operator).

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

      Caution Caution

      When you change one of the Detail Collection conditions, the system adapts the corresponding filter condition automatically (see below).

      End of the caution.

      Selectivity lower or equal than

      Ratio between the number of records that fulfill the conditions of an SQL statement and the number of records that the system had to read when executing that SQL statement.

      Runtime (ms) higher or equal than

      SQL statement runtime

      Page Accesses higher or equal than

      Number of pages that the system has to access when executing the SQL statement.

      If this number is high, this could indicate an unsuitable search strategy or an overly large result set.

      Initialize Monitor Tables

      Deletes the information collected in the Detail Collection.

    2. Choose Start to start simple SQL monitoring and, if necessary, activate the Detail Collection.

Configuring Filters

Filters let you restrict the list of SQL statements that the system records in SQL monitoring. All the SQL statements that fulfill at least one of the filter conditions are displayed (OR operator).

The following types of filters are available:

  • Cumulative Filters

    These filters use the information that the system collects during simple SQL monitoring.

    The system accumulates the properties of the single executions for each SQL statement.

    These filters help you find SQL statements that the system runs frequently. Minor improvements in the performance of such SQL statements can result in large performance gains in the overall system.

  • Single Filters

    These filters use information that the system collected in the Detail Collection.

    They help you find SQL statements for which single executions are particularly slow (for specific parameter values of an SQL statement, for example).

    Caution Caution

    When you change the filter condition of a single filter, the system modifies the corresponding Detail Collection condition in the SQL monitoring settings automatically.

    End of the caution.
Adding a Filter
  1. Choose a filter in the Analysis field and enter a value for the filter condition under Filter Detail.

    Performance Analysis: Filters

    Filter Name

    Filter Condition

    Cumulated Read Pages

    Total pages accessed by all executions of individual SQL statements

    Cumulated Runtime

    Total runtime (in s) of all executions of individual SQL statements

    Cumulated Selectivity

    Total selectivity (in s) of all executions of individual SQL statements

    Read Pages

    Pages accessed by single executions of individual SQL statements


    Runtime (in ms) of single executions of individual SQL statements


    Selectivity (in %) of single executions of individual SQL statements

  2. Choose Add to add the filter.

Changing the Filter
  • To change a filter condition, choose the filter, enter the new filter condition, and choose Update.

  • To remove a filter, set the checkbox for the filter and choose Remove Checked Filters.


When the SQL performance analysis identifies specific SQL statements as bottleneck candidates, you can use the SQL editor, table editor, and Explain view to examine them in more detail and identify the specific reasons for poor performance (such as missing indexes, bad indexes, or obsolete SQL optimizer statistics).

For more information, see Analyzing SQL Performance Bottlenecks.

More Information