Query Plan
SQL statements captured in the SQL Collector for Monitor Server modules are displayed with corresponding query plan using the sp_showplan stored procedure. If the query plan is not available, then the Query Plan pane window remains blank and the SQL is classified as SQL without Plan
Note: It is not guarantee that using the sp_showplan stored procedure to retrieve the query plan will obtain a query plan that corresponds to the SQL statement.
Monitored SQL Statement Types
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.
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 
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.
Ad-hoc Monitoring
Ad-hoc monitoring allows you to analyze the SQL statements currently running without taking consideration of start, until, and duration time.
To start ad-hoc monitoring
-
Mark an existing Collector
-
Right-click and select Ad-hoc Monitor to start monitoring.
To stop ad-hoc monitoring
Click 
Warning: If you have already monitored the Collector, ad-hoc monitoring erases any existing information.