Forced Parameterization for Queries in a Database

Introduced in SQL 2005, setting the parameterization mode to forced takes all plans that contain literals (firstname='andy') and changes it to a parameter (firstname = @firstname). The impact on performance can often be dramatic as the number of plans and compilations drops sharply due to the plan reuse that happens with parameterized plans. It's not always the right thing to do, but it's easy to use and requires no code changes.

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

C5E6132634 on 7/28/2010

It would have been better if he told us what to look for in perfmon - which measures should show an improvement if this was a good thing to do?

Amit on 7/28/2010

I am not able to view the video and Its saying you need to flash air installed.Is there any way to view these videos?

Stephen on 7/28/2010

Very nice with a lot of potential. Wish he would have spent moment to described when both types of parameterizing works best and to be avoided.

Kristeen Sellers on 7/28/2010

Not sure how applicable this will be for me, but definately can see it's uses.

Mark Hathaway on 7/28/2010

The video is very fuzzy.

Catto on 7/28/2010

Good Vid

oliver on 7/28/2010

Crystal clear, good work.

Denise Tsubota on 7/28/2010

very helpful and informational. was not aware of this option being added in 2005, we currently run stuff out of 2000, 2005 and 2008, so its nice to know about these tools.

Michelle Poolet on 7/29/2010

An incredibly powerful addition to the DBA toolkit!

Charlie Bruno on 7/31/2010

learned something new....very good, excellent presentation.

stan on 8/10/2010

This topic sheds some light on the workings of SQL server and also on caching of objects. Thanks.

Ron on 8/18/2010

Good topic.

Alec Lewis on 3/31/2012

Very nice demonstration and lesson. Great stuff.