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.

About the author

Benjamin Nevarez Benjamin Nevarez is a database professional based in Los Angeles, California who specializes in SQL Server query tuning and optimization. He is the author of three books, “High Performance SQL Server”, “SQL Server 2014 Query Tuning & Optimization” and “Inside the SQL Server Query Optimizer” and has also coauthored other books including “SQL Server 2012 Internals”. Benjamin has also been a speaker at many SQL Server conferences and events around the world including the PASS Summit, SQL Server Connections and SQLBits. His blog can be found at and he can also be reached on twitter at @BenjaminNevarez.


  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… #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 […]

  4. Shinya says:

    I wouldn’t look to me for ginuadce there It was a long, painful, arduous process.I recently switched to a Mac, so I’ve got MySQL on it. That sort of motivated me to try and gradually move away from SQL Server. Nothing against SQL Server, but for something like this blog there’s no reason not to use a free and readily available solution.So my situation was my blog entries were in a SQL Server database up on a VPS. I wanted to convert them to MySQL here on my local machine.I’ve still got a PC running, so using Enterprise Manager I connected to the remote SQL Server instance, and tried exporting the data to plain text files (to be imported into MySQL). Unfortunately, on a couple of tables that had NTEXT datatypes, the export failed. I spent a while trying to figure out how to get around that, but every avenue I looked into ended up with the same unhappy result.Since all I really needed to do was one database (the one powering the blog), I ended up going the brute force route and simply writing a cfm page that did a SELECT * on each table, then looped over the result and did an INSERT into an empty table in a newly created database on the remote MySQL database.Not particularly elegant, but it got the job done.I’m sure there has to be a better way. If anyone has any suggestions, please feel free to post them. I may be done with migrating the blog over, but you never know when the need to do something like this will arise again.

  5. utpokemap says:

    In this case, you could use a plan guide to change such statements to use Simple parameterization.

Leave a Reply

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