Contents
When to use forced parameterization in SQL Server?
When enabled, plans do get reused – which means you may suddenly have parameter sniffing issues that you didn’t have before (because before, every query got its own hand-crafted plan.) So when should you use Forced Parameterization?
How to fix multiple plans for one query?
You can do EXEC sp_BlitzCache @SortOrder = ‘query hash’ to find the queries to focus on, too. SQL ConstantCare® suggests forced parameterization when we see that you have a ton of these over time, and can’t keep plans in the cache as a result You could fix this by changing the application so that it uses parameterized SQL instead of strings.
Why are there multiple execution plans in SQL Server?
Queries have been found with multiple execution plans. This can be caused by multiple things, but the two primary causes are a lack of parameterization or improper parameterization. When queries aren’t parameterized, SQL Server will end up creating a separate plan for each set of literal values.
What happens if I run the same query twice?
If you right-click on a database, click Properties, Options, and scroll to the Miscellaneous section, you’ll see Parameterization. The default is Simple, but you can also choose Forced. Setting it to Forced takes effect instantly, doesn’t require a restart, and then has a different behavior. If I run the same queries again, here’s the new output:
When the PARAMETERIZATION database option is set to FORCED, you can specify that for a certain class of queries, only simple parameterization is attempted, not forced parameterization.
How to override the parameterization of a database?
You can override the parameterization behavior of a database by using plan guides in the following ways: When the PARAMETERIZATION database option is set to SIMPLE, you can specify that forced parameterization is attempted on a certain class of queries.
Do you need to enable parameterization on all queries?
As a database administrator, you have determined that you do not want to enable forced parameterization on all queries in the database. However, you do want to avoid compilation costs on all queries that are syntactically equivalent to the previous query, but differ only in their constant literal values.
How to set query parameterization behavior using plan guides?
You do this by creating a TEMPLATE plan guide on the parameterized form of the query, and specifying the PARAMETERIZATION FORCED query hint in the sp_create_plan_guide stored procedure. You can consider this kind of plan guide as a way to enable forced parameterization only on a certain class of queries, instead of all queries.
What does it mean when a query is parameterized?
This means that any literal values that are contained in a query are substituted with parameters. This process is referred to as simple parameterization. When SIMPLE parameterization is in effect, you cannot control which queries are parameterized and which queries are not.