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

CPU time = 0 ms, elapsed time = 1 ms.
Table 'BoysNames'. Scan count 1, logical reads 1, physical reads 0, read-ahead
reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'GirlsNames'. Scan count 1, logical reads 1, physical reads 0, read-ahead
reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'lastNames'. Scan count 0, logical reads 2, physical reads 0, read-ahead
reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'people'. Scan count 0, logical reads 3, physical reads 0, read-ahead
reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'people'. Scan count 0, logical reads 3, physical reads 0, read-ahead
reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 33 ms.
On the cold run, you increased the number of logical reads from 1 to 3. Physical reads didn't change
because the whole database is pretty well cached by now. This change is too small to see on a
single run, so you'll need to run the query a few thousand times. Before you do that, take a look at the
showplan_text output.
The only obvious change here is that you can see that you are now doing an insert into a clustered index
table versus the heap you were inserting into before:
|--Clustered Index
Insert(OBJECT:([People].[dbo].[people].[_dta_index_people_c_6_2089058478__K1]),
SET:([People].[dbo].[people].[firstName] = RaiseIfNull([@BoysName]),[People].
[dbo].[people].[lastName] = RaiseIfNull([@lastName]),[People].[dbo].[peop
Now look to see how this has changed the insert rate when you run usp_loopPeopleInsert .Thisisnow
reporting the following:
Inserted 10000 people in 5250mS at a rate of 1904.76 per Second
Inserted 10000 people in 5296mS at a rate of 1888.22 per Second
Inserted 10000 people in 5233mS at a rate of 1910.95 per Second
Inserted 10000 people in 5300mS at a rate of 1886.79 per Second
Inserted 10000 people in 5233mS at a rate of 1910.95 per Second