Databases Reference
In-Depth Information
I performed this exercise and found that the highest estimated number of records to
get a bookmark lookup for this particular example was 62, and the first one to have a
Clustered Index Scan was 106. Let us see both examples here, by running the query
with the
StateProvinceID
values 163 and 71. You will get the plans on Figure 2-9
and Figure 2-10, respectively.
Figure 2-9:
Plan for the StateProvinceID = 163 predicate.
Figure 2-10:
Plan for the StateProvinceID = 71 predicate.
By looking at the plans, you can see that, for this specific example, the Query Optimizer
selects a bookmark lookup for an estimated 62 records, and changes to a Clustered Index
Scan when that estimated number of records goes up to 106 (there are no estimated
values between 62 and 106 for this particular statistics object). Although in this case both
the actual and estimated number of rows are the same, keep in mind that the Query
Optimizer makes its decision based on the estimated number of rows. It does not know
the actual number of rows when the execution plan is generated (as the candidate plans
