Databases Reference
In-Depth Information
Run the following statement to execute the stored procedure, and request to display the
execution plan:
EXEC
test
@pid
=
897
Listing 6-12.
The Query Optimizer estimates that only a few records will be returned by this query,
and produces the execution plan shown in Figure 6-11, which uses an Index Seek operator
to quickly find the records on an existing non-clustered index, and a Key Lookup operator
to search on the base table for the remaining columns requested by the query.
Figure 6-11:
Plan using Index Seek and Key Lookup operators.
This combination of Index Seek and Key Lookup operators was a good choice because,
although it's a relatively expensive combination, the query was highly selective. However,
what if a different parameter is used, producing a less selective predicate? For example,
try the following query, including a
SET
STATISTICS
IO
ON
statement to display the
amount of disk activity generated by the query:
SET
STATISTICS
IO
ON
GO
EXEC
test
@pid
=
870
GO
Listing 6-13.
