Disable a bad SQL Server Query Plan vs. Force a good plan

Posted on

Question :

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.

Answer :

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.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *