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

|--Clustered Index Scan(OBJECT:([People].[dbo].[lastNames].[cix_LastNames_ID]))

This shows that you are in fact using the newly added clustered index. Now see how much this affects
the execution of the query. Remember that before you were able to achieve 1300-1400 inserts per second.

Inserted 1000 people in 530mS at a rate of 1886.79 per Second
Inserted 1000 people in 606mS at a rate of 1650.17 per Second
Inserted 1000 people in 610mS at a rate of 1639.34 per Second
Inserted 1000 people in 533mS at a rate of 1876.17 per Second

This shows that the rate of insertion has increased to 1600-1900 per second. That's quite an improvement
for adding a clustered index.

Run the monitor script again to check what you're waiting on.

set nocount on
while 1 > 0
begin
select spid, kpid, blocked, waittime, lastwaittype, waitresource
from master..sysprocesses
where program_name = 'SQLCMD'
waitfor delay '00:00:00.05'
end

spid kpid blocked waittime lastwaittype

54 5804 0 0 WRITELOG

54 5804 0 0 WRITELOG

54 5804 0 0 WRITELOG

54 5804 0 0 WRITELOG

No surprises there, it remains the log that's limiting the insert performance.

Indexes for Updates
Next you want to tune the update query usp_marriageUpdate . Start that by capturing some metrics
around the query's performance. Before you do that, fill the table up a bit by writing a million rows to
the people table. You need that many to be able to get a full set of results for the usp_marriageUpdate
query, which pulls out the top 1000 rows for a given date range.

Truncate the people table, and run usp_loopPeopleInsert to fill it with 1,000,000 rows. After that
you can start capturing metrics around the raw performance again by running the script DTAmarriage
Update.sql. Here is the code to do this if you want to follow along:

USE people
GO
TRUNCATE TABLE people
GO
EXEC usp_loopPeopleInsert 500000
GO

Here are the results of the cold run and three warm runs, edited to remove the many extra rows and with
some additional formatting for clarity: