Tuning Indexes - Professional SQL Server 2005 Performance Tuning - page 353

Tuning Indexes

Indexes are the solution to many performance problems but as in most parts of life, too much of
a good thing can be bad for you. A thorough understanding of indexes is essential for a DBA to
make the right choices and SQL Server 2005 brings new features and options to help even the most
hardened indexing expert. We've broken this chapter into four sections to make it easy to dip into:

❑ ''Section 1: Indexing Review'' discusses indexing terminology and introduces some new

features and tips. This should be regarded as a refresher rather than an introduction.

❑ ''Section 2: Tuning'' focuses on using Database Tuning Advisor and the built-in tuning

tool. It also covers tuning indexes with no server impact using the built-in Dynamic Man-
agement Views.

❑ ''Section 3: Maintenance'' is predominantly the domain of the operational DBA and will

show you how and why you need to maintain your indexes.

❑ ''Section 4: Table and Index Partitioning'' is an advanced topic but is made much more

accessible with the built-in features of SQL Server 2005. This section covers how, why,
and where you should implement it.

Sample Database
This chapter uses a very simple database called People to demonstrate the concepts. The database
contains only four tables:

CREATE TABLE people
(

personId UNIQUEIDENTIFIER DEFAULT newsequentialid(),
firstname VARCHAR(80) not null,
lastname VARCHAR(80) not null,
dob DATETIME not null,
dod DATETIME null,