Troubleshooting the Index Tuning Wizard

Microsoft® SQL Server™ 2000 uses indexes to optimize searching. Indexes that worked on SQL Server 6.5 or SQL Server 7.0 may not be the best choice for use with SQL Server 2000. Use the Index Tuning Wizard to find the most efficient indexes for the SQL Server 2000 optimizer.

The wizard requires a workload or a table to build a recommendation of the optimal set of indexes that should be in place. For more information about optimal sets of indexes, see Tuning Indexes.

Here are some general problems that you might encounter.

• Communication error

  The connection to the server is broken or the server is offline. See Connect to SQL Server Dialog Box.

• Empty workload

  The trace file or script contains no SQL batch or RPC events.

• Canceling index analysis

  When processing a large workload, canceling index analysis can take several minutes or more to complete processing.

• Accepting index configuration

  After accepting the recommended index configuration for a large workload or database, final processing can take several minutes or more to complete.

• Nonexisting objects

  Queries referencing temporary or other nonexisting objects cannot be tuned.

• File error

  The Index Tuning Wizard was unable to open a file. Check to see if another user or process locked the workload file, or if the workload file was moved or deleted.

• I/O error

  The Index Tuning Wizard encountered a problem writing to a work file. Increase the available space on the disk drive on which the system temp directory is located.

• Insufficient memory error

  There was insufficient memory to run the Index Tuning Wizard. Run the Index Tuning Wizard on a computer other than the server, or increase the size of the operating-system paging file.

• "Missing index" error

  Multiple users concurrently tuning a database may result in "missing index" errors. It is recommended that only one user tune a database at a time.

• Add Indexed Views option is grayed out

  Indexed views are available only on Microsoft SQL Server 2000 Enterprise Edition. For more information, see Using Indexes on Views and Creating an Indexed View.

Here are some workload problems that you might encounter.

• Cannot choose a SQL Server trace table
  • Does not connect

    If you receive a connection error, the connection to the server is broken or the server is offline.

  • Does not list my tables

    You must first create a SQL Profiler trace of server activity and save this to either a file or a table. The trace must be created in advance. For more information, see Monitoring with SQL Profiler.

• "My workload file" problems

  You must first create a SQL Profiler trace of server activity and save this to either a file or a table.

• Workload cannot be parsed

  The trace file contains SQL batch or RPC events, but none that reference objects in the selected database.