SQL Optimizer for SAP ASE 3.8 - User Guide

Why use the SQL Scanner to Identify Problematic SQL Statements

The SQL Collector for Monitor Server offers you a way of identifying problematic SQL statements by analyzing the query plan retrieved using the sp_showplan procedure. However, Adaptive Server does not guarantee that the query plan retrieved from sp_showplan corresponds to the SQL statement. Therefore, it is recommended that you use the SQL Scanner to retrieve the query plan and identify which ones are problematic.

Note: The settings for the SQL classification for Problematic, Complex and Simple SQL statements may differ between SQL Collector for Monitor Server module and SQL Scanner module. The settings for the SQL Collector for Monitor Server module are found SQL Collecting Criteria tab in the Add Collector window. The settings for the SQL Scanner are found SQL Classification tab in the Preferences window.

Related Topics

SQL Scanner Overview

Scan Collectors

SQL Collector for Monitor Server Overview

Scan Collectors

The SQL statements captured by the SQL Collector for Monitor Server can be scanned in the SQL Scanner to classify the SQL statements to help identify which SQL statements are likely to be causing performance problems.

To scan a Collector from the SQL Collector window

1. Mark a Collector by clicking the Collector row. A green checkmark is prefixed to the Collector Name column to indicate that the Collector is marked.

2. Right-click and select Scan.

Related Topics

Why use the SQL Scanner to Identify Problematic SQL Statements

SQL Collector for Monitor Server Overview

SQL Collector Window

Place Bookmarks in SQL Collector

Bookmarks are used to mark a Collector in the SQL Collector window to indicate such things as:

• You are currently working on a Collector.

• You have finished reviewing a Collector.

• You have determined the critical Collectors to be reviewed first.

If the row contains a bookmark, the complete row text will be displayed in fuchsia color. You can have multiple bookmarks.

To place or remove a bookmark

Right-click and select Add/Remove Bookmark on the selected row.

Related Topics

SQL Collector for Monitor Server Overview

SQL Collector Window

View Particular Types of SQL Statement

After the SQL statements are captured, all the SQL statements found in the selected Collector are displayed. However, you can restrict the SQL statements shown by selecting the type of SQL statement by selecting the menu items available under the View menu. The SQL Collector window's title bar displays your chosen option. You can view one or more types of SQL statement by selecting or clearing the menu items.

To display the simple SQL statements

Select View | Simple SQL.

To display the complex SQL statements

Select View | Complex SQL.

To display the problematic SQL statements

Select View | Problematic SQL.

To display the SQL statements without a query plan

Select View | SQL without Plan.

To display all SQL statements

Select View | All SQL.

Related Topics

SQL Collector for Monitor Server Overview

SQL Collector Window