Export (0) Print
Expand All

Troubleshooting the Index Tuning Wizard

SQL Server 2000

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 Resolving 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.

Show:
© 2015 Microsoft