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

SELECT text

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.

About the author

Benjamin Nevarez Benjamin Nevarez is a database professional based in Los Angeles, CA, and author of "Inside the SQL Server Query Optimizer". He has also contributed to other SQL Server books including "SQL Server 2012 Internals". Benjamin has 20 years of experience with relational databases and has been working with SQL Server since version 6.5. He holds a Master’s Degree in Computer Science and has been a speaker at many SQL Server conferences, including the PASS Summit and SQL Server Connections. Benjamin’s blog is at http://www.benjaminnevarez.com, can be reached by e-mail at admin at benjaminnevarez dot com, on twitter at @BenjaminNevarez and on .

3 Comments

  1. [...] This post was mentioned on Twitter by Benjamin Nevarez. Benjamin Nevarez said: Auto-Parameterization in SQL Server: The SQL Server Query Optimizer might decide to parameterize so… http://bit.ly/abhQUj #sql #sqlserver [...]

  2. [...] query is not auto-parameterized, and as a result, changing the last name or the postal code in the Where clause will generate a new [...]

  3. [...] query is not auto-parameterized, and as a result, changing the last name or the postal code in the Where clause will generate a new [...]

Add Comment Register



Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>