SQL Optimizer for SAP ASE 3.8 - Installation Guide

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

SQL Collector for Monitor Server Overview

SQL Collector Window

Query Plan