How I Learned To Hate SQL Server’s Plan Cache

Used To Love Her

---

I used to get so excited about looking in the plan cache, and writing all sorts wacky XML queries to parse plans and dig for goodies.

Then I started to ask some tough questions about it, like:

• How many plans are in here?
• What’s the oldest one?
• How many duplicate plans are there?
• Where’s the parameter sniffing details?
• Why is optimize for adhoc workloads the worst setting that everyone said is a best practice for eons?

As I wrote queries to look at a lot of these things, stuff got… weird. And disappointing.

What’s In There?

---

The plan cache has limits for how many plans it’ll keep, and how big of a cache it’ll keep. Even if there’s a lot of plans, there’s no guarantee that they’re older than a few hours.

You may even find that simple parameterization makes things confusing, and that things get cleared out at inopportune times.

One situation I’ve run into well-more than once is the plan cache getting cleared out due to query memory pressure, and then any chance of figuring out which queries were responsible disappears along with it.

Memory is important, someone once said.

On top of that, a lot of SQL Server consumers have way too much faith in bunk metrics, like query and operator costs. Sure, there are plenty of corroborating views to get resource usage metrics, but if all you’ve got is a query plan, all you’ve got is a bunch of nonsense costs to tweedle yourself with.

Sniff Sniff Pass

---

Another big miss with the plan cache is that it is almost no help whatsoever with parameter sniffing.

Sure, you can sort of figure out based on wide variances in various metrics if a plan sometimes does way more work or runs way longer than other times, but… You still just see the one plan, and its compile values. You don’t see what the plan was, or could be.

Or should be, but that’s a story for another day.

This is where Query Store is awesome, of course! You can see regressions and all that jazz.

But in the plan cache, boy howdy, you get a whole lotta nothing. No Rosie at all.

And this is why I hate the plan cache.

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.