About Optimizing SQL (SQL Rewrite)
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.
Select Connection
To select a connection
- Select the Optimize SQL tab in the main window.
-
Click
-
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. See Connect to SQL Server for more information.
Select Database and User Description Database
Click Set User
Click
Optimize SQL Statements
In a SQL Rewrite session, SQL Optimizer generates semantically equivalent alternatives with unique execution plans for your original SQL statement.
After SQL Optimizer generates SQL alternatives, you can test run the alternatives to benchmark their performance. SQL Optimizer provides execution times and run-time statistics for each alternative to help you identify the best SQL statement for your database environment.
To optimize a SQL statement
- Select the Optimize SQL tab in the main window.
- Select SQL Rewrite from the Optimize SQL start page.
-
Enter a SQL statement in the Alternative Details pane.
-
Click
. The Select Connection window displays.
-
Select a connection to use. Review the following for additional information:
Select Connection Description Connection
Click
Tip: Click
Select Database and User Description Database
Click Set User
Click -
Click
to optimize the SQL statement or click
to optimize the SQL statement and generate index alternatives in one step.
- After SQL Optimizer finishes generating alternatives, you can test run the alternatives. See Test Run SQL Alternatives for more information.
- If you selected multiple alternatives to test run, the Test Run Settings dialog opens. Select test run criteria to apply to this test run. See Test Run Settings.
- 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.
- Select the Compare tab to compare SQL alternatives to the original SQL.
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.
Test Run 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
- After generating SQL alternatives in a SQL Rewrite session
- 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
- 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
- To test run a single SQL alternative, select the alternative and click
- 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.
- 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.
- 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.
- 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.