I never had the need to turn on the PARAMETERIZATION FORCED database option until this week. We pretty much use only stored procedures for our internal applications so the execution plans are almost always in cache and reused. This practice of using parameterized stored procedure calls, together with attention to detail in query and index tuning, allows us to comfortably handle several thousand requests per second on commodity hardware without taking special measures.
The Perfect Storm
We acquired a third-party application which had to sustain thousands of batch requests per second in order to keep up with our peak demand. Our first attempt to use the application out-of-the box failed miserably when the 16-core database server quickly hit 100% CPU and stayed there. An examination of the most frequently run query soon revealed why CPU was so high. Not only was the moderately complex query not parameterized, each invocation required a full table scan. The schema (EAV model, missing primary keys and indexes), application code (ad-hoc, non-parameterized queries) and inattention to indexing seemed the perfect storm to guarantee failure.
Our hands were tied in what the vendor could/would do to address our performance concerns. We worked with the vendor to optimize indexes and this brought the CPU down to about 65% but the batch requests/sec rate and slow response time was still unacceptable. We needed to increase performance by at least an order of magnitude to meet SLAs.
The Perfect Fix
I then recalled an experience that SQL Server MVP Adam Machanic shared not long ago:
CPU was 95%+ at peak time (several thousand batch requests/second, via an ASP (classic) front end), and the peak time lasted 8+ hours every day. The server was one of the big HP boxes -- not sure if it was a Superdome or some other model -- with something like 56 cores and 384 GB of RAM. The database itself was only 40 or 50 GB, as I recall, so the entire thing was cached. Long story short, I logged in during peak load, did a quick trace and noticed right away that none of the queries were parameterized. I decided to throw caution to the wind and just go for it. Flipped the thing into Forced Parameterization mode and held my breath as I watched the CPU counters *instantly* drop to 7% and stay there. I thought I'd broken the thing, but after checking my trace queries were running through the system same as before, and with the same number of errors (another story entirely <g>). Luckily the head IT guy happened to be watching his dashboard right as I made the change, and after seeing such an extreme result thought I was a god...
I knew of PARAMETERIZATION FORCED but never realized how big a difference the option could make until I learned of Adam's experience. I'm not quite as adventuresome as he is so I restored the production database to a separate environment for some cursory testing. To my amazement, I watched the rate of my single-threaded test jump from a few dozen batch requests/sec to several hundred immediately after I executed "ALTER DATABASE...SET PARAMETERIZATION FORCED". CPU dropped by half even with the tenfold increase in throughput.
The production improvement was even more impressive - the 16 core Dell R900 hasn't exceeded 8% CPU since the change. Response time is excellent, we have happy users and plenty of CPU headroom to spare.
A Turbo Button?
Despite anecdotal success with PARAMETERIZATION FORCED, I wouldn't turn it on indiscriminately. When the PARAMETERIZATION FORCED database option is on, all queries are parameterized, including complex ones. This is good in that compilation costs are avoided due to cache hits. The bad news is that a single plan might not be appropriate for all possible values of a given query. Worse overall performance will result when higher execution costs (due to sub-optimal plans) exceed compilation savings so you should understand the query mix before considering the option.
In contrast, SQL Server parameterizes only relatively simple "no brainer" queries in the default PARAMETERIZATION SIMPLE mode. This behavior promotes reuse of plans for queries that will yield the same plan anyway regardless of the literal values in the query. Complex queries are not parameterized automatically so that the optimizer can generate the optimal plan for the values of the current query in the event of a cache miss. The downside with simple parameterization, as Adam and I observed, is that complex queries not already in cache will incur costly compilation costs that are a CPU hog in a high-volume OLTP workload.
There is also middle ground between PARAMETERIZATION SIMPLE and PARAMETERIZATION FORCED. One can use plans guides with PARAMETERIZATION SIMPLE to avoid compilation for selected queries while other complex queries are compiled as normal. In my case, a plan guide may have been a better option because the culprit was a single query rather than many different unpredictable ones.
In my opinion, the best solution is to use stored procedures and/or parameterized queries in the first place. These methods provide the performance benefits of PARAMETERIZATION FORCED and add other security and application development benefits. Unfortunately, third-party vendors are notorious for not following parameterization Best Practices so DBAs need to keep PARAMETERIZATION FORCED and plan guides in their tool belt.