Select Server and Database

Use the Select Server and Database dialog box to select a Microsoft® SQL Server™ 2000 database for tuning. You can also choose the keep existing indexes, include indexed views, and select a tuning mode.

Options

Server

Specify the name of an instance of SQL Server to which you want to connect.

Database

Specify the database to be tuned.

Keep all existing indexes

Specify to keep all existing indexes in the final tuning recommendation. Indexes may be dropped or replaced if you elect not to keep existing indexes.

Add indexed views

Specify to include indexed views in the analysis. Indexed views are recommended on platforms where their use is supported.

Tuning mode

Specify the tuning mode to use.

Mode | Description | Restrictions
Fast | Select to provide the quickest execution time. This mode may not result in the best overall improvement in performance. | New clustered indexes are not recommended. New indexed views are not recommended. All existing indexes are kept.
Medium | Select to provide a more comprehensive analysis than Fast mode and a quicker execution time than Thorough mode. This is the default selection. | None
Thorough | Select to perform an exhaustive analysis of queries. The execution time of this mode will take longer, but will result in greater overall improvement in performance. | None


Specify Workload

Use the Specify Workload dialog box to specify the name of the workload file to use as input for tuning.

Options

My workload file

Specify the location of the workload file. The file must be in one of these formats: .trc (SQL Profiler trace file), .sql (SQL file), or .log (Microsoft® SQL Server™ 2000 version 6.5 trace file).

SQL Server table

Open the Connect to SQL Server and Source Table dialog boxes to specify the database table containing the workload for tuning.

Query Analyzer selection

Select to analyze a single query or batch in SQL Query Analyzer. Only members of the sysadmin fixed server role can perform index analysis using SQL Query Analyzer.

Advanced Options

Click the Advanced Options dialog box to view or change the index tuning parameters.


Advanced Options

Use the Advanced Options dialog box to view or change the default index tuning parameters.

Options

Current space usage

Displays the data and index size of the database and the space available for growth. This information cannot be modified from the Index Tuning Wizard.

Current data size (MB)
The current size of tables and clustered indexes in the database.
Current index size (MB)
The current size of all non-clustered indexes in the database.
Space available for growth (MB)
Available space on the disk for the database to grow.

Index Tuning Parameters

Modify these parameters to improve the performance of the Index Tuning Wizard.

Limit number of workload queries to sample
Specify the number of workload queries to sample. The default value is 200 queries. Queries are selected at random from the specified workload file. If the value exceeds the number of queries in the workload file, all queries are tuned.
Maximum space for the recommended indexes (MB)
Specify the maximum space in megabytes that can be consumed by the recommended index set. The default space is three times the current data size or the maximum available space on all attached disk drives, or whichever is smaller
Maximum columns per index
Specify the maximum number of columns in indexes proposed by the Index Tuning Wizard. The maximum value allowed is 16; this is the default value.


Select Tables to Tune

Use the Select Tables to Tune dialog box to choose the tables to be tuned.

Options

Displays current tables available for analysis.

Table

Clicking the check box to specify the tablet for the wizard to tune. Alternatively, click the check box to clear a previously selected table.

Actual Rows

Displays the current number of rows in the table.

Projected Rows

Displays the projected growth of the table. By default, this value is the same as Actual Rows. This table-scaling feature enables studying recommended indexes on smaller scale sample databases. A reasonable size (several percent, 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.

Select All Tables

Click to select all the tables that are listed.

Clear All Tables

Click to clear all the selected tables.


Index Recommendations

Use the Index Recommendations dialog box to view the recommendations made by the Index Tuning Wizard. This dialog box

Displays the recommended index set. The list may contain existing indexes to use, existing indexes to drop, or new indexes to be created.

Options

Clustered

Indicates whether the index is clustered.

Index Name

Displays the name of the index. An icon representing the status of the index (existing, new, or to be dropped) is displayed. To view the icon definition, click the icon.

Table/View Name

Displays the table or view associated with the index.

Column Name

Displays the columns used in the index.

Analysis

Click to open the Reports dialog box.


Reports

Use the Reports dialog box to select a report type. The report can be saved to a tab-delimited text file.

Options

Reports

Displays the selected report from the list of available report types.


Schedule Index Update Job

Use the Schedule Index Update Job dialog box to schedule the implementation of the index recommendation.

Options

Apply changes

Specify the implementation option.

Execute recommendations now

Specify immediate implementation.

Schedule a time to execute recommendations

Specify the date and time to implement the index recommendation.

Save script file

Specify the file name and location to store the script file. The script can be executed manually at a later time or on a different server.