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:
- CPU: 160 logical CPU (80 cores with hyper-threading enabled)
- RAM: 2TB RAM
- Active users: about 1400
- 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