SQL Optimizer for SQL Server 10.1.2 - User Guide

Optimize SQL

Create Optimize SQL Sessions

Optimize SQL consists of SQL Rewrite sessions and Plan Control sessions. You can use SQL Rewrite sessions to optimize SQL statements by generating SQL statement or index alternatives. Use Plan Control sessions to generate execution plan alternatives for SQL statements without modifying the SQL text.

To create a new session

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

2. Click the arrow beside and select New SQL Rewrite Session or New Plan Control Session.

   Tip: Select Switch Default Session Type to Plan Control to change the default setting for new sessions. The default setting is to create a SQL Rewrite session.

Related Topics 

About SQL Optimizer

Select Connection

Tutorial: SQL Optimizer (SQL Rewrite)

Open Optimize SQL Sessions

To open a session

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

2. Click .

3. Select a SQL Rewrite or Plan Control session to open..

Related Topics 

Create Optimize SQL Sessions

Rewrite SQL

Use SQL Rewrite sessions to optimize SQL statements by using SQL Optimizer's Artificial Intelligence engine to rewrite SQL statement syntax. This process begins with SQL Optimizer analyzing your original SQL statement and generating a list of semantically equivalent statement alternatives. The SQL rewrite process continues until SQL Optimizer cannot generate additional alternatives. SQL Optimizer then applies a set of SQL Server hints to each alternative to generate additional alternatives.

Once SQL Optimizer completes the SQL rewrite process, you can execute the alternatives to retrieve execution plans and run time statistics. You can compare execution times and run time statistics for alternatives to identify the best statements for your database environment.

Notes:

• Although different SQL statement alternatives produce the same results, SQL Server may use different paths to retrieve data for each alternative. The database structure, indexes, and data volumes affect execution statistics. Therefore, you should test the alternatives with your database environment to find the best alternative.
• The Intelligence Level setting you select affects the duration of the optimization process and the number of alternatives SQL Optimizer generates.

Related Topics

Generate Index Alternatives

About Optimizing SQL (Plan Control)