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.
The connection to the server is broken or the server is offline. See Connect to SQL Server Dialog Box.
The trace file or script contains no SQL batch or RPC events.
When processing a large workload, canceling index analysis can take several minutes or more to complete processing.
After accepting the recommended index configuration for a large workload or database, final processing can take several minutes or more to complete.
Queries referencing temporary or other nonexisting objects cannot be tuned.
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.
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.
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.
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.
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.
If you receive a connection error, the connection to the server is broken or the server is offline.
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.
You must first create a SQL Profiler trace of server activity and save this to either a file or a table.
The trace file contains SQL batch or RPC events, but none that reference objects in the selected database.