Forced Parameterization: A Turbo Button?

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.

 

posted @ Saturday, June 27, 2009 4:47 PM

Print

Comments on this entry:

# re: Forced Parameterization: A Turbo Button?

Left by Ian Wilson at 6/28/2009 10:01 AM
Gravatar
Well, I have just done some quick testing and this does look like an amazing tweak to improve DB access. Obviously needs a lot of testing, but it looks promising.

# re: Forced Parameterization: A Turbo Button?

Left by SReddy at 6/29/2009 7:03 AM
Gravatar
I was seeing a non-responsive production server follwed by stack dumps and contacted PSS - they noticed that we had a very large ad hoc query load and suggested we implement forced parameterization - while not as dramatic as going from 95% CPU utilization to 7%, the cpu load went down by about 25% almost instantly and helped us remain on the existing server for several months before jumping to a 64-bit machine with SAN disks - all the tuning we had done in the past seemed inconsequential compared to the jump in performance we saw at that moment. Performance improvement was almost 100% and all issues seemed resolved - at least for now.

# re: Forced Parameterization: A Turbo Button?

Left by TheSQLGuru at 7/5/2009 9:37 AM
Gravatar
I had a client with horrible looping ADOc code. I was able to improve the throughput of their most intensive processes by 35% with forced parameterization. Good stuff when you need it!

# re: Forced Parameterization: A Turbo Button?

Left by Cookie at 6/23/2010 1:35 AM
Gravatar
Hi!

Can this help to run more queries simultaneous? I mean on servers with down CPU consume

# re: Forced Parameterization: A Turbo Button?

Left by guzmanda at 7/11/2010 11:16 AM
Gravatar
Forced parameterization will reduce schema locks as a result of query compilation. The parameterization setting will allow you to run more concurrent queries if you have short-term blocking due to query compilation, even though you might not have a CPU bottleneck.

# re: Forced Parameterization: A Turbo Button?

Left by mauro at 7/13/2012 2:45 PM
Gravatar
what would happen, once the force option is set, with a new index creation or statistics changes??? is the plan regenerated with those events? or will remain the same until I empty the proc cache?
Comments have been closed on this topic.