The Index Tuning Wizard provides a rich set of options to customize index selection:
This option can be exercised on the server and database choice screen. By selecting this option, the user instructs the wizard not to drop any of the existing indexes. Thus, this option allows conservative use of the tool and incremental changes in the design of the indexes. Unless the user is experienced, it is recommended that this mode of operation be used.
For large workload files and large databases, index tuning may require a significant amount of time and resources. However, to lessen the elapsed time and the workload on the server, SQL Server allows the user to request the Index Tuning Wizard to be less extensive in its search for an appropriate set of indexes. Users can choose this mode by deselecting the exhaustive enumeration operation. This option is also presented on the server and database choice screen. Although this mode of operation searches fewer possibilities, in many cases it is able to provide a respectable set of recommendations. The Index Tuning Wizard deselects exhaustive enumeration by default.
Additional customization options are presented in the Advanced Options screen:
If this number is set to k, the Index Tuning Wizard ignores the workload that follows the first k queries (for example, Transact-SQL statements). By default, this number is set to 32,767. By reducing the value of this parameter, the size of the effective workload file can be controlled and the execution of the Index Tuning Wizard may be accelerated. However, it should be remembered that the Index Tuning Wizard considers the first k queries, not events. Specifically, events that are not considered by the Index Tuning Wizard to be queries are not counted towards this limit.
This parameter sets the limit on the sum total of all storage for all indexes. By default, this parameter is set to twice the size of the current data set. This limit includes the storage devoted to indexes that must be included due to integrity constraints (for example, uniqueness constraint). In case the Keep all indexes option is selected, this limit also includes the storage required for existing indexes. Because databases grow over time, the administrator should adjust the parameter so that the assigned storage is appropriate for the current data size.
This parameter can be tuned to set the maximum width of indexes. An index with few columns potentially can be used in many queries in a workload. An index with many columns may enable index-only access and eliminate data scans for some of the queries even though it requires more storage space than an index with fewer columns. Given the complexity of the trade-off, it is recommended that only experienced administrators tune this parameter.
Another significant way in which the Index Tuning Wizard can be customized is by restricting index tuning to only a subset of all tables by selecting the Select Tables to Tune option. This allows the user to focus the design on selected tables in the database without altering the indexes for the remaining tables.