SQL Optimizer for SQL Server 10.1.2 - User Guide

Test Run Execution Plan Alternatives

After SQL Optimizer generates execution plan alternatives, you can you can test run the alternatives. Test run plan alternatives to retrieve run-time statistics and identify the best plan alternative for your database environment. You can test run a single alternative, a selected group of alternatives, or all alternatives simultaneously.

To test run execution plan alternatives

1. After generating execution plan alternatives in a Plan Control session (see Generate Execution Plan Alternatives), use one of the following methods to test run the plan alternatives.
   • To test run a single plan alternative, select the alternative and click .
   • To test run a single plan alternative and specify test run criteria, select the alternative and click the arrow beside and select Test Run Special - Current.
   • To test run multiple plan alternatives simultaneously, select the alternatives and click the arrow beside and select Test Run Selected.
   • To test run all plan 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. See Test Run Special Settings for more information.
3. If you selected to test run multiple or all alternatives simultaneously, the Test Run Settings dialog opens. Select test run options to apply to this test run. See Test Run Settings for more information.
4. After the test run process finishes, you can review and compare plan alternatives, and then select a plan alternative to save as a plan guide. See Deploy Plan Guides for more information.

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 Plan Control window

To clear the results, right-click inside the Original SQL 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 Plan Control window, but retain the database connection.

Related Topics

Generate Execution Plan Alternatives

Deploy Plan Guides

Auto 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. 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 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. See Deploy Plan Guides for more information.

Related Topics

Review Plans

Deploy Plan Guides

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