Configuring and Starting SQL Performance Monitoring

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

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.

Prerequisites

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.

Procedure

Configuring and Starting SQL Performance Monitoring

In the context menu of the database system administrator or the logged-on database user, choose Performance SQL Performance Analysis .

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:

Choose Advanced.... .

Monitoring Settings
Function	Property	Description
Monitoring		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 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.

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

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	Runtime (in ms) of single executions of individual SQL statements
Selectivity	Selectivity (in %) of single executions of individual SQL statements

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.

Result

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

SAP Note 819641 (FAQ: MaxDB Performance)
Database Administration, Analyzing the Database Performance
Monitoring Databases: Overview