SQL SERVER – 2005 – Forced Parameterization and Simple Parameterization – T-SQL and SSMS

SQL Server compiles query and saves the procedures cache plans in the database. When the same query is called it uses compiled execution plan which improves the performance by saving compilation time. Queries which are parametrized requires less recompilation and dynamically built queries needs compilations and recompilation very frequently. Forced parameterization may improve the performance of certain databases by reducing the frequency of query compilations and recompilations. Database which has high volumes of the queries can be most benefited from this feature.

When the PARAMETERIZATION option is set to FORCED, any literal value that appears in a SELECT, INSERT, UPDATE or DELETE statement is converted to a parameter during query compilation. When the PARAMETERIZATION database option is SET to SIMPLE, the SQL Server query optimizer may choose to parametrize the queries.

Using T-SQL:
-- Change database Parameterization to Forced
ALTER DATABASE AdventureWorks
SET PARAMETERIZATION FORCED
GO
-- Change database Parameterization to Simple
ALTER DATABASE AdventureWorks
SET PARAMETERIZATION Simple
GO

Using SQL Server Management Studio:
Right Click on Database >> Click On Property >> Click on Options >> Change Paramterization Attribute to either SIMPLE or FORCED.
SQL SERVER - 2005 - Forced Parameterization and Simple Parameterization - T-SQL and SSMS sqlprop

This article is just an introduction. There are lots of things to read. I recommend to read references listed below.

Reference : Pinal Dave (https://blog.sqlauthority.com), BOL – Forced Paramterization

,
Previous Post
SQL SERVER – Simple Example of WHILE Loop With CONTINUE and BREAK Keywords
Next Post
SQL SERVER – Deterministic Functions and Nondeterministic Functions

Related Posts

4 Comments. Leave new

  • When setting parametrization to FORCED, good thing is to put on every query (OPTION RECOMPILE) because, if we stored first query plan that is not optimal, every other request will use this bad plan, and we can get bad performance :)

    Reply
  • Amel – No, don’t apply (OPTION RECOMPILE). The TechNet article at https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms175037(v=sql.105) states quite clearly that queries with a RECOMPILE hint are not eligible for forced parameterisation.

    I guess you’re trying to get the best ‘first reusable plan’ to be created once the forced parameterisation kicks in. If so, relax. That same TechNet article says that “…Setting the PARAMETERIZATION option to FORCED flushes all query plans from the plan cache of a database…”

    Reply
  • in forced parameterazation how select ,update, delete how converted to parameters?

    Reply
  • If you set this option to on all the plans will flush becasue of this setting .It is next time that the new plans will be used .After that , yes there is a probability that for a long time the same plan is used which might not be the best in future until its in the cache as per page life expectancy..

    Reply

Leave a Reply

Menu