3

I like to leave query planning up to the database as much as possible, and only force when absolutely necessary. I'll usually do this in SSMS with the 'Force plan' option.

So below, my purple query went rogue today and timed out 10 times in a row, but the black one seems to be working great. So I can Force the black query.

But what about the turquoise plan vs. the black? They're about the same.

enter image description here

I've been wondering for a while why there doesn't seem to be any option to 'Disable' a query plan from running. I know I can delete a plan from the cache, but there's nothing stopping it recreating it right?

Especially times when I don't have the resources for a full investigation I'd much rather say 'skip this plan' - but not have to commit to forcing a specific plan. I can find no such feature.

I can't even find a discussion of why this doesn't seem to exist. I was expecting 'Yes a lot of people ask this, and generally it's better to...' - but nothing.

I don't understand also why it picked a timeout plan 10 times in a row (the last purple square) and didn't even attempt the other plans once. But that's probably a different question. Also if you're curious - the above query is to get order history for a specific SKU - so the corresponding number of source rows may vary greatly for each SKU.

1

I'll play the guessing game.

It's expensive

When we force a plan, we're essentially mapping a given query text to a known plan - that is easy to do. Every time query x is run, use plan y.

If we instead blacklisted one or more plans, the optimizer now has to generate a plan and iterate through a list of blacklisted plans to see if any match (or something similar). And what is the matching criteria - do the variables and row estimates have to be the exact same, or just the "shape" of the plan? In addition to requiring caching space for all these potential blacklist plans, if the newly created plan matches a blacklisted plan, what would happen?

Try to generate a new one again? Fall back to the "next best" (so now we're saving multiple plan copies for potential use...)? Something else? What if we've accidentally blacklisted all the valid plans to get the data?

The more you go through this thought exercise, the more intricacies come about, and it quickly becomes clear that this isn't a good idea. Keeping the optimizer from landing on a specific plan would be costly and incredibly complex in addition to probably not working well.

  • Good news is my boss is happy with 'black circle' plan for now, but I still think it's a shame. I think your analysis is probably right though, however it doesn't fully make sense. The existing mapping from query text hash to the 'current best plan' - wouldn't need to change (and the query wouldn't take longer). My 'new logic' to filter out bad plans would only need to run whenever the database is choosing a new plan (which it did this morning) - not on every execution. The time to filter out plans at that time would be negligible. A 30 second timeout is a lot more expensive! – Simon_Weaver Mar 6 '19 at 20:43
1

The current ways (I'm oversimplifying) to affect plan shape are hinting, which usually removes options from the optimizer, and forcing a particular plan, whether with USE PLAN, plan guide, or Query Store.

Adding hints can prevent the plan you don't want, but will likely prevent other options as well. Forcing a plan has the same issue of course - it prevents ALL other options.

Your best practical option here is probably to force a plan that works well, then continue to monitor it.

If you really want to keep both options available, then you may want to apply hints with a plan guide.

  • Hmm I always thought a plan hint in the SQL was the same as if I picked one in SSMS. I'll have to reread over that. Unfortunately I have a lot of ancient Linq2SQL code which doesn't support query plan hints - and a bigger problem that is generates some pretty horrendous nested queries. Looking at a nice graph in SSMS and clicking a color shape is much more satisfying anyway! Fortunately in this case the chosen plan seems to be working much better - but I still find the whole situation 'a bit odd' that there isn't an option to 'exclude'. – Simon_Weaver Mar 6 '19 at 20:49
  • 1
    @Simon_Weaver LINQ is a four-letter word to DBAs...I share your pain. Did you know that plan guides allow you to apply query hints to LINQ queries? – Forrest Mar 6 '19 at 20:52
  • I'll have to reread some of that stuff. Anything new is with the newest version of EFCore which is much better behaved so I'm seeing less need for tricks there:-) And don't get me started on Linq2SQL with filtered indexes :-( Thanks for your help – Simon_Weaver Mar 6 '19 at 21:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.