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.
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
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.
Right-click and select Scan.
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.
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.