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
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.
- Does not connect
- "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.