After you generated Index candidates or created Index Sets for your original SQL statement in the Index Advisor window, you need to determine which indexes give you the best performance. The Batch Run function is used to retrieve the run time of the original SQL statement for different index candidates or set.
To setup and execute the Batch Run
The Create Indexes for Batch Run window displays. Select the segment where the index will be created and specify the number for the consumers. Click OK.
In the Batch Run Criteria window, select the Batch Run criteria from the following tabs and click OK.
The Batch Run dialog field displays the run time criteria and the current run time of the SQL statement for each Index Set as it is retrieved.
Note: The indexes are physically created on the database, the query plan is retrieved, and then the indexes are dropped. This process may affect other SQL statements executing on the database during this period.
Select SQL | Stop Current.
To terminate the Batch Run process
Select the segment where you would like the index created from the drop-down list.
(Default 1 Range 1-20)
Specify the number of consumer processes that should perform the sort operation for creating the index. The actual number of consumer processes used to sort the index may be smaller than the specified number, if fewer worker processes are available when Adaptive Server executes the sort.
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.
Note: The Est. I/O Cost is only available if you have already obtained the query plans.