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.

 

image

 

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

image

 

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!

image

 

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 (6)

  1. Satya Vijjana says:

    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?

  2. SATYA_DBA says:

    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?

  3. mark says:

    Ad Hoc queries *don't* cause this. Changing Ad Hoc queries cause this. There is no trouble with executing Ad Hoc queries with parameters.

  4. Thierry says:

    I would have thought changing "Optimize for Ad-Hoc Workloads" to true would help alleviate this problem?

  5. Kevin Boles says:

    I would have expected Optimize for Ad Hoc Workloads to make things worse, not better. It would allow for even MORE plans to be in the same amount of memory, causing a larger amount of work to be done a) for hashing and b) to check the larger plan for hash values.

    What I want to know is if this was a pure ad hoc workload or was there some amount of sproc calls and what was the ratio? Also, how complex where the main ad hocs?

  6. Jacob says:

    @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.

Skip to main content