Used To Love Her
I used to think the plan cache was so cool.
- You can find queries that aren’t good there
- Plans are full of details (and XML)
- Supporting DMVs give you extra insights about resource usage
But most of the time now, I’m totally frustrated with it.
It clears out a lot, plans aren’t there for some queries, and the plans that are there can be very misleading.
Can you really tell someone what their worst performing queries are when everything in there is from the last 15 minutes?
No.
Why I Like Query Store Better
Plans end up there for more definite amounts of time, even if you stick a recompile hint on them.
Plus, you get a whole lot more information about what went on with all the different query metrics. As far as a source of “what happened and when”, it’s much more reliable. It’s almost like having a monitoring tool in SQL Server.
Do you trust your plan cache to tell you what was slow more than a couple days ago?
One really big bonus is that it’s portable, and it’s per-database. You can backup or clone a database so someone else can easily look at it somewhere else.
On top of that, it’s more usable: there are built in reports, a graphical interface, settings you can tweak, and plans you can force with a couple clicks.
You could solve a lot of frustrations by moving things here.
Microsoft Has Work To Do, Though
There have been a number of issues, and unanswered user requests for Query Store over the years, and not all of the associated DMVs have full parity.
Querying the data also needs to be made less prone to performance issues. And, living in the future, it’s a much more sensible location for the last known actual plan.
A nice to have would be predicate normalization for identical and morally equivalent queries, with totals and averages for resource usage, so you could group things together. Especially for morally equivalent queries, this would allow plan forcing where the queries get different ids.
Some monitoring tools have this in their interface, and the number of times I’ve been able to track down outliers within a group of executions to solve a problem is, well, at least memorable to me.
I wish more people had monitoring tools.
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.
One thought on “Why Microsoft Should Get Rid Of SQL Server’s Plan Cache And Use Query Store Instead”
Comments are closed.