WARNING: I do NOT currently recommend enabling the Automatic Plan Correction feature due to issues described in my post, Automatic Plan Correction Could be a Great Auto Tuning Feature for SQL Server. Here’s why it Isn’t.

Transcript

I’m jumping in with some extra content for the recorded version of this webcast.

This wasn’t part of the webcast, but after the webcast, I was doing some experimenting and thinking. I think this is important to cover!

We are going to talk about and do some demos of how recompile hints get handled by Automatic Plan Correction

… and also how you are still able to manually force a plan in Query Store if you want to, if it has a recompile hint– and why those are different.

So first up… a spoiler: in automatic tuning, the Automatic Plan Correction feature is designed to ignore queries that have recompile hints

Now, I was doing some extra testing, and I was like, “Okay, every time I use a recompile hint, I can’t get Automatic Plan Correction to kick in,” but of course that doesn’t mean it could never kick in.

So I asked for some help.

I’m very thankful to Jovan Popovic of Microsoft, who explained the logic of this to me

Jovan explained that automatic plan correction is designed to ignore queries with recompile hints, because when we add a recompile hint what we’re saying is: “I don’t want to use a cached plan, or reuse a plan for this query. I want a fresh, new plan.”

If automatic tuning kicked in and said, “Oh, I’m not going to give you a fresh plan, I’m going to force you to use a plan that I already have around,” that would be changing the expected behavior of ‘recompile’. That would be violating the essence of the recompile code.

So, the Automatic Plan Correction feature isn’t going to jump in and auto-tune queries with recompile hints, by design.

However, we as individuals, using just Query Store – Not the Automatic Plan Correction feature, but just using Query Store – we can manually force a plan for a query that has recompile in it

That is allowed because we are demanding that behavior. We are the owners of the code, and just like taking the recompile hint out, we are allowed to force it manually. It’s just not going to do it for us. Let’s dive into a demo and take a look at this in action.