SQL Optimizer for SQL Server 10.1.2 - User Guide

Intelligence Options

To set intelligence options

1. Click .

2. Select Optimize SQL | Plan Control | Intelligence.

3. Review the following for additional information:

   Intelligence	Select one of the following: Predefined Settings—Select an intelligence level. Use Extended Total Quota—Enter a value in the Total Quota field to indicate the maximum number of execution plan alternatives generated.
   	Total Quota Notes: A higher quota generates more plans to be evaluated. Total quota is not equal to the number of plans evaluated.

Parameterization Options

This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To set parameterization option

1. Click .

2. Select Optimize SQL | Plan Control | Parameterization.

3. Review the following for additional information:

   Parameterization Option	Select the checkbox to enable auto parameterization.
   Plan Guide Name Prefix	Use to name the generated plan guides.
   Default prefix of plan guide name	Name for the common plan guide.
   Default prefix of template plan guide name	Name for template plan guide.

Test Run

When you test run SQL, SQL Optimizer provides various options to allow you to customize the test run behavior according to your environment and objectives. These options are presented in the Test Run Settings dialog that opens when you initiate the test run process (for multiple SQL).

Note: The customized options are accessed when you click the Customize Test Run Settings link at the bottom of the Test Run Settings dialog.

Use the Test Run page of the Options dialog to specify default values for some of the custom test run options. If default values are specified, the Test Run Settings dialog opens with these values pre-populated.

To specify test run options (default values)

1. Click in the main menu.

2. Select Optimize SQL | Test Run.

3. Specify default values for one or more of the options listed. Review the following for additional information:

   Default number of rows to retrieve	Instead of retrieving all rows, you can instruct SQL Optimizer to retrieve the first N rows. Use this field to specify a default value. Purpose: This is useful for simulating SQL in an online query program, which normally retrieves only a specified number of rows.
   Default threshold to run SQL for a second time	If you select Dynamically determine the number of times to test run (running at most twice) in the Test Run Settings dialog (Run Time Retrieval Method tab), you can instruct SQL Optimizer to Run all SQL twice if original SQL runs faster than N seconds. Specify a default value for N here. If the run time of the original SQL is less than this threshold, the original SQL and all alternatives run twice, and the second run time is used.
   Test run SQL multiple times	If you select Dynamically determine the number of times to test run (running multiple times) in the Test Run Settings dialog (Run Time Retrieval Method tab), you can instruct SQL Optimizer to Run all SQL N times when original SQL is faster than N seconds Default number of times to test run SQL multiple times—Specify a default value for N (number of run times). Default threshold to test run SQL multiple times—Specify a default value for N (number of seconds). If the run time of the original SQL is less than this threshold, the original SQL and all alternatives run multiple times, and the average run time is used.

Related Topics

Test Run Settings

Extract SQL

To specify Extract SQL options

1. Click in the main menu.

2. Select Optimize SQL | Extract SQL.
3. Select an option to specify SQL Optimizer behavior when invalid SQL (or multiple SQL) is entered as the original SQL text and the Get Estimated Plan function is selected.