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

Married 20 people in 9110mS at a rate of 2.19539 per Second
Married 20 people in 6936mS at a rate of 2.88351 per Second
Married 20 people in 5280mS at a rate of 3.78788 per Second
Married 20 people in 5376mS at a rate of 3.72024 per Second

That's pretty remarkable; you have gone from just over 1 update per second to 2-4 updates per second.

Now check the waits again:

spid kpid blocked waittime lastwaittype

58 4688 0 859 LOGMGR_RESERVE_APPEND

This time the waits are predominantly this one wait type.

Finally, take a quick look at the showplan_text output to see how the DTA recommendations are chang-
ing the query plan.

This is the showplan output for the update before applying the DTA recommendations:

|--Table Update(OBJECT:([People].[dbo].[people]), SET:([People].[dbo].
[people].[lastName] = RaiseIfNull([Expr1016])))
|--Table Spool
|--Compute Scalar(DEFINE:([Expr1016]=[Expr1016]))
|--Nested Loops(Left Outer Join)
|--Top(ROWCOUNT est 0)
| |--Table
Scan(OBJECT:([People].[dbo].[people]), WHERE:([People].[dbo].[people].
[personID]=[@girlID]) ORDERED)
|--Assert(WHERE:(CASE WHEN [Expr1015] > (1) THEN (0) ELSE

NULL END))

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

|--Table
Scan(OBJECT:([People].[dbo].[people]), WHERE:([People].[dbo].[people].
[personID]=[@BoyID]))

You can clearly see that you are using a table scan to apply the update to People .

This is the showplan output after applying the DTA recommendations:

|--Clustered Index
Update(OBJECT:([People].[dbo].[people].[_dta_index_people_c_6_2089058478__K1]),
SET:([People].[dbo].[people].[lastName] = RaiseIfNull([Expr1016])))
|--Table Spool
|--Compute Scalar(DEFINE:([Expr1016]=[Expr1016]))
|--Nested Loops(Left Outer Join)
|--Top(ROWCOUNT est 0)
| |--Clustered Index
Seek(OBJECT:([People].[dbo].[people].[_dta_index_people_c_6_2089058478__K1]),
SEEK:([People].[dbo].[people].[personID]=[@girlID]) ORDERED FORWARD)
|--Assert(WHERE:(CASE WHEN [Expr1015] > (1)