Transcript

Here’s a final recap of my favorite of the solutions that I showed you.

The two things that I think are really the most practical to use in the real world are, if you have Query Store and you want to intervene for a short time, maybe it’s forcing a plan that’s parallel or maybe it’s just forcing a plan that’s faster.

Using Query Store for that short temporary, please use this plan to stabilize the situation can be very, very helpful and is much easier than the other potential solutions. You do have to make sure that the plan that you force, really is fast enough, consistently when run with different parameters over time.

I do think it’s a good thing that when we force plan there is some wiggle rooms but, monitor carefully to make sure that you’re getting what you actually want.

If we don’t have SQL Server 2016, in practice I’ve found that creating the plan guide from the handle is the easiest of the other options, because if we identify the plan handle we want to freeze, then doing that process of saying okay freeze this guy is pretty straight forward once we found that plan handle. We don’t have to manually escape out, single quotation marks or use a hint of an execution plan that’s a mile long and our script and may end up with a weird typo in it. So these are the two things that I think do practically make the most sense.

One disclaimer on the way that I created that plan guide from the handle I did use an unsupported trace flag to get the plan into cache. I would put that, you know if I was doing a change request for this I would put that, at that step. I would very clearly state okay, part of our process, we need to use an unsupported trace flag for our session to generate this parallel plan, if we didn’t have another good way to get it into cache, right.

Not everybody would approve that change. And in that case, we might say okay, if we can’t fix it this way, if we can’t stabilize it this way, we need to look at finding another solution and maybe we do need to lower our cost threshold or maybe we do need to change indexes around to make the query faster if this is a critical query.

Getting used to these morally equivalent plans…

…And we could see the morally equivalent plan when freezing with Query Store, or sorry, when forcing with Query Store. The term with Query Store is forcing. The term with Plan Guides is freezing. We also saw them when using just a used plan query hint. So once we force a plan there may be a little bit of shifting in the plan. Same general plan shape, but SQL Server does have an optimization path that does go around. It does have some wiggle room. In these cases where we were forcing a plan and things were a little different we did always still have use plan = true in the properties of the plan.

So that’s one thing you can look at and say okay, did this plan happen, quote unquote, naturally or was it coerced. With plan guide to add hints, with plan guides to freeze plans, we can also see the plan guide name and data base in the properties of that plan. I don’t see forcing plans as a permanent solution.

And that’s because if I’ve had a plan forced and I upgrade my SQL Server, how do I know that the optimizer couldn’t do better than my forced plan? To test, I would have to un-force it. Similarly, because of data changes, there may become a better option available.

Forcing plans like we’ve gone through today, they can be good temporary fixes to stabilize, but long term, what are my better options for making this query faster other than forcing the plan. I do have some good options, they just may be options that take more testing and a little more release time than just temporarily forcing a plan.

Thanks for taking the SQLChallenge

I really love making these. I hope that you enjoy taking them. And I hope to see you for a SQLChallenge soon. I’m Kendra Little from SQLWorkbooks.com.