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

gains and so may miss out on recommendations for some of the smaller queries. Because of this, it's still
worthwhile to tune individual queries after applying recommendations for the whole workload.
A good example of this can be seen when DTA didn't recommend indexes on the lastnames table when
tuning the workload. When you tuned just the usp_peopleInsert procedure, DTA was able to see
enough of an improvement to recommend indexes. Figure 11-14 shows the recommendations DTA comes
up with for the lastnames table when tuning the usp_peopleInsert procedure.
Figure 11-14
Section 3: Index Maintenance
There are a number of problems that can occur with indexes over time as data changes in the underlying
table. As rows are inserted, deleted, and updated, the distribution of data through the index can become
unbalanced, with some pages becoming fully packed. This results in additional inserts causing immediate
page splits. Other pages can become very sparsely packed, causing many pages to have to be read to
access a few rows of data. These problems can be easily overcome with some simple index maintenance.
The first thing you need to do is implement some monitoring to figure out when the indexes are getting
to the stage where they need attention. The second step is figuring out which of the various options for
index maintenance you should use to clean up the index.