Databases Reference
In-Depth Information
ALTER
DATABASE
AdventureWorks
SET
PARAMETERIZATION
FORCED
Listing 6-29.
With this new configuration, the two queries in Listings 6-24 and 6-25, which returned
two distinct execution plans, will now be parameterized and produce only one plan. Run
the following query again:
SELECT
*
FROM
Sales
.
SalesOrderDetail
WHERE
ProductID
=
897
Listing 6-30.
Because of the parameter used on this instance of the query, SQL Server will create a
plan using an Index Seek and a Key Lookup, which may be used by any similar query.
Of course, the risk for this specific example is that the first query will get a plan which
is tailored to its parameters, but which may be suboptimal for some other instances
of the same query with different parameters (like the
ProductID
870
query used in
Listing 6-25). Run the following query to verify that the plan was, in fact, parameterized:
SELECT
text
FROM
sys
.
dm_exec_cached_plans
CROSS
APPLY
sys
.
dm_exec_sql_text
(
plan_handle
)
WHERE
text
LIKE
'%Sales%'
Listing 6-31.
It will show an output similar to this:
(@0 int)select * from Sales . SalesOrderDetail where ProductID = @0
Listing 6-32.
