SQL Server Simple and Forced Parameterization

By:   |   Comments (3)   |   Related: > Performance Tuning


I have heard about parameterization for SQL Server queries, but what is Forced and Simple Parameterization and which one should I use for my SQL Server database?


There are two different parameterization options that one can use in SQL Server. Simple parameterization and Forced parameterization. Let's discuss each a little more in detail.

Simple Parameterization

When you execute a SQL statement without parameters, SQL Server internally will add parameters where needed so that it can try to reuse a cached execution plan. For example, if you look at the execution plan of the following statement you will see that SQL Server changes the WHERE value to a parameter (@1):

SELECT * FROM AdventureWorks2012.Sales.CreditCard WHERE CreditCardID = 11

Simple Parameterization

SQL Server builds this execution plan as if a parameter was the input instead of the number 11. Because of this parameterization, the following two statements show an example of SQL Server reusing the same execution plan even though the data results are different:

SELECT * FROM AdventureWorks2012.Sales.CreditCard WHERE CreditCardID = 11
SELECT * FROM AdventureWorks2012.Sales.CreditCard WHERE CreditCardID = 207

SQL Server builds this execution plan as if a parameter was the input

This is the default behavior for Simple parameterization, however, it only applies to a small class of queries. If you want all your queries parameterized, you will need to enable the option, Forced parameterization.

Forced Parameterization

Forced parameterization is when the database engine parameterizes any literal value that appears in a SELECT, UPDATE, INSERT, or DELETE statement submitted in any form, but there are a few exceptions.  Refer to this article for a list of these exceptions.

Some applications use queries that pass in literals as opposed to stored procedures that pass in parameters. For these type of applications you may want to experiment with enabling Forced parameterization to see if it has a positive effect on the workload by reducing query compilations.

Running the following query in Simple parameterization produces the following execution plan where the WHERE clause is not parameterized:

unning the following query in Simple parameterization produces the following execution plan

After enabling Forced parameterization and running the same query we get the following execution plan where the execution plan is parameterized:

enabling Forced parameterization

Keep in mind that in some cases when the data in a table is highly skewed, Forced parameterization may cause a suboptimal plan to be reused, thus degrading performance.

Enabling Parameterization

To determine the current setting of parameterization you can run the following query:

SELECT name, is_parameterization_forced FROM sys.databases
  • 1 indicates Forced
  • 0 indicates Simple

To enable Parameterization you can use the following ALTER DATABASE statements:



...Or you can use SSMS.  Right click on the database then go to Properties, Options, Parameterization as shown below:

you can use SSMS. Right click on the database, Properties, Options, Parameterization

Using Plan Guides

After testing your workload you may find that Forced parameterization seems to work better for your SQL Server, but you notice a few statements that aren't performing well that were optimized before you made the parameterization change. In this case, you could use a plan guide to change such statements to use Simple parameterization. The following is an example of where a query will perform better using Simple parameterization:

EXEC sp_create_plan_guide
  @stmt=N'SELECT * FROM AdventureWorks2012.Sales.CreditCard WHERE CreditCardID = @CID', 
  @type = N'TEMPLATE', 
  @module_or_batch = NULL, 
  @params = N'@CID int', 

To learn more about how to use a SQL Server Plan Guide visit Armando Prato's tip here.

Next Steps
  • Setting parameterization to FORCED flushes all query plan from cache except for those that are currently running, compiling, or recompiling.
  • Setting the parameterization option will not lock the database and doesn't require restarting the SQL service.
  • When restoring a database, the parameterization option is preserved.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Sunday, July 15, 2018 - 1:38:57 AM - Abrar Uddin Back To Top (76644)


i just wanna make sure that how to fix an issue of  Min and Max memory of sql server. i have an issue related to SQl Server memory, my memory is being utilized to its defined value. 95% of my memory is used. how to get out of this issue. please, help me.

Tuesday, November 28, 2017 - 5:53:07 AM - Juergen Back To Top (73325)

Hi Brady, we are in trouble with a partial parameterized query we cannot change (3rd party). The query is executed with some (3) parameters and some literals that change with every execution - so cached plan is not reused. Is there a way I can produce a fully parameterized version with sp_get_query_template for the partial parameterized query that can be used to create a TEMPLATE plan guide with sp_create_plan_guide.

Note that (with profiler) the query is already executed with "exec sp_prepexec"?

If I try to create a query template an dplan guide for a non-parameterized version this works but the plan is not used if the application executes the query - so it seems the problem is the difference in the query itself.

Thank you, Juergen

Friday, May 10, 2013 - 10:22:19 AM - Jean Back To Top (23874)

Newbie question - Does this deal effectively with the parameter sniffing issue ?

get free sql tips
agree to terms