Databases Reference
In-Depth Information
EXEC
test
@pid
=
870
Listing 6-17.
You can find the following entry close to the end of the XML plan.
<
ParameterList
>
<
ColumnReference
Column
=
"
@pid
"
ParameterCompiledValue
=
"
(897)
"
ParameterRuntimeValue
=
"
(870)
"
/>
</
ParameterList
>
Listing 6-18.
This entry clearly shows which parameter value was used during optimization, and which
one was used during execution. In this case, the stored procedure is optimized only once,
and the plan is stored in the plan cache and reused as many times as needed. The benefit
of using this hint, in addition to avoiding optimization cost, is that you have total control
over which plan is stored in the plan cache.
Optimize on every execution
If you want the best performance for every query, the solution might be to optimize for
every execution. You will get the best possible plan on every execution but will end up
paying for the optimization cost, so you'll need to decide if that's a worthwhile trade-off.
To do this, use the
RECOMPILE
hint as shown in Listing 6-19.
ALTER
PROCEDURE
test
(
@pid
int
)
AS
SELECT
*
FROM
Sales
.
SalesOrderDetail
WHERE
ProductID
=
@pid
OPTION
(
RECOMPILE
)
Listing 6-19.
