Database Reference
In-Depth Information
Drawbacks of Lookups
A lookup requires data page access in addition to index page access. Accessing two sets of pages increases the number
of logical reads for the query. Additionally, if the pages are not available in memory, a lookup will probably require
a random (or nonsequential) I/O operation on the disk to jump from the index page to the data page as well as
requiring the necessary CPU power to marshal this data and perform the necessary operations. This is because, for a
large table, the index page and the corresponding data page usually won't be directly next to each other on the disk.
The increased logical reads and costly physical reads (if required) make the data retrieval operation of the
lookup quite costly. In addition, you'll have processing for combining the data retrieved from the index with the data
retrieved through the lookup operation, usually through one of the JOIN operators. This cost factor is the reason that
nonclustered indexes are better suited for queries that return a small set of rows from the table. As the number of rows
retrieved by a query increases, the overhead cost of a lookup becomes unacceptable.
To understand how a lookup makes a nonclustered index ineffective as the number of rows retrieved increases,
let's look at a different example. The query that produced the execution plan in Figure
11-2
returned just a few rows
from the
SalesOrderDetail
table. Leaving the query the same but changing the filter to a different value will, of
course, change the number of rows returned. If you change the parameter value to look like this:
SELECT *
FROM Sales.SalesOrderDetail AS sod
WHERE sod.ProductID = 793;
then running the query returns more than 700 rows, with different performance metrics and a completely different
execution plan (Figure
11-3
).
Table 'SalesOrderDetail'. Scan count 1, logical reads 1246
CPU time = 15 ms, elapsed time = 137 ms.
Figure 11-3.
A different execution plan for a query returning more rows
To determine how costly it will be to use the nonclustered index, consider the number of logical reads (1,246)
performed by the query during the table scan. If you force the optimizer to use the nonclustered index by using
an index hint, like this:
SELECT *
FROM Sales.SalesOrderDetail AS sod WITH (INDEX (IX_SalesOrderDetail_ProductID))
WHERE sod.ProductID = 793 ;
then the number of logical reads increases from 1,246 to 2,173:
Table 'SalesOrderDetail'. Scan count 1, logical reads 2173
CPU time = 31 ms, elapsed time = 319 ms.
Figure
11-4
shows the corresponding execution plan.
