Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 286

perspective. The alternative to a non-clustered index bookmark lookup is a clustered index scan, which is
a table scan with help. Even SQL Server will give up and revert to a clustered index scan if the optimizer
deems the bookmark lookup operation to be too expensive.

SQL Server 2005 has made some changes relative to bookmarks. One of the most obvious is a change
in SP2 to add two new operators: RID Lookup and Key Lookup Operators. You'll also notice that the
old SQL Server 2000 bookmark operator has been depreciated. If you haven't installed this service pack,
you'll only see a Clustered Index Scan operator and you'll have to dig to find the LOOKUP keyword on the
SEEK operation. Overall, you'll notice that SQL Server 2005 has less tolerance for the bookmark lookup
operation and will use the clustered index scan more frequently. You can play around with this, but it
appears that the break-even mark occurs when the selectivity indicates that the select density includes
around 20 percent of the table rows. Until that point, you'll see these bookmark operations. To see an
example, run this query:

SELECT PurchaseOrderNumber
FROM Sales.SalesOrderHeader
WHERE CustomerId = 75

In the query execution plan, the operation is Clustered Index Seek with the LOOKUP keyword to indicate
a bookmark operation.

Nested Loops(Inner Join, OUTER REFERENCES:([SalesOrderID]))
|--Index Seek(OBJECT:([IX_SalesOrderHeader_CustomerID]),
SEEK:([CustomerID]=(75)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([PK_SalesOrderHeader_SalesOrderID]),
SEEK:([SalesOrderID]=[SalesOrderID] LOOKUP ORDERED FORWARD)

Note in the plan that the optimizer performs a SEEK operation into the SalesOrderHeader table using
the SalesOrderId. Sometimes you can help the plan by giving the optimizer this information up front.
Change the Query to include an additional predicate like this:

SELECT PurchaseOrderNumber
FROM Sales.SalesOrderHeader SOH
WHERE SOH.SalesOrderID = SOH.SalesOrderID
AND SOH.CustomerId = 75

Table9-17showstheimprovementintheplannumbersaccordingly.

Table 9-17: Boosting Performance with an Additional Predicate

Method % Cost %Key Lookup Reads Duration

With Supplied Seek 15 51 52 406ms

Without 85 92 50 420ms

The reason that the optimizer is giving this plan such low costing information is because the extra
predicate tricks it into thinking that only one row will be resolved. You can see the difference in the