SQL Optimizer for SQL Server 10.1.2 - User Guide

Auto 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. See Select Connection for more information.
6. After you select a connection, the Test Run Settings dialog opens. Specify test run options. See Test Run Settings for more information.
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.

Related Topics

Optimize SQL Statements

Review Alternatives

Test Run Settings

Use the Test Run Settings dialog to specify test run options and optimization goals when you test run multiple SQL simultaneously. This dialog opens when you initiate the test run process in Optimize SQL. The options you specify are used for the current test run only.

In the Test Run Settings dialog, SQL Optimizer provides you with two options:

• Allow SQL Optimizer to determine the best test run settings based on your answers to a few questions.
• Customize the test run settings yourself.

Allow SQL Optimizer to Determine Settings

If you want SQL Optimizer to choose the best test run settings for you, simply answer the questions on the first page of the Test Run Settings dialog. SQL Optimizer will determine your optimization goals based on your answers.

The settings that SQL Optimizer selects are displayed in the Recommended Settings section at the bottom of the Usage and Symptom page.

To allow SQL Optimizer to determine the best test run settings

1. Answer the three questions on the first page (Usage and Symptom) of the Test Run Settings dialog.
2. (Optional) In the Test Run Label field, enter a label to use to identify this test run. The label displays in the Alternatives pane and identifies the test run used to collect the run-time statistics.
3. After answering the questions, review the test run settings selected by SQL Optimizer in the Recommended Settings section (bottom of page).
4. Click Start Test Run.

Customize Test Run Settings

If you want to configure the test run settings manually, click the Customize Test Run Settings link which opens additional pages in the Test Run Settings dialog.

To customize the test run settings

1. Click the Customize Test Run Settings link located at the bottom of the first page (Usage and Symptom) of the Test Run Settings dialog.
2. (Optional) In the Test Run Label field, enter a label to use to identify this test run. The label displays in the Alternatives pane and identifies the test run used to collect the run-time statistics.

3. Specify custom test run options on the Customized Setup page. Review the following for additional information:

   Execution Method

   Select one of the following options.

   Retrieve all rows	Select this option to retrieve all rows.
   Retrieve only this first number of rows from the SQL	Select this option to specify the number of rows to retrieve. Purpose: This is useful for simulating SQL in an online query program, which normally retrieves only a specified number of rows.

   Run Time Retrieval Method

   Select one of the following options.

   Dynamically determine the number of times to test run (running at most twice)	If the run time of the original SQL is less than the value you specify here, the original SQL and all alternatives run twice, and the second run time is used. Otherwise, all SQL run once. Tip: You can specify a default value for the maximum run time in Options | Optimize SQL | Test Run.
   Dynamically determine the number of times to test run (may run multiple times)	Determine how many times to run SQL if the run time of the original SQL is less than the thresholds you specify here. In the first statement, specify max run time and number of runs. If your original SQL run time is less than this threshold, your original SQL and all alternatives run the number of times you specify, and the average run time is used. Tip: You can specify default values for these two editable options in Options | Optimize SQL | Test Run. In the second statement, if the run time of your original SQL is less than the threshold you specify here, your original SQL and all alternatives run twice, and the second run time is used. Otherwise, all SQL run once.
   Run original SQL twice and all other SQL once	Caches data from a table into memory the first time you access it. The next time you access that data, it is already in memory so the following SQL statements run faster. To provide an accurate comparison, the first SQL statement runs twice but only the time from the second run is compared to the times for the other statements.
   Run all SQL twice using the second run time	Executes all SQL statements twice to eliminate factors that can affect the accuracy of the results. If you recently executed a SQL statement, the information for that statement may be cached and the statement may execute faster. Also, if the SQL statement uses different indexes, one index may be cached while another may not be cached. This option eliminates time variation caused by caching since it runs all SQL statements twice but only uses the second run time for comparison.
   Run all SQL once	Executes all SQL statements once. For long running SQL, you do not need to run any statement twice since the effect from caching diminishes over time.
   Flush data cache before running each SQL (running SQL once)	Clears SQL Server data cache before running SQL. Note: Your login must be in the sysadmin fixed server role to clear the data cache.

   Order and Termination

   Specify the following options.

   Execution Order

   Select one of the following: Intelligent order—Executes representative SQL statements with various plan costs according to SQL Optimizer's intelligence engine. Plan Cost—Executes SQL statements in order of plan cost.

   SQL Termination Criteria

   Select one of the following: Run time of fastest SQL—Finds the fastest run time of the SQL in the batch. Cancels SQL statements that run longer than the current fastest run time. With this option, the first SQL statement runs and the time from that statement is used as the termination time for the next SQL statement. When a SQL statement runs faster than this time, the faster time is used as the new termination time. This percentage of the original SQL run time—Cancels SQL statements whose total elapsed time is the specified % of the total elapsed time for the original SQL statement. It terminates all SQL statements that run longer than the calculated termination time. If the original SQL has not been tested, it will be included in the test run. If the original SQL has been tested, but is not included in the test run, select whether to use the original SQL's existing run time or to run it again. User defined time (min:sec)—Cancels SQL statements that run longer than the time you specify here. If the original SQL is included in the test run, specify whether to terminate the original after the specified time, or allow original to run until finished. Run without termination—Runs all SQL statements to completion, regardless of run time. Cancellation delay (sec)—Adds a specified time to the termination time. It is important to factor a delay into the overall termination time to account for the time needed to send the SQL statement to the database server.

   Best Alternative Criteria

   Best SQL Alternative Selection Criteria

   Select one of the following: Execution Elapsed Time—Uses the time it takes to execute the SQL statement, not including compile time, as the criteria to find the best SQL alternative or best index set. Total Elapsed Time—Uses total elapsed run time to find the best SQL alternative or best index set. Response Time—Uses the response time as the criteria to find the best SQL alternative or best index set. Execution CPU Time—Uses the execution CPU time to find the best SQL alternative or best index set. Logical Reads—Uses the number of logical reads to find the best SQL alternative or best index set. Physical Reads—Uses the number of physical reads to find the best SQL alternative or best index set.

