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 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:
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
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
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:
|
SQL Termination Criteria |
Select one of the following:
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:
|
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.
Test Run Execution Plan Alternatives
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
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. |
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
Enter SQL statements in the Alternative Details pane of the SQL Details tab.
Select a connection.
The SQL Selector window displays your SQL statements.
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 |
| |||
Contains a variable(s) |
To enter bind variable values
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
|
Note: After you select the original SQL statement, the other SQL text is ignored.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. 利用規約 プライバシー Cookie Preference Center