Forced parameterization to the rescue

Some of the features have been around for a long time.  But we keep seeing users not taking advantage of it.   I wanted to give you an example how forced parameterization can help you.

Recently I worked with a customer with a very active system serving many concurrent users.  Here is some basic information:

1. CPU: 160 logical CPU (80 cores with hyper-threading enabled)
2. RAM: 2TB RAM
3. Active users: about 1400
4. Batch requests/sec:  averaging 4000 or above

This is very mission critical system.  When their users reached max of 1400 and CPU reached above 70-80%, their application started to slow down.    With high CPU, the usual troubleshooting is the tune heavy hitter queries.  But SQL Nexus & RML report showed that there wasn’t predominant set of queries to tune.  The screenshot bellowed showed that top 10 queries accumulatively accounted for less than 20% of total CPU consumed.   This made it hard to focus and tune individual queries.

 

 

We noticed that the compilation was fairly high as shown in the screenshot below.  SQL Compilation/sec averaged 730.

 

With compilation being this high, it usually was because ad hoc queries were used at high rate.  To prove this, we pulled out “SQL Plan” out of “Cache Object Counts”.   It was almost over 160,000 (see screenshot below)!   This counter meant that there were almost 160,000 ad hoc plans in the plan cache!

 

Solution

Many times, ad hoc queries at high rate can cause issues such as wasting CPU to compile and wasting plan cache memory.   We had this customer enable “Forced Parameterization” for the database.  After that, the CPU dropped to 10-20% even with highest user load and performance became super fast.

Sometimes, a solution may be simpler than you might have thought.  Just keep this option handy.  If things don’t work out, it’s easy to back it out.  Over the course of troubleshooting performance issues, I have used this trick many times.  I hope this serve as a reminder for you.

 

Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support

twitter| pssdiag |Sql Nexus

Comments

• Anonymous
  April 23, 2015
  Thanks for the insight, I believe 2014 is intelligent enough to deal with forced parameterization. Could you please tell us it applies to which SQL version?

• Anonymous
  April 23, 2015
  Thanks for the insight, I believe 2014 is intelligent enough to deal with forced parameterization. Could you please tell us it applies to which SQL version?

• Anonymous
  April 23, 2015
  Ad Hoc queries don't cause this. Changing Ad Hoc queries cause this. There is no trouble with executing Ad Hoc queries with parameters.

• Anonymous
  April 26, 2015
  I would have thought changing "Optimize for Ad-Hoc Workloads" to true would help alleviate this problem?

• Anonymous
  April 26, 2015
  The comment has been removed

• Anonymous
  April 26, 2015
  @Thierry: Optimize for Ad-Hoc Workloads won't really affect the CPU cost of compiling queries. That setting helps save space in the plan cache by only storing a stub instead of a full plan the first time a query is compiled. The CPU cost of compiling the query is still there, though, and that's something that forced parameterization can help with in some situations, as in the article.