Shredded Cheese
I often speak with people who are confused about what these settings do, and which one they should be using to fix certain problems.
The first myth is that Optimize For Ad Hoc Workloads has some special effect on queries run outside of stored procedures: it does not. It’s very poorly named in that regard. There are no special optimizations applied because of that setting.
If you hit up the documentation, there’s no mention of it, but because it’s named how it is, people think YEAH IT’LL MAKE MY AD HOC QUERIES FASTER!
But no. It does not.
Add Chalk
Optimize For Ad Hoc Workloads mainly helps servers where the plan cache is unstable because it gets full of plans that don’t ever get reused. You end up with a lot of churn.
Why don’t they get reused? There are a lot of reasons, but often it’s because queries either aren’t parameterized, or because parameters aren’t explicitly defined in the application.
When you turn Optimize For Ad Hoc Workloads on, first-time plans are stored as stubs. That’s all. If they get used a second time, the full plan is stored.
This is great, unless all your plans have a low rate of reuse anyway, like < 10 or so.
Paramesan Cheese
First, the documentation for Forced Parameterization is hard to find.
Second, it’s usually a *wonderful* setting for queries that are fully unparameterized. One of the limitations is that if a query is only partially parameterized, it won’t parameterize the unparameterized bits.
That kinda sucks, but I understand why it doesn’t: Microsoft thinks you’re smart and you know what you’re doing, and there must be a *very good reason* for you to only have partially parameterized a query.
For instance, to get a filtered index used, or to avoid some parameter sniffing issue with skewed data.
Which One Do You Need?
Focus on the problem you’re trying to solve.
- If you have a lot of single use plans clogging up your plan cache and forcing a lot of churn, then Optimize For Ad Hoc Workloads can be great
- If you have a lot of unparameterized queries creating loads of duplicate plans (maybe even single use), you want Forced Parameterization
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
Thank you for sharing this, Erik.
Could you please specify which sp_blitz* checks may be relevant to these two scenarios?
There’s a couple checks in Blitz that will warn about plan cache stuff. The one I see most often is queries with multiple plans.
The majority of checks for this are gonna be in BlitzCache, under the “rollup” section at the bottom that has all the warnings and explanations.
Thanks!