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.

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