Database Settings : Forced Parameterization

I originally looked at Forced Parameterization here.

By default Parameterization is SIMPLE, it can be set to FORCED however (in sql 2005+).

My reason for this is that developing stored procedures apparently isn't feasible for our current project. The web developers don't have the skills to maintain them and don't want to learn them. The 'increase in development time' is apparently not worth it. Similarly there is no love for table functions or even for sargable sql. Sql is (and will remain) embedded in the web application :(

With the speed at which decisions are made and the product changes direction I am reluctantly letting it go. I don't agree because (as a dba) I'd like to encourage every opportunity to optimise the database environment. Balancing my DBA role, SSIS development tasks and aspirations to persue the BI track, I have enough to think of.

Rant aside, Forced Parameterization will allow the creation of less query plans by parameterizing values within submitted sql queries. Later queries will be compared in their parameterized form against those in the plan cache.

For example;

With Parameterization = SIMPLE, the following plans could all exist >

SELECT Forename FROM Person.Contact WHERE ID = 1

SELECT Forename FROM Person.Contact WHERE ID = 3

SELECT Forename FROM Person.Contact WHERE ID = 6

SELECT Forename FROM Person.Contact WHERE ID = 13

With Parameterization = FORCED, only the 1 plan would be stored >

SELECT Forename FROM Person.Contact WHERE ID = (@P1)

I covered Viewing and clearing the plan cache here. It's a useful post for debugging query caching.

Setting Parameterization to FORCED >

ALTER DATABASE AdventureWorks SET PARAMETERIZATION FORCED

Returning Parameterization to SIMPLE >

ALTER DATABASE AdventureWorks SET PARAMETERIZATION SIMPLE

Setting all dbs on a server to FORCED parameterization >

exec sp_msforeachdb @command1= 'ALTER DATABASE ? SET PARAMETERIZATION FORCED

Links :
SQL Authority
How SQL Server 2005 "Forced Parameterization" cut ad-hoc query CPU usage by 85%
Strictly Software : Optimizing a query with Forced Parameterization