itwiz Utility
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.
Syntax
itwiz
[-?] |
[
-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]
[-F][-v]
]
Arguments
-?
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).
-E
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.
-F
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.
-v
Enables verbose output from itwiz. If -v is not specified, itwiz directs only abbreviated information to the screen during execution.
Remarks
For more information about this utility, see Getting Started with Command Prompt Utilities.
Examples
A. Tune remote instances
This example connects to the tpcd1G database on remote server autoadmin5. The connection uses a login ID of sa, and a password of NULL.
itwiz –S autoadmin5 -U sa –P -D tpcd1G -i tpcd_22.sql –o script.sql
B. Limit disk use
This example limits the size of newly created indexes to 3 gigabytes and directs the output to d:\result_dir\script1.sql.
itwiz –D tpcd1G –i tpcd_22.sql -B 3000 –o "d:\result_dir\script1.sql"
C. Limit the number of tuned queries
This example limits the number of queries read from file orders_wkld.sql to a maximum of 10.
itwiz –D orders –i orders_wkld.sql –o script.sql -n 10
D. Tune specific tables
This example demonstrates the use of a table_list_file (-T option). The contents of table_list.txt specifies that:
- Only the authors, titles, and publishers tables in the database should be tuned.
- The number of rows in the authors and titles tables should be assumed to be 100,000 and 2,000,000 respectively.
- The number of rows in publishers should be assumed to be the current number of rows in the table.
itwiz –D pubs –i pubs_wkld.sql –o script.sql –T table_list.txt
Here is the content of file table_list.txt:
dbo.authors 100000
dbo.publishers
titles 2000000
E. Use thorough analysis mode
This example shows using the thorough analysis mode (-M option).
itwiz –D tpcd1G –i tpcd_22query.sql –M 2 –E –o tpcd_22recomm.sql