Benjamin Nevarez Rotating Header Image


Auto-Parameterization in SQL Server

The SQL Server Query Optimizer might decide to parameterize some queries in those cases where the value of a specific parameter does not impact the choice of an execution plan, that is, in the cases where it does not matter which parameter value is used, the plan returned will be the same. This is a very conservative policy and SQL Server will only use it when it is safe to do it, so the performance of the queries are not negatively impacted. In this case the parameterized plan can be reused by similar queries which differ only on the value of their parameters. This feature, which helps to avoid optimization time and cost, is call auto-parameterization and was introduced with SQL Server 7.0.

For example, on the AdventureWorks database, the next two SQL statements will produce different execution plans and will not be parameterized, even when the queries are exactly the same and only the parameters are different. In this case the Query Optimizer decides that it is not safe to auto-parameterize them.

SELECT * FROM Sales.SalesOrderDetail

WHERE ProductID = 897

SELECT * FROM Sales.SalesOrderDetail

WHERE ProductID = 870

On the other hand, the following query will be auto-parameterized.

SELECT * FROM Sales.SalesOrderHeader

WHERE SalesOrderID = 43669

In this last example the column SalesOrderID is the primary key of the SalesOrderHeader table so it is guaranteed to be unique. In addition, because the query predicate is using an equality operator and SalesOrderID is unique, there will be always a maximum of one record returned by this query. In this case SQL Server decides that it is safe to parameterize this plan by using a clustered index seek operator. You can verify if your query is using a parameterized plan by inspecting the plan cache like in the following query


FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

WHERE text LIKE ‘%Sales%’

This code will output the following auto-parameterized query which will show placeholders for the parameter values like @1

(@1 int)SELECT * FROM [Sales].[SalesOrderHeader] WHERE [SalesOrderID]=@1

Finally, a new feature to parameterize queries more aggressively called forced parameterization was introduced with SQL Server 2005. This feature is disabled by default and can be enabled at the database level (or inside a query using the PARAMETERIZATION FORCED query hint). By enabling forced parameterization you can reduce the frequency of query optimizations but you may also choose suboptimal plans for some instances of some queries, so you should do extensive analysis and testing of your application to verify that your performance is in fact being improved. To differentiate from forced parameterization, auto-parameterization is also referred as simple parameterization.