SQL statements captured in the SQL Collector for Monitor Server are classified as Problematic, Complex, Simple, or SQL without Plan. The classification settings are found on the SQL Collecting Criteria of the Add Collector wizard.

Problematic SQL

Problematic SQL statements are potentially problem SQL statements that should be optimized. Problematic SQL must satisfy one of the following criteria:

The number of tables referenced in the query plan exceeds the upper limit of the Complex SQL Table Range.
SQL statements with a full table scan if With Full Table Scan option is selected.
SQL statements with the number of worktables greater than or equal to the value defined in the With number of worktables greater than or equal to option.
SQL statements whose number of reformattings is greater than or equal to the value defined in the With number of Reformattings greater than or equal to option.

Complex SQL

Complex SQL statements are complicated SQL statements where there is room for improvement. Complex SQL must satisfy one of the following criteria:

The number of tables referenced in the query plan falls into the Complex SQL Table Range.
SQL statements with full index scan if With Full Index Scan selected.

Simple SQL

Simple SQL statements are direct and straight forward SQL statements with minimal probability of improvement. Simple SQL are SQL statements with the number of tables referenced in the query plan less than the lower limit of the Complex SQL Table Range.

SQL without Plan

SQL statements for which the query plan cannot be retrieved using sp_showplan stored procedure.

For SQL statements without Plan you can use the SQL Scanner module to scan the Collector and obtain a query plan for all the captured SQL statements.

Note: The parameter settings for Problematic, Complex, and Simple SQL statements may be different between SQL Collector for Monitor Server and SQL Scanner modules. The settings for the SQL Classification for the SQL Collector for Monitor Server are set in the Add/Modify Collector window and settings for the SQL Classification for the SQL Scanner are set in the Preferences window.

SQL Collector Window

Query Plan

Monitor

SQL Collector for Monitor Server > Monitor

For the Monitor function to be enabled, you must first mark the Collector you want to monitor in the SQL Collector window. To mark a Collector, simply click the row you want to monitor.

To start monitoring

Click $image\B_Monitor.gif$ .

Warning: If you have already monitored the Collector, re-monitoring erases your existing information.

If the start time of the Collector has not been reached, SQL Collector for Monitor Server will wait until it is time to begin. During and after monitoring, information acquired is updated in the SQL Collector window.

The SQL Collector window must remain open during the monitoring process. If you have started the monitoring process, but the scheduled time has not arrived, this window must remain open for the monitoring process to start and run to completion.

Monitor is terminated automatically once the end time is reached.

At the end of the monitoring process, duplicated SQL statements are eliminated. This may take a few minutes to complete.

Note: The ad-hoc monitoring from the right-click menu does not use the start, until, and duration time. The ad-hoc monitoring starts immediately and is only terminated by clicking Abort Monitor.

Related Topics

Ad-hoc Monitoring

Abort Monitor

SQL Collector for Monitor Server Overview

SQL Collector Window