Indexes are a critical factor in how well any database performs. They impact queries and reports, finds and selections, and many other essential operations. Although any table involved in a relationship should have an indexed primary key, it's not always so obvious what should be indexed and how the index should be composed. Creating indexes is not really a science; it's almost an art form. Choose your indexes well and you improve performance. Choose your indexes well and the indexed views from which they are derived (also called materialized views) are more often used by the SQL Server Optimizer. If you don't choose your indexes well, you can seriously bloat your database and choke off decent performance for your users.
To help you identify indexes and indexed views to create, and to generate sample scripts that can be used to create them under a particular workload, SQL Server 7 and SQL Server 2000 contain an Index Tuning Wizard. The Index Tuning Wizard runs a workload (as a file or table or Profiler trace) against the server and database names you provide. The wizard returns a physical design that it calculates will optimize the performance of your queries and updates. What you'll see in the results are options to keep all your existing indexes, add indexed views, and a tuning mode. The tuning mode can be very tim-e and resource-intensive, and so the wizard allows you to set quality parameters to trade off quality for time.
Many new features were added to the Tuning Wizard for SQL Server 2000. They include more limitations on what gets looked at, how large the indexes can be, table sizes for table scaling, and on the way the output of the wizard is reported and acted upon. To access the Index Tuning Wizard, open the Enterprise Manager and select Wizards from the Tools menu, then select Management. You can also open the wizard from the SQL Profiler using the Tools tab of the Profiler menu. You can also start the wizard from within the SQL Server Query Analyzer, with a workload consisting of Transact-SQL statements. In the Query Analyzer, select Perform index analysis on the Query tab to initiate the wizard. Finally, there is also the command line utility itwiz that will allow some aspects of this wizard to run.
A rather lengthy article about the Index Tuning Wizard may be found on the Microsoft Tech Net Web site.
Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.
This was first published in October 2002