Database Reference
In-Depth Information
Chapter 11
Key Lookups and Solutions
To maximize the benefit from nonclustered indexes, you must minimize the cost of the data retrieval as much as
possible. A major overhead associated with nonclustered indexes is the cost of excessive lookups, formerly known as
bookmark lookups
, which are a mechanism to navigate from a nonclustered index row to the corresponding data row
in the clustered index or the heap. Therefore, it makes sense to look at the cause of lookups and to evaluate how to
avoid this cost.
In this chapter, I cover the following topics:
•
The purpose of lookups
•
The drawbacks of using lookups
•
Analysis of the cause of lookups
•
Techniques to resolve lookups
Purpose of Lookups
When a SQL query requests information through a query, the optimizer can use a nonclustered index, if available,
on the columns in the
WHERE
or
JOIN
clause to retrieve the data. If the query refers to columns that are not part
of the nonclustered index being used to retrieve the data, then navigation is required from the index row to the
corresponding data row in the table to access these remaining columns.
For example, in the following
SELECT
statement, if the nonclustered index used by the optimizer doesn't include
all the columns, navigation will be required from a nonclustered index row to the data row in the clustered index or
heap to retrieve the value of those columns:
SELECT p.[Name],
AVG(sod.LineTotal)
FROM Sales.SalesOrderDetail AS sod
JOIN Production.Product p
ON sod.ProductID = p.ProductID
WHERE sod.ProductID = 776
GROUP BY sod.CarrierTrackingNumber,
p.[Name]
HAVING MAX(sod.OrderQty) > 1
ORDER BY MIN(sod.LineTotal);
The
SalesOrderDetail
table has a nonclustered index on the
ProductID
column. The optimizer can use the
index to filter the rows from the table. The table has a clustered index on
SalesOrderID
and
SalesOrderDetailID
,
so they would be included in the nonclustered index. But since they're not referenced in the query, they won't help
