Query Store Improvements I’d Like To See For SQL Server: Recent Actual Plans

Posted on by Erik Darling

Spirited Away

This week’s posts are a series of short, and, admittedly lazy ones about improvements I’d like to see in Query Store.

I’m away from home for the Holiday Week, and I don’t have a lot of time to write at the moment. Normally I’d put these all into one post. I do hope you, dear reader, will forgive me. In the spirit of the holidays, or something.

Problem One

Query Store plans are equivalent to cached plans, which are equivalent to estimated plans. You get what SQL Server thought was gonna happen.

Sometimes, that can have very little in common with reality.

Other times, it can be misleading because of parameter sniffing.

Right now, SQL Server has a plan cache view in SQL Server 2019+ that holds the last actual plan. It would be really cool if Query Store tracked a configurable number of recent actual plans.

That’d really help folks out where the performance problem isn’t easy to deduce or reproduce from what they’ve got available.

Actual plans are incredibly valuable these days, with operator times included, and a whole bunch of other stuff that isn’t surfaced in estimated plans, like spills.

In real life, I see all sorts of weird things popping up as the bottleneck in query plans:

  • TOP above a SCAN
  • Repartition Streams
  • Compute Scalar

You only see the pain when operator times are involved, and Query Store needs to start collecting those to make query tuning easier.

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.

Related Posts

2 thoughts on “Query Store Improvements I’d Like To See For SQL Server: Recent Actual Plans”

  1. I’d like to see a 3rd button in addition to “Force Plan” and “Unforce Plan” for “Drop Plan.” I’ve yet to figure out a way to drop a stuck bad plan that doesn’t involve pen and paper because the planID is buried in a non-copy-capable location like a context menu or middle tool bar. I’d also like to see some mechanism to better handle “death plans.” These are plans so abhorrent that they are essentially eternal and trigger an app timeout. Since app timeouts are seen as cancellations, the server thinks its plan is super awesome and keeps trying to use it in a loop of timeouts until the plan is manually dropped. It would be nice to tell the optimizer to treat cancellations as prohibitively expensive plans.

    1. Yeah, that’d be nice to have, too. There are so many things like that, which I’m surprised Query Store just doesn’t have. It’s like every time I go to use it, I find it coming up short.

      Thanks!

Comments are closed.