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

All right, back to our demo.

Now that the query’s being forced, can I tell, if I’m looking at the query plan?

Here is the version of the query that compiles the slow plan.

I’m going to first run the estimated plan. I did control+L, the same thing as this button up here. Even though I know that auto tuning is on for this and that it’s saying, “No, I want you to use the last good plan,”

When I do estimated plan I still see, hey, window spool! This is still slow plan.

So control L, I’m saying: Show me how you would optimize this. Don’t look at stuff in the execution plan cache, give me a fresh optimization. And it’s saying, “I would optimize this as slow plan.”

But if I turn on actual execution plans and then actually execute it, and I’m saying, Don’t go off the plan cache. Exec with recompile.

Hey, that only took three seconds. I think I got a fast plan, right? Well how can I tell?

Looking at my execution plan, oh sure enough, I did get fast plan

Here’s my nice window aggregate operator and it’s in batch mode. Yeah, this is fast plan!

Well, how I can tell that this was guided by that, well, not guided, but FORCED? If I look at the properties of the node farthest to the left, which in this case is a SELECT INTO. I’m using SELECT INTO inside the procedure so that if I run it 75 times in a row it doesn’t spew 75 result sets into Management Studio, by the way. Use plan = true. When you see use plan in your plan properties, that means, “Hey, somebody told me that I needed to use this plan.”

And in this case, what was doing that is the automatic plan correction feature.

Let’s go back and let’s refresh our report here

Now notice that we have a new dot who showed up! we have plan_id 15.

This might look curious to you, it certainly looked curious to me the first time I saw it, because I’ve got a check mark over here on plan_id 5, saying, “Hey, I’m forcing this guy.” But now I’ve got plan_id 15, a different plan_id, and it doesn’t have a check mark.

When you’re first looking at this, it kind of looks like it didn’t force it. This is a different plan. But it has the same shape. Let’s compare those two plans. Plan_id 15, the one I just generated, here we have it. It even says here– let’s get rid of that tool tip. Notice here it says, “not forced.” Well, let’s look at the properties. We can look at properties here just like we did before.

Notice that when I do look in the properties, it says: use plan = true. This is the same plan that had ‘use plan’.

What are the differences between this and plan_id 5?

Let’s look at the estimates and the cost. On my SELECT INTO here, if I look at my estimated subtree cost, it’s 574. Right, that’s a chunk of data. And if I look at in my parameter list, my threshold, this was compiled for threshold = null.

Looking at the one who’s back here– the one that has the check mark– what parameter list was it compiled for? It was compiled for the old fast plan. It was compiled for a different value: 500,000 for the parameter value. Notice that the lines, these estimates are different.

That plan estimated that only one row was going to come out of the filter. And it had, based on those estimates, it had an estimated subtree cost of only 28.

So I have the same shape as this plan, I have the shape with the window aggregate operators with the batch mode.

But on my new one, it did pay attention to the value that I optimized it for, and for the different threshold value. It thinks not just one row is going to come out of that filter, but that a lot more rows are going to come out.

So although a plan shape is being forced on this, it is still looking at the parameter that it compiles for, it’s still thinking about statistics

I can tell that the plan is being shaped by looking at the properties and seeing that ‘use plan’ = true. The fact that this check box doesn’t show up over here doesn’t mean that magic isn’t happening. Magic is happening behind the scenes if I see a check box anywhere on this screen. Possibly this report will change in the future and evolve, we recently got this report.

We’ll see how that evolves, but as for now, I would look very carefully at plan properties in here when I’m asking the question, “Has this been auto tuned?”