Database Reference
In-Depth Information
Figure 26-12.
Forced Parameterization and Filtered Indexes: Execution plans
One of the good use-cases for forced parameterization is the complex ad-hoc queries submitted by a client
application in cases when the choice of execution plan does not depend on constant values. While it is better to
change the client application and parameterize queries, it is not always possible.
Listing 26-22 shows an example of such a query. Every query execution leads to a compilation, and it adds an
entry to plan cache. Such a query benefits from forced parameterization because the most optimal execution plan for
the query is a Clustered Index Seek, and it does not change based on the constant/parameter value.
Listing 26-22.
Example of a query that benefits from forced parameterization
select top 100 RecId, /* Other Columns */
from dbo.RawData
where RecID > 432312 -- Client application uses different values at every call
order by RecId
With all that being said, you should be careful with forced parameterization when you enable it at the database
level. It is safer to enable it on an individual query level if needed.
Plan Guides
Query hints can be extremely useful in helping to resolve various plan caching related issues. Unfortunately, in some
cases, you are unable to modify the query text, either because you do not have access to the application code or if the
recompilation and redeployment is impossible or impractical.
You can solve such problems by using plan guides, which allow you to add hints to the queries or even force
specific execution plans without changing a query's text. You can create them with
sp_create_plan_guide
and
manage them with
sp_control_plan_guide
stored procedures.
