Once the Query Optimizer comes up with a plan, which may have taken a considerable amount of work, SQL Server does its best to ensure that you can leverage all that costly work again. It does this by caching the plan it just created, and taking steps to ensure that the plan is reused as widely as possible. It does this by using parameterization options.
About Parameterization
Parameterization is a process whereby SQL Server takes the T-SQL you entered and looks for ways to replace values that may be variables with a token, so that if a similar query is processed, SQL Server can identify it as being the same underlying query, apart from some string, or integer values, and make use of the already cached plan. For example, the following is a basic T-SQL query to return data from the AdventureWorks2012 database:
select * from person.person where lastname = 'duffy'
Parameterization of this query would result in the string ‘duffy’being replaced with a parameter such that if another user executes the following query, the same plan would be used, saving on compilation time:
select * from person.person where lastname = 'miller'
Note that this is just an example, and this particular query gets a trivial plan, so it isn’t a candidate for parameterization.
The SQL Server Books Online topic on “Forced Parameterization” contains very specific details about what can and cannot be converted to a parameter.
To determine whether a query has been parameterized, you can search for it in the DMV sys.dm_exec_cached_plans (after first executing the query to ensure it is cached). If the SQL column of this DMV shows that the query has been parameterized, any literals from the query are replaced by variables, and those variables are declared at the beginning of the batch.
Simple or Forced Parametrization
Parameterization is controlled by one of two SQL Server configuration options — simple or forced:
- Simple parameterization — The default operation of SQL Server is to use simple parameterization on all queries that are suitable candidates. Books Online provides numerous details about which queries are selected and how SQL Server performs parameterization. Using simple parameterization, SQL Server is able to parameterize only a relatively small set of the queries it receives.
-
Forced parameterization — For more control over database performance, you can specify that SQL Server use forced parameterization. This option forces SQL Server to parameterize all literal values in any select, insert, update, or delete statement queries. There are some exceptions to this, which are well documented in SQL Server Books Online. Forced parameterization is not appropriate in all environments and scenarios. It is recommended that you use it only for a very high volume of concurrent queries, and when you are seeing high CPU from a lot of compilation/recompilation. If you are not experiencing a lot of compilation/recompilation, then forced parameterization is probably not appropriate. Using forced in the absence of these symptoms may result in degraded performance and/or throughput because SQL Server takes more time to parameterize a lot of queries that are not later reused. It can also lead to parameter sniffing, causing inappropriate plan use.
Forced parameterization can also be more finely controlled through the use of plan guides.
See also “SQL Server Plan Caching and Recompliation: Plan Cache.”
[…] the same bucket are all variations on the same query, then try to get better plan reuse through parameterization. If the queries are already quite different, and there is no commonality that would allow […]