SQL Optimizer for SQL Server 10.1 - User Guide

Tutorial: SQL Optimizer

Using SQL Rewrite mode in Optimize SQL consists of two steps. In the first step, SQL Optimizer generates semantically equivalent alternatives with unique execution plans for your original SQL statement. A SQL Server cost estimate displays for each alternative generated. In the second step, SQL Optimizer executes the alternatives to benchmark each statement's performance. This provides execution times and run time statistics that allow you to find the best SQL statement for your database environment.

Tip: SQL Server cost only provides an estimate of resource usage to execute a SQL statement. Since statements with higher cost may perform better, you should test alternatives generated to determine the best statements for your database environment.

Step 1: Optimize the SQL Statement

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

2. Select SQL Rewrite from the Optimize SQL start page.

   Note: If the start page does not display, click the arrow beside and select New SQL Rewrite Session.

3. Enter a SQL statement in the Alternative Details pane.

4. Click . The Select Connection window displays.

5. Select a connection. Review the following for additional information:

   Select Connection	Description
   Connection	Click to select a previously created connection. Tip: Click to open the Connection Manager to create a new connection.
   Select Database and User	Description
   Database	Click to select the database to match your SQL statement.
   Set User	Click to select your user name.

6. Optimize SQL to use in a cursor. SQL Server generates different execution plans for SQL statements embedded in a cursor declaration. This needs to be considered when using SQL Optimizer to retrieve execution plans, retrieve run times, and generate SQL alternatives. Specific cursor settings need to be selected before SQL statements that come from or will be embedded in a cursor declaration. These settings tell SQL Optimizer to use cursor simulation when retrieving execution plans and run time information.

   Complete the following steps to select cursor settings:

   1. Select the Optimize for Cursor checkbox.
   2. Click Settings.
   3. Select the cursor type and the associated cursor argument.

7. Click to optimize the SQL statement or click to optimize the SQL statement and generate index alternatives in one step.

   You can configure hints and other optimization settings, such as temporary table generation and ANSI JOIN syntax, in the Options pages before performing this step. The Intelligence Level that determines the number of SQL Server hints applied and the number of alternatives generated can also be configured from the Options pages.

   Note: You can create a temporary table for your SQL statement. See "Use Temporary Tables in Scan SQL" in the online help for more information.

8. Click after SQL Optimizer completes the SQL rewrite process to compare your original SQL statement with the alternatives generated.

Step 2: Benchmark Alternative SQL Statements

The Test Run function provides an efficient way to benchmark alternatives generated by SQL Optimizer. You can execute selected alternatives to obtain actual execution statistics. This function does not affect network traffic since SQL Optimizer can provide these statistics without having to retrieve result sets from the database server. Additionally, data consistency is maintained when using SELECT, SELECT INTO, INSERT, DELETE, and UPDATE statements because these statements are run in a transaction that is rolled back after execution.

To benchmark a SQL statement alternative

1. Click the drop-down arrow beside and select Test Run All.

2. The Test Run Settings dialog opens. Select test run options to apply to this test run.
   • To allow SQL Optimizer to determine the best test run settings, answer the questions in the Test Run Settings dialog and click Start Test Run.
   • To customize test run settings, click Customize Test Run Settings at the bottom of the page and specify options for this test run.
3. The execution statistics display in the Alternatives pane once the test run is finished. Select an alternative to see more information in the Alternative Details and Execution Plan panes.

Automatically Optimize SQL Statements

Use the Auto Optimize function to perform the optimization and testing processes simultaneously. The function optimizes your original SQL statement by generating alternatives and then starts testing once SQL Optimizer generates the first alternative.

The Auto Optimize function reduces the time required to find the best alternative because it begins the testing process early instead of waiting until SQL Optimizer generates all alternatives. You can stop the Auto Optimize function once you find a satisfactory SQL statement alternative.

To automatically optimize a SQL statement

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

2. Select SQL Rewrite from the Optimize SQL start page.

3. Enter a SQL statement in the Alternative Details pane.
4. Click .
5. If you did not already select a connection for this session, the Select Connection dialog opens. Select a connection.
6. After you select a connection, the Test Run Settings dialog opens. Specify test run options.
7. When the optimization and testing process is finished, the execution statistics display in the Alternatives pane. Select an alternative to view details in the Alternative Details pane and the Execution Plan pane.
8. Select the Compare tab to compare SQL alternatives to the original SQL.

Tips:

• Click to stop the optimization and testing processes.
• You can send SQL statements from SQL Optimizer back to Toad for SQL Server after the optimization process is finished. Click to send a SQL statement back to Toad from SQL Optimizer.

Automatically Optimize Using Plan Guide

Use Auto Optimize to generate plan alternatives and then test run the alternatives all in one process.

To automatically optimize using plan guide

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

2. Select Plan Control from the Optimize SQL start page.
3. Enter a SQL statement in the Original SQL pane.

4. Click .

   Tip: Click to abort the process.

5. If you did not already select a connection for this session, the Select Connection dialog opens. Select a connection.
6. After you select a connection, the Test Run Settings dialog opens. Specify test run options.
7. When the optimization and testing process is finished, the execution statistics display in the Plans pane. Select a plan alternative to view details in the Execution Plan pane.
8. Select the Compare tab to compare plan alternatives to the original plan.
9. Select the Deploy tab to deploy a plan to the Manage Plan Guides module.

Execute SQL Alternatives

After SQL Optimizer generates SQL alternatives, you can test run the alternatives. Test run statement alternatives to view their execution statistics. You can test run a single statement, a selected group of statements, or all statements simultaneously.

Note: SQL Optimizer test runs the statements against the database during this process to obtain accurate run-time statistics. SQL Optimizer automatically rolls back any changes made to the database after it executes SELECT, SELECT INTO, INSERT, DELETE, or UPDATE statements.

To test run SQL alternatives

1. After generating SQL alternatives in a SQL Rewrite session, use one of the following methods to test run the SQL alternatives.
   • To test run a single SQL alternative, select the alternative and click .
   • To test run a single SQL alternative and specify test run criteria, select the alternative and click the arrow beside and select Test Run Special - Current.
   • To test run multiple alternatives simultaneously, select the alternatives and click the arrow beside and select Test Run Selected.
   • To test run all alternatives simultaneously, click the arrow beside and select Test Run All.
2. If you selected to test run a single alternative using special test run criteria, the Test Run Special Settings dialog opens. Specify criteria for this test run only.
3. If you selected to test run multiple or all alternatives simultaneously, the Test Run Settings dialog opens. Specify test run options to apply to this test run only.
4. After the test run process is finished, the execution statistics display in the Alternatives pane. Select an alternative to view details in the Alternative Details pane and the Execution Plan pane.
5. Select the Compare tab to compare SQL alternatives to the original SQL.

Notes:

• Click to stop the test run process for multiple alternatives.
• To stop the process for a single alternative, select the running plan alternative and click .

To clear the SQL Rewrite window

To clear the results, right-click inside the Alternative Details pane and do one of the following:

• Select Clear Optimization Results | Keep Original Scenario to clear only the results and retain both the original SQL statement and the database connection.
• Select Clear Optimization Results | Clear Original Scenario to clear the SQL Rewrite window, but retain the database connection.