Index Tuning Recommendations

Indexes can be dropped, added, and changed without affecting the database schema or application design. Efficient index design is paramount to achieving good performance. For these reasons, you should not hesitate to experiment with different indexes. The Index Tuning Wizard can be used to analyze your queries and suggest the indexes that should be created. For more information, see Index Tuning Wizard.

The Microsoft® SQL Server™ query optimizer reliably chooses the most effective index in the majority of cases. The overall index design strategy should provide a good selection of indexes to the query optimizer and trust it to make the right decision. This reduces analysis time and results in good performance over a wide variety of situations.

Do not always equate index usage with good performance, and vice-versa. If using an index always produced the best performance, the query optimizer’s job would be simple. In reality, incorrect choice of indexed retrieval can result in less than optimal performance. Therefore, the query optimizer’s task is to select indexed retrieval only when it will improve performance and to avoid indexed retrieval when it will affect performance.

Recommendations for creating indexes include:

• Write queries that update as many rows as possible in a single statement, rather than using multiple queries to update the same rows. By using only one statement, optimized index maintenance can be exploited.
• Use the Index Tuning Wizard to analyze your queries and make index recommendations. For more information, see Index Tuning Wizard.
• Use integer keys for clustered indexes. Additionally, clustered indexes benefit from being created on unique, nonnull, or IDENTITY columns. For more information, see Using Clustered Indexes.
• Create nonclustered indexes on all columns frequently used in queries. This can maximize the use of covered queries. For more information, see Using Nonclustered Indexes.
• The time taken to physically create an index is largely dependent on the disk subsystem. Important factors to consider are:
  • RAID (redundant array of independent disks) level used to store the database and transaction log files.
  • Number of disks in the disk array (if RAID was used).
  • Size of each data row and the number of rows per page. This determines the number of data pages that must be read from disk to create the index.
  • The columns in the index and the data types used. This determines the number of index pages that have to be written to disk.
• Examine column uniqueness. For more information, see Using Unique Indexes.
• Examine data distribution in indexed columns. Often, a long-running query is caused by indexing a column with few unique values, or by performing a join on such a column. This is a fundamental problem with the data and query itself and usually cannot be resolved without identifying this situation. For example, a physical telephone directory sorted alphabetically on last name will not expedite locating a person if all people in the city are named Smith or Jones.

For more information, see Designing an Index.

See Also

Statistical Information