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 (http://blog.SQLAuthority.com), BOL – Forced Paramterization

About these ads

4 thoughts on “SQL SERVER – 2005 – Forced Parameterization and Simple Parameterization – T-SQL and SSMS

  1. 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 :)

  2. Amel – No, don’t apply (OPTION RECOMPILE). The TechNet article at http://technet.microsoft.com/en-us/library/ms175037.aspx 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…”

  3. 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..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s