Chapter 13. Index design and maintenance

Well-designed indexes reduce the time needed to access required data, but designed and used incorrectly, they slow query performance, lengthen maintenance routines, and increase database size and storage costs. The ability to successfully design and maintain indexes is an important DBA skill that requires a good understanding of how SQL Server uses indexes along with a solid grasp of index-analysis and maintenance techniques.

In this chapter, we’ll begin with an overview of SQL Server indexes and cover strategies for successful index design. We’ll then focus on a number of index-analysis techniques aimed at identifying indexes to add, drop, and defragment. We’ll close the chapter with coverage of the tools and techniques involved in maintaining indexes and statistics, a crucial component of any database maintenance strategy.

13.1. An introduction to indexes

Like the index at the end of this book, indexes within a database enable fast access to table contents. With each table in a SQL Server 2008 database supporting up to 1000 indexes, fast access can be enabled for a wide variety of lookups. However, as you’ll soon see, poor index selection and maintenance can have the opposite effect, with reduced performance a common outcome.

13.2. Index design

13.3. Index analysis

13.4. Index maintenance

13.5. Managing statistics

13.6. Best practice considerations: index design and maintenance