Chat now with support
Chat with Support

SQL Optimizer for SAP ASE 3.9.1 - User Guide

Introduction Tutorials Preferences Editor Functions SQL Information and Functions Performance Monitor SQL Inspector SQL Collector for Monitor Server SQL Scanner Index Advisor SQL Optimizer
SQL Optimizer Overview Optimization Engine Common Coding Errors in SQL Statements What Function Should l Use to Retrieve the Run Time? Unsatisfactory Performance Results SQL Optimizer Functions SQL Editor Optimized SQL Activity Log
SQL Worksheet SQL Formatter Database Explorer Code Finder Object Extractor SQL Repository Index Impact Analyzer Index Usage Analyzer Configuration Analyzer Migration Analyzer Abstract Plan Manager User-Defined Temp Tables SQL History Legal Information

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.

 

Related Topic

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.

 

Related Topics

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

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

  1. Mark an existing Collector

  2. Right-click and select Ad-hoc Monitor to start monitoring.

To stop ad-hoc monitoring

Click image\B_AbortMonitor.gif.

Warning: If you have already monitored the Collector, ad-hoc monitoring erases any existing information.

 

Related Topics

Related Documents