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

In addition, you are no longer using a useful clustered index on lastNames, but you have to use the
non-clustered index, which results in an extra page read required on every lastNames access. Overall
the performance degradation isn't that great. On a system with a faster disk, performance might be
considerably higher.
Now see what DTA has to say about all these extra indexes. You can use the trace file you captured
earlier from the usp_loopMarriageUpdate trace but you'll have to change the DTA options. Open DTA
and select the trace file you used earlier. Select the Tuning Options tab and in the group titled Physical
Design Structures (PDS) to keep in database, change the default selection from Keep all existing PDS
to Do not keep any existing PDS. You can have DTA advise you about additional indexes as well by
keeping the same options selected under the PDS section or you can choose for DTA to just show you
which indexes to remove. For now, ask DTA to examine the existing indexes and to recommend addi-
tional indexes if it finds any, as shown in Figure 11-11.
Now you can start the Analysis Session and see what results DTA provides you with. The results are
shown in Figure 11-12.
Figure 11-12
DTA has easily found the bad indexes. It's recommending that you drop 12 of them and then in their
place create two new indexes and two new statistics.