Conducting an SQL Performance Analysis

Note Note

This function is not available for all SAP MaxDB database versions. If it is not available for your software version, the corresponding functions are not available in Database Studio.

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.

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

Prerequisites

You are logged on to the database as database system administrator or as a database user.
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 Performance SQL Performance Analysis .

Activating SQL Monitoring

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

To activate 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	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	SQL statement runtime
	Page Accesses	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.
	Maximum Number of Monitor Entries	Maximum number of executions that the system records.
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.

Displaying Bottlenecks

This feature shows potentially troublesome SQL statements that the system recorded during SQL monitoring.

Enter the number of SQL statements you want to display.
Choose Get Bottleneck Candidates.

The Bottleneck Candidates tab page shows two types of SQL statements: statements whose sum total of all executions meet the requirements a cumulative filter and statements for which a single execution meets the requirements of a single filter.

Fields whose values match the filter conditions are highlighted in yellow.
Double-click an SQL statement to analyze it in detail.

Analyzing Single Executions of an SQL Statement

The Bottleneck Candidate Detail tab page displays the single executions of the selected SQL statement.

Fields whose values match the filter conditions are highlighted in yellow.

Note Note

If no single executions are displayed, this may be due to the following reasons:

You did not activate the Detail Collection.
None of the single executions match the selected filter conditions.
The single executions that matched a filter condition have already been overwritten by newer entries.

End of the note.

Bottleneck Candidate Details
Area	Description
Single Executions	List of single executions of the SQL statement
Statement	Text and parameters of the selected execution of the SQL statement The context menu provides the following functions (selection): Edit Opens the SQL editor. More information: SQL Editor Show table/view definition Shows the table/view definition of the selected (view) table More information: Table Editor, View Editor
Parameters	Values of the parameters of the SQL statement (if any)
Explain	Result of the EXPLAIN statement for the selected execution of the SQL statement Caution The system does not execute the EXPLAIN statement until you select the entry in the list. In some cases, the system may have used a different execution plan during the actual execution of the SQL statement. End of the caution. More information: Analyzing SQL Statements (EXPLAIN)

More Information

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