Databases Reference
In-Depth Information
Figure 2-8:
Plan switching to a Clustered Index Scan.
This time, the Query Optimizer has selected a Clustered Index Scan and returned 308
records (compared to just a single record for the
StateProvinceID
32). So the Query
Optimizer is producing two different execution plans for the same query, with the only
difference being the value of the
StateProvinceID
parameter. As I will show in more
detail in the next chapter, in this case, the Query Optimizer uses the value of the query's
StateProvinceID
parameter to estimate the cardinality of the predicate as it tries to
produce an efficient plan for that parameter.
This time, the Query Optimizer estimated that more records could be returned than
when
StateProvinceID
was equal to 32, and it decided that it was cheaper to do a Table
Scan than to do many bookmark lookups. At this stage, you may be wondering at what
point the Query Optimizer decides to change from one method to another. Well, since a
bookmark lookup requires random I/O, which is very expensive, it would not take many
records for the Query Optimizer to switch from a bookmark lookup to a Clustered Index
Scan (or a Table Scan). We already know that, when the query returned one record, for
StateProvinceID
32, the Query Optimizer chose a bookmark lookup. We also saw that,
when we requested the records for
StateProvinceID
20, which returned 308 records,
it used a Clustered Index Scan. Logically, we can try requesting somewhere between 1 and
308 records to find this switch-over point, right?
Actually, as you may already suspect, this is a cost-based decision which does not
depend on the actual number of records returned by query, but rather the
estimated
number of records. We (or rather, the Query Optimizer) can find these estimates by
analyzing the appropriate statistics object for the
IX_Address_StateProvinceID
index, something that will be covered in
Chapter 3
,
Statistics and Cost Estimation
.