4. After specifying custom options, click Start Test Run.

   Note: If you click the Usage and Symptom link, any settings on the Customized Setup page will be lost.

Tip: You can specify default values for some of the custom test run options at Options | Optimize SQL | Test Run. See Test Run Options for more information.

Related Topics

Test Run SQL Alternatives

Test Run Execution Plan Alternatives

Test Run Special Settings

Use the Test Run Special Settings dialog to specify test run options when you test run a single SQL or plan alternative. This dialog opens when you select the Test Run Special - Current command. The options you specify are used for the current test run only.

To specify Test Run options for a single alternative

1. When the Test Run Special Settings dialog opens, specify test run options. Review the following for additional information:

   Execution Method

   Select one of the following options.

   Retrieve all rows	Select this option to retrieve all rows.
   Retrieve only this first number of rows from the SQL	Select this option to specify the number of rows to retrieve. Purpose: This is useful for simulating SQL in an online query program, which normally retrieves only a specified number of rows.

   Run Time Retrieval Method

   Select one of the following options.

   Run the SQL once	Runs the SQL one time.
   Run the SQL multiple times	Select this option to run the SQL alternative multiple times. Then specify the number of runs.
   Flush data cache before running the SQL (running SQL once)	Clears SQL Server data cache before running SQL. Note: Your login must be in the sysadmin fixed server role to clear the data cache.

2. Click Start Test Run.

Related Topics

Test Run SQL Alternatives

SQL Selector Workflow: SQL Rewrite

The SQL Selector workflow allows you to choose original SQL from a batch of SQL statements.

To select original SQL using SQL Selector for Plan Control

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

3. Enter SQL statements in the Alternative Details pane of the SQL Details tab.

   View example

   

4. Click an icon to perform an operation.

5. Select a connection.

6. The SQL Selector window displays your SQL statements.

   View the SQL Selector window

   

   The SQL list displays in the control grid on the left. When you select one of the SQL statements, the corresponding SQL text is highlighted in the right grid.

   The buttons on the user interface change accordingly if the selected SQL statement has a variable(s) or temp table(s). Review the following for additional information:

   If the SQL statement…	the user interface displays…
   Does not contain a variable	» Click the OK button to use the selected SQL as the original SQL statement.
   Contains a variable(s)	To enter bind variable values Click the Enter Bind Variables button. Click OKto use the selected SQL as the original SQL statement. Note: Click to return to the SQL list to select a different SQL as the original SQL statement.
   Contains a temp table(s)	To create temp tables Click the Create Temp Tables button to create the relevant temp table(s). Note: If the SQL script for creating temp table(s) has variable(s), the Enter Bind Variables window displays. Click the OK button to use the selected SQL as the original SQL statement.

   Note: After you select the original SQL statement, the other SQL text is ignored.