SQL Optimizer for SQL Server 10.1.2 - User Guide

Review Alternatives

Use the Alternatives pane to review the SQL text and execution plan for the alternatives SQL Optimizer generates.

You can also review the run-time statistics for your original SQL statement and the alternatives after you test run the statements.

To review details for an alternative

1. Select the Optimize SQL tab in the main window.

2. Select an alternative in the Alternatives pane.

3. Review details for the alternative in the Alternative Details and Execution Plan panes.

Note: You must test run the SQL alternatives before you can review the run-time statistics. See Test Run SQL Alternatives for more information.

Related Topics

Optimize SQL Statements

Auto Optimize SQL Statements

Alternative SQL Text

Alternative SQL Text

The text editor is used to display the SQL text of the selected alternative. When there’s only original alternative or the selected alternative is user defined, the SQL text can be edited. Otherwise, it’s read-only.

Related Topics

Optimize SQL Used in Cursors

Use Temporary Tables in Scan SQL

View SQL Information (SQL Rewrite)

To view SQL information

1. Select the Optimize SQL tab in the main window.

2. Select a SQL Rewrite session.

3. Select the SQL Information tab in the Optimize SQL window. Click the following to view the information that displays for each pane.

   Classification

   SQL Information	Description
   Classification	Statement type as classified by SQL Optimizer. Types include Simple, Complex, or Problematic.
   Classification Rules Detail	Description
   Details	Applicable SQL Classification Rules. Rules used for the classification process are set on the SQL Classification Options page. See SQL Classification Options for more information.
   Note: SQL Classification information only displays for the original SQL statement.

   Optimization Details

   Optimization Details	Description
   Intelligence Level	Intelligence level used for optimization. This option is set on the Optimizer Intelligence options page. See Intelligence Options (Optimization) for more information.
   Semantically equivalent SQL statements	Number of semantically equivalent SQL statements generated by SQL Optimizer.
   Alternative execution plans produced	Number of alternative execution plans produced for the original SQL statement.
   SQL statements eliminated due to identical execution plans	Number of statement alternatives eliminated with the same execution plan as the original SQL statement. Note: SQL statements with the same execution plan produce the same run time because the database executes these statements in the same way. Therefore, SQL Optimizer eliminates alternatives with equivalent execution plans.
   Optimization Time Details	Description
   Optimization started at	Time optimization started.
   Optimization finished at	Time optimization finished.
   Total optimization time	Total time to complete optimization.
   Average optimization time	Average time to generate each statement alternative.
   Notes:  This information is only available after you generate SQL alternatives. Optimization details only display for the original SQL statement.

   Index Details

   Index Details	Description
   Intelligence level	Intelligence level used for index generation. This option is set in the Index Generation Intelligence options menu. See Intelligence Options (Index Generation) for more information.
   Indexes have been generated	Number of indexes generated.
   Index sets were composed to generate different execution plans	Number of index sets produced to generate alternative execution plans.
   Index sets eliminated due to identical execution plans	Number of index sets eliminated because Database Engine Tuning Advisor information indicated they are not used by the original SQL statement.
   Indexes have been used in index sets	Number of indexes used in the selected index set.
   Index Time Details	Description
   Index started at	Time index generation started.
   Index finished at	Time index generation finished.
   Total index time	Total time to complete index generation.
   Average index time	Average time to generate each index set.
   Note: This information is only available after you generate index alternatives.

   Alerts

   Alert Details	Alert messages about one or more of the following: Errors SQL Optimizer encountered during the optimization process. SQL classification details.
   Note: This pane is only available if SQL Optimizer generates alert messages.

Related Topics  

About Optimizing SQL (SQL Rewrite)

View Schema Information

View Execution Statistics (SQL Rewrite)

You can view execution statistics in the Alternatives pane or in the Execution Statistics tab.

To view execution statistics in the Alternatives pane

• To sort alternatives by an execution statistic, click a column header in the Alternatives grid.
• To change the Optimization Goal, click the Execution Elapsed Time column header and select another goal from the drop-down list.

To view Execution Statistics tab

1. Select the Optimize SQL tab in the main window.
2. Select Rewrite SQL from the Optimize SQL start pages.

3. Select the Execution Statistics tab in the SQL Rewrite window.

4. Review the following for additional information:

   Statistics Name	Description
   Execution Time	Shows CPU time and elapsed time required to execute the SQL statement after SQL Optimizer complies it.
   Compile Time	Shows CPU time and elapsed time required to compile the SQL statement.
   IO Statistics	Shows run time scan count, logical reads, physical reads, and read-ahead reads information recorded during execution of the SQL statement. Also displays information for each table used during execution.
   Note: This information is only available after you execute the SQL statement.

Related Topics 

About SQL Optimization

View SQL Information (Optimizer)

View Schema Information