Databases Reference
In-Depth Information
SELECT
text
FROM
sys
.
dm_exec_cached_plans
CROSS
APPLY
sys
.
dm_exec_sql_text
(
plan_handle
)
WHERE
text
LIKE
'%SalesOrderID%'
Listing 6-27.
The output will include the following auto-parameterized query which will show
placeholders like
@1
for the parameter values:
(@1 int)SELECT * FROM [Sales].[SalesOrderHeader] WHERE [SalesOrderID]=@1
Listing 6-28.
Forced parameterization
Finally, a new feature, called forced parameterization, was introduced in SQL Server
2005 to parameterize queries more aggressively. This feature is disabled by default and
can be enabled at the database level, or it can be used on an individual query by using the
PARAMETERIZATION
FORCED
query hint.
By enabling forced parameterization you can reduce the frequency of query optimiza-
tions, but you may also introduce suboptimal plans for some instances of those queries,
so you should do extensive analysis and testing of your application to verify that your
performance is, in fact, being improved. To differentiate it from forced parameterization,
auto-parameterization is also referred to as simple parameterization. For more
information about forced parameterization please consult Books Online.
To show how forced parameterization works, execute the statement in Listing 6-29 to
enable forced parameterization at the database level.
