itwiz Utility

itwiz Utility

SQL Server 2000

  New Information - SQL Server 2000 SP3.

The itwiz utility allows the Index Tuning Wizard to be executed using a command prompt utility. The Index Tuning Wizard can also be started from SQL Server Enterprise Manager, SQL Query Analyzer, and SQL Profiler.


    [-?] |
        -D database_name {-i workload_file | -t workload_trace_table_name}
        -o script_file_name
        [-S server_name[\instance]]
            {-U login_id [-P password]}
            | –E
        [-f tuning_feature_set]
        [-K keep_existing_indexes]
        [-M recommendation_quality]
        [-B storage_bound]
        [-n number_of_queries]
        [-C max_columns_in_index]
        [-T table_list_file]
        [-m minimum_improvement]



Displays usage information.

-D database_name

Specifies the name of the database to be tuned.

-i workload_file

Specifies the name of the workload file to use as input for tuning. The file must be in one of these formats: .trc (SQL Profiler trace file), .sql (SQL file), or .log (SQL Server 7.0 trace file).

-t workload_table_name

Specifies the name of a table containing the workload trace for tuning. The name is specified as: [server_name].[database_name].[owner_name].table_name. The first three parameters are optional and can be omitted by marking their positions with a period. The table shows the default values for each.

Parameter Default value
server_name server_name specified with –S option. If the –S option is not specified, server_name defaults to the local computer.
database_name database_name specified with –D option.
owner_name dbo.
table_name None.

Note  owner_name must be dbo. If any other value is specified, execution of itwiz will fail and an error will be returned.

-o script_file_name

Specifies the name of the file to which itwiz writes the recommendation script. By default, output files are created in the current directory. The recommendation script contains the expected improvement if the recommendation is accepted.

-S server_name[\instance]

Specifies the computer and instance of SQL Server to connect to. If no server_name or instance is specified, itwiz connects to the default instance of SQL Server on the local computer. This option is required when executing itwiz from a remote computer on the network.

-U login_id

Specifies the login ID used to connect to SQL Server.

-P password

Specifies the password for the login ID. If this option is not used, itwiz prompts for a password. If this option is used without specifying a password, itwiz uses the default password (NULL).

Security Note  Null passwords are not recommended.


Uses a trusted connection instead of requesting a password.

-f tuning_feature_set

Specifies the features to be considered by itwiz for tuning.

Value Description
0 All features (default)
1 Indexes only
2 Indexed views only (applies only to SQL Server 2000, Enterprise and Developer editions)

-K keep_existing_indexes

Specifies whether itwiz is allowed to propose a recommendation that requires dropping one or more existing indexes.

Value Description
0 Do not keep existing indexes
1 Keep all existing indexes (default)

-M recommendation_quality

Specifies the desired point in the running time versus quality of recommendation tradeoff. Higher values of recommendation_quality yield better quality of recommendation. Currently, recommendation_quality can be one of the values shown in this table.

Value Description
0 Fast mode
1 Medium mode (default)
2 Thorough analysis mode

Fast mode currently has these restrictions:

  • No new clustered indexes are recommended.

  • No new indexed views are recommended.

  • All existing indexes are kept (this is equivalent to specifying the -K 1 option).

    Note  The combinations -M 0 -K 0 and -M 0 -f 2 are invalid and cannot be used. Also, when used in conjunction with -M 0, options -f 0 and –f 1 are equivalent.

-B storage_bound

Specifies the maximum space in megabytes that can be consumed by the recommended index set. The default storage bound is three times the current data size or the maximum available space on all attached disk drives, or whichever is smaller. The current data size consists of all tables and clustered indexes.

-n number_of_queries

Specifies the number of queries to be tuned. By default, 200 queries are randomly chosen from the specified workload file. If number_of_queries exceeds the number of queries in the workload file, all queries are tuned.

-C max_columns_in_index

Specifies the maximum number of columns in indexes proposed by itwiz. The default value is 16; this is the maximum value allowed by SQL Server.

-T table_list_file

Specifies the name of a file containing a list of tables to be tuned. Each table listed within the file should begin on a new line. Table names can be qualified by a user name (for example, dbo.authors). Optionally, to invoke the table-scaling feature, the name of a table can be followed by a number indicating the projected number of rows in the table. The table-scaling feature enables studying recommended indexes on smaller scale sample databases. A reasonable size (several %, thousands of rows per table) should be used for the smaller sample database, otherwise the scaled data distribution histograms may be inaccurate and the set of recommended indexes for the sample database may be different from the index recommended for the full scale database.

This is the file format for table_list_file:

[owner.]table [number_of_rows]
[owner.]table [number_of_rows]
  ...    ...       ...

If the -T option is omitted, all user tables in the specified database are considered for tuning.

-m minimum_improvement

If the -m option is specified, itwiz does not recommend any changes in the index configuration, unless the expected improvement in performance for the selected workload is at least minimum_improvement%. If all queries are not considered for tuning (see option -n), the queries not selected are not considered when the improvement is evaluated.


Permits itwiz to overwrite an existing output file. In the event that an output file with the same name already exists and -F is not specified, itwiz returns an error.


Enables verbose output from itwiz. If -v is not specified, itwiz directs only abbreviated information to the screen during execution.

© 2016 Microsoft