Summary

One of the most important aspects to improving SQL Server performance is proper index design. Choosing the appropriate indexes to be used by SQL Server to process queries involves thoroughly understanding the queries and transactions being run against the database, understanding the bias of the data, understanding how SQL Server uses indexes, and staying aware of the performance implications of overindexing tables in an OLTP environment. In general, consider using clustered indexes to support range retrievals or when data needs to be sorted in clustered index order; use nonclustered indexes for single or discrete row retrievals or when you can take advantage of index covering. Use the Index Tuning Wizard to help you identify the appropriate mix of indexes that will optimize performance for your database applications.

To really make good index design choices, it helps to have an understanding of the SQL Server query optimizer to know how it uses indexes and index statistics to develop query plans. This would be a good time to read Chapter 35.