Database Reference
In-Depth Information
Figure 11-4.
Execution plan for fetching more rows with an index hint
To benefit from nonclustered indexes, queries should request a relatively well-defined set of data. Application
design plays an important role for the requirements that handle large result sets. For example, search engines on the
Web mostly return a limited number of articles at a time, even if the search criterion returns thousands of matching
articles. If the queries request a large number of rows, then the increased overhead cost of a lookup can make the
nonclustered index unsuitable; subsequently, you have to consider the possibilities of avoiding the lookup operation.
Analyzing the Cause of a Lookup
Since a lookup can be a costly operation, you should analyze what causes a query plan to choose a lookup step
in an execution plan. You may find that you are able to avoid the lookup by including the missing columns in the
nonclustered index key or as
INCLUDE
columns at the index page level and thereby avoid the cost overhead associated
with the lookup.
To learn how to identify the columns not included in the nonclustered index, consider the following query, which
pulls information from the
HumanResources.Employee
table based on
NationalIDNumber
:
SELECT NationalIDNumber,
JobTitle,
HireDate
FROM HumanResources.Employee AS e
WHERE e.NationalIDNumber = '693168613' ;
This produces the following performance metrics and execution plan (see Figure
11-5
):
Table 'Employee'. Scan count 0, logical reads 4
CPU time = 0 ms, elapsed time = 53 ms
Figure 11-5.
Execution plan with a lookup
