In SQL Server 2005 and later, there is a database option called “forced parameterization”. When it is turned on, it overrides the default “simple parameterization” normally used by SQL Server. It does this by forcing most SELECT, INSERT, UPDATE, and DELETE statements to be parameterized, potentially boosting performance of databases by reducing the number of query compiles and recompiles that need to occur.
As with many “options” in SQL Server, whether this option should be turned on for a particular database is not a simple decision. So I was curious to see how many DBAs actually employ this option. I ran the above poll on my website, and had two surprises. The first surprise is that about 19% of responders said they use it, which I thought was high. The second surprise is that about 44% of responders have not even heard of this option.
If you are one of the 44% who aren’t familiar with forced parameterization, I suggest you read the following to learn more about it. While this database option can be very handy in specific cases, it can also hurt performance in other databases. If you try this option, be sure to perform a benchmark before implementing it, then another benchmark after implementing it, and seeing if you get the behavior you expect. Of course, perform this experiment in a test environment, not in production.
http://technet.microsoft.com/en-us/library/ms175037.aspx
