Databases Reference
In-Depth Information
The XML plan for this execution:
EXEC
test
@pid
=
897
Listing 6-20.
… will show:
<
ParameterList
>
<
ColumnReference
Column
=
"
@pid
"
ParameterCompiledValue
=
"
(897)
"
ParameterRuntimeValue
=
"
(897)
"
/>
</
ParameterList
>
Listing 6-21.
Local Variables and OPTIMIZE FOR UNKNOWN
Another solution that has been traditionally implemented in the past is the use of local
variables instead of parameters. As mentioned in
Chapter 3, Statistics and Cost Estimation
,
SQL Server is not able to see the values of local variables at optimization time, as these
values are only known at execution time. However, by using local variables you are not
only disabling parameter sniffing, you're also disabling the Query Optimizer's option of
using the statistics histogram to find an optimal plan for the query. Instead, it will rely on
just the density information of the statistics object, as explained in Chapter 3.
This solution will simply ignore the parameter values and use the same execution plan
for all the executions, but at least you're getting a consistent plan every time. A varia-
tion of the
OPTIMIZE
FOR
hint shown previously,
OPTIMIZE
FOR UNKNOWN
, which was
introduced with SQL Server 2008, has the same effect.
