Databases Reference
In-Depth Information
Of course, this doesn't mean that you're not supposed to trust your stored procedures
any more, or that maybe all your code is incorrect. This is just a problem that you need
to be aware of and research, especially if you have queries where performance changes
dramatically when different parameters are introduced. If you happen to have this
problem, there are a few choices available, which we'll explore now.
Optimize for a typical parameter
There might be cases when most of the executions of a query use the same execution
plan and you want to avoid an ongoing optimization cost. In these cases you can use the
OPTIMIZE
FOR
hint, which helps when an optimal plan is generated for the majority of
values used in a specific parameter. As a result, only the few executions using an atypical
parameter will not have an optimal plan.
Suppose that almost all of the executions of our stored procedure would benefit from the
previous plan using an Index Seek and a Key Lookup operator. To take advantage of that,
you could write the stored procedure as in Listing 6-16.
ALTER
PROCEDURE
test
(
@pid
int
)
AS
SELECT
*
FROM
Sales
.
SalesOrderDetail
WHERE
ProductID
=
@pid
OPTION
(
OPTIMIZE
FOR
(
@pid
=
897
))
Listing 6-16.
When you run the stored procedure for the first time, it will be optimized for the value
897, no matter what parameter value was actually specified for the execution. If you want
check, test the case in Listing 6-17.
