Chapter 34. Indexes and Performance

by Ray Rankins

IN THIS CHAPTER

• Index Usage Criteria

• Index Selection

• Evaluating Index Usefulness

• Index Statistics

• Index Design Guidelines

• Indexed Views

• Indexes on Computed Columns

• The Index Tuning Wizard

• Choosing Indexes: Query Versus Update Performance

There can be a number of reasons why SQL Server performance might be less than optimal, but in many cases, it comes down to poor index design, or simply a lack of appropriate indexes. Often, substantial performance gains can be realized in your applications by creating the proper indexes to support the queries and operations being performed in SQL Server. The great benefit here is that your applications will immediately reap the benefits of the indexes without having to rewrite the code in any way.

You need to closely examine the indexes defined on your tables to ensure that the appropriate indexes exist that the optimizer can use to avoid table scans and reduce the I/O costs of resolving queries. You also need to have a good understanding of the criteria SQL Server uses to determine when to use an index.

It's also important to keep in mind that although many indexes on a table can help improve response time for queries and reports , too many indexes can hurt the performance of inserts , updates, and deletes. At other times, your other index design decisions, such as which column(s) to create your clustered index on, might be influenced as much by how the data is inserted and modified and what the possible locking implications might be, as it is by the query response time alone.

Clearly, proper index design is a key issue in achieving optimum SQL Server performance for your applications. In Chapter 33, "SQL Server Internals," you learned about the structure of an index and how indexes are maintained by SQL Server. In this chapter, the focus is on how indexes are evaluated and used by SQL Server to improve query response time. Using this information, this chapter explores the issues and factors that influence index design.

NOTE

This chapter assumes that you already have an understanding of the different types of indexes, their structures, and how to define them. For more information on index types, how to create indexes, and the underlying structure of indexes, see Chapter 13, "Creating and Managing Indexes," and Chapter 33.