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

THEN (0) ELSE NULL END))

|--Stream
Aggregate(DEFINE:([Expr1015]=Count(*), [Expr1016]=ANY([People].[dbo].[people].

[lastName])))

|--Clustered Index
Seek(OBJECT:([People].[dbo].[people].[_dta_index_people_c_6_2089058478__K1]),
SEEK:([People].[dbo].[people].[personID]=[@BoyID]) ORDERED FORWARD)

This starts with the clustered Index tag, showing that you are now using the newly created clustered
index to apply the update, and this is what's giving you the big benefit.

Reassessing Inserts after AddingUpdate Indexes
Now go back and measure the impact the update indexes have had on the insert procedure. You haven't
done anything else to the insert procedure, but the update procedure added a clustered index to People .
Now the insert procedure will have to contend with the additional overhead of index maintenance,
inserting new records and splitting index pages. It's going to be interesting to see how much slower this
makes the inserts and how it changes what you are waiting on.

Start by looking at the stats time and I/O output.

-- COLD RUN
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 51 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'BoysNames'. Scan count 1, logical reads 1, physical reads 1, 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 = 21 ms.
Table 'GirlsNames'. Scan count 1, logical reads 1, physical reads 1, 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 1, logical reads 9, physical reads 1, read-ahead
reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.

SQL Server Execution Times:
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.