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:

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.