Selected Index Set
The Select Index Set tab of the Batch Run Criteria window is used to select or deselect which Index Sets are applied before retrieving the run time for the original SQL statement. All Index Sets are selected by default. By clicking a row, an Index Set can be selected or deselected. The selected Index Sets are prefixed with a Blue Checkmark 
Note: The higher the Estimated I/O value does not necessarily mean lower performance. If possible, it is recommended that all alternatives be tested.
To select or unselect all the index sets
Right click and select Unselect All or Select All.
The Used Index can be de-selected only if the Used Index checkbox is not selected on the SQL Termination and Batch Termination tab and the Always run Used Index first option in the Selected Index tab is not selected.
To change the order the Index Sets are tested
-
Click the row.
-
Click Move Up
or Move Down
.
To change the order of the Index Sets by sorting
The Index Sets are ranked by Est. I/O Cost by default, with the exception for the Used Index which is placed at the top. You can sort either the Est. I/O column or the Index Set column by clicking the column heading.
To always run the Used Index first
Select the Always run Used Index first checkbox.
Note: This checkbox is dimmed if Used Index is selected as the SQL Termination or Batch Termination criteria.
To select or unselect a group of Index Sets according to the Est. I/O Cost values
-
Select the Apply Filtering checkbox.
-
Click SQL Selection Filter
.
View Selected Filter Window
Note: The Est. I/O Cost is only available if you have already obtained the query plans.
SQL Termination (Options)
The Index Advisor provides several index candidates for the SQL statement. The SQL statement will run faster with some of these index alternatives and it may run longer with other index alternatives. Therefore, you will want to terminate SQL statement when it is running longer. The SQL Termination tab is used to set the termination criteria for the SQL statement with each index set. If the current run time for the SQL statement exceeds the termination time, then it is terminated automatically.
Note: The termination time has a percentage delay added to it.
To define your termination criteria select one of the following options
Used Index
The Used Index option enables you to retrieve the run time of SQL statements that run faster than the original SQL statement. It terminates the SQL statement when it runs longer using an Index alternative than it ran with the current or "used" indexes. If you choose this option, the Used Index is automatically selected on the Selected Index Set tab. During the Batch Run, you cannot terminate the SQL statement when it is running with the Used Indexes because this run time is needed to determine when to terminate the Index alternatives.
Best running time SQL
The Best running time SQL option allows you to retrieve the run time of the SQL statement that is faster than the current best run time. With this option, the run time from the first execution of the SQL statement is used as the termination time. When a faster run is found, the faster time is used as the new termination time. So you are always using the current fastest run time as the termination time for the next execution.
Note: The first index is either the Used Index or the index with the lowest Est. I/O Cost. This depends on whether the Always run Used Index first option is checked on the Selected Index Set tab.
User-defined time
The User-defined time option retrieves the run time of the SQL statement when it is less than the defined time.
Combining Criteria
You can also combine User defined time with Used Index or Best running time SQL by clicking the Or user-defined time checkbox next to each one.
Run without Termination
The Run without termination option allows you to retrieve the run time of the SQL statements without any termination criteria. The SQL statement is run to completion with each Index Set.
SQL Termination (Percentage Delay)
The SQL Termination tab is used to determine the percentage delay calculation and then adds the additional time to the termination time. It is used to account for the time it takes the SQL statement to travel from the PC to the database server over the network. It also enables you to find other SQL statement that are executing close to the termination time.
Percentage delay (%)
The value entered into this field is used as a percentage to calculate the additional time that is added to the termination time. For example, if the termination time is 10 minutes and the percentage delay is 5%, then all SQL statements executed are terminated if the run time exceeds 10.5 minutes. (10+ (10 * 5%))
Minimum delay time (secs) - Range 1 - 99 (Default: 5)
This is the minimum number of seconds that is added to the termination time. It is necessary to factor into the overall termination time the time needed for the SQL statement to be sent to the database server from the PC before it starts to run. This number is only used if the Percentage delay calculation is lower than this value.
Maximum delay time (mins) - Range 1 - 9999 (Default 60)
This is the maximum number of minutes that can be added to the termination time. This number is only used if the Percentage delay calculation is higher than this value.
Batch Termination
The Batch Termination page of the Batch Run Criteria window is used to determine if and when to terminate the Batch Run. It enables you to find alternative SQL statements that give you good performance improvement without having to execute every SQL alternative.
No termination
Specify to run the Batch Run to completion.
Terminate Batch Run if the specified number of index sets fall in the criteria.
Specify to terminate the Batch Run when a specified number of index set are found that meet the following requirements for terminating the Batch Run.
Number of index sets (excluding the Used Index)
Specify how many index sets must be found which show performance improvement over any index that is currently being used.
Count the index set if its elapsed time is faster than
Specify one of the following criteria to select how the performance improvement is determined.
Used Index
Count each index set where the run time for the SQL statement is faster than the time using the indexes currently on the database.
Used Index with a percentage of improvement
Count all index sets where the run time for the SQL statement is the specified percentage faster than the time using the indexes currently on the database.
User-defined time (mins/secs)
Count all index sets where the run time for the SQL statement is faster than a specified number of minutes and/or seconds.