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.
Select the Optimize SQL tab in the main window.
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.
Click . The Select Connection window displays.
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. |
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:
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.
Click after SQL Optimizer completes the SQL rewrite process to compare your original SQL statement with the alternatives generated.
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
Click the drop-down arrow beside and select Test Run All.
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
Select the Optimize SQL tab in the main window.
Select SQL Rewrite from the Optimize SQL start page.
Tips:
Use Auto Optimize to generate plan alternatives and then test run the alternatives all in one process.
To automatically optimize using plan guide
Select the Optimize SQL tab in the main window.
Click .
Tip: Click to abort the process.
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
Notes:
To clear the SQL Rewrite window
To clear the results, right-click inside the Alternative Details pane and do one of the following:
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center