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

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'people'.
Scan count 2, logical reads 32281, physical reads 0, read-ahead reads 0, lob logi-
cal reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'.
Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logi-
cal reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 703 ms, elapsed time = 713 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 812 ms, elapsed time = 980 ms.
There was a significant improvement between the cold and warm run, down to a reduction in the com-
pilation time and faster access as the tables are now mostly loaded into memory. This is seen in the
reduction in read ahead reads between the cold and warm runs.
This procedure is taking between 10 seconds for a cold run and 1 second for a warm run so it should be
much easier to see any improvement that DTA can make. However, you'll still run it in a loop and see
what the average update rate is over a longer period of executions.
This is the command to run:
sqlcmd -E -d people -Q"exec usp_loopMarriageUpdate 100, 10"
These are the results:
Married 20 people in 15326mS at a rate of 1.30497 per Second
Married 20 people in 18610mS at a rate of 1.07469 per Second
Married 20 people in 15470mS at a rate of 1.29282 per Second
Married 20 people in 14610mS at a rate of 1.36893 per Second
Married 20 people in 14890mS at a rate of 1.34318 per Second
Married 20 people in 17076mS at a rate of 1.17123 per Second
The results show that the query is taking between 1 and 1.36 seconds to execute so it should be relatively
easy to see any performance improvement.
Before going on to run DTA, take a quick look at the wait types. Run the command again and also run
the monitoring code and capture the waits from sysprocesses . The output should look something
like this:
set nocount on
while 1 > 0
begin
select spid, kpid, blocked, waittime, lastwaittype, waitresource