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

This shows that you haven't caused any impact on the insert rate, which remains at around 1800 to 1900
per second. This is more evidence that the limiting factor for this insert is the speed with which you can
write to the log (not surprisingly, on a laptop system this is pretty slow). This can also be the case on a
large enterprise server where the amount of log activity could be much higher but the log files are on a
slow disk or poorly configured disk array, which yet again ultimately limits insert performance.
The wait stats for the query show that you are still waiting on WriteLog , so you haven't seen a change to
the point where the new index started to be the bottleneck.
TooMany Indexes?
One final scenario you are going to look at is how DTA tells you when you have too many indexes. To
make this obvious, you'll add a whole stack of other indexes to the four tables in this scenario, run DTA
against the insert and update procedures, and see what it tells you about indexes you aren't using.
Here is the script to create some bad indexes to see what DTA will recommend:
-- Create Bad indexes
use people
go
create clustered index cix_boysnames on BoysNames ( ID, Name)
go
create index ix_boysnames_id on BoysNames (id)
go
create index ix_boysnames_name on BoysNames (name)
go
create clustered index cix_girlsnames on GirlsNames ( ID, Name)
go
create index ix_Girlsnames_id on GirlsNames (id)
go
create index ix_Girlsnames_name on GirlsNames (name)
go
create clustered index cix_LastNames on LastNames ( ID, Name)
go
create index ix_Lastnames_id on LastNames (id)
go
create index ix_Lastnames_name on LastNames (name)
go
create clustered index cix_people on people(firstname)
go
create index ix_people_id on people(personid)
go
create index ix_people_dob on people(dob)
go
create index ix_people_lastname on people(lastname)
go
create index ix_people_dod on people(dod)
go
create index ix_people_sex on people(sex)
go