I am WAY more excited about this feature than I thought I would be

It only works sometimes — when you’ve actually had decent performance in the past!

It’ll probably be really clumsy

I shouldn’t be so cynical, because after working with this even for a short time, I started to see the magic.

I’m glad I was wrong

Yes, the feature only works sometimes. However, that’s because it’s NOT really clumsy. It’s quite thoughtful!

Automatic Plan Correction is smart enough to test and verify its changes. It’s smart enough to back off what it’s done when you add an index, or when your data distribution changes, and then test and verify again.

But this is OK, because it’s not trying to permanently fix things. It’s trying to make things better for a little while until YOU can step in and figure out a permanent fix!

Automatic Tuning makes it much easier to find bad parameter sniffing

One of the trickiest performance problems that developers and DBAs face is figuring out “why was this thing slow two hours ago, but it’s fast now?”

Many times, the answer to this is “bad parameter sniffing”: we had an execution plan in cache that was slow when the query was executed with a variety of different parameter values.

This is hard to identify because often a different plan is currently in cache by the time we look at it. And even if we have fancy monitoring tools, it can be tricky to compare the average costs of the plans and determine which plan may be better.

The Automatic Plan Correction feature is looking exactly for things like that.

And that is a hard enough problem to detect that I see why this is an Enterprise Only feature. I get it.

Best of all, you can try this out in a safe way

You can either let Automatic Plan Correction test out changes for you, or just look at its recommendations.

The recommendations contain pretty rich detail about the problem it observed, the regressed plan, and the plan that looks like it would be better. You can take that information and use it with Query Store to devise your own fix safely.

Pretty cool.

This course is for subscribers. If you haven’t joined, you can watch the first two video lessons for free

Microsoft has used it widely, I don’t know if they’ve published the numbers on exactly how many instances, or how many large instances have been using it in Azure. I will ask around at MVP Summit this week and see if they have any numbers or specific examples that can be called out.

For mission critical instances, I’d start just by evaluating the suggestions without letting it apply them. Often for those type of instances, consistent performance is as important as fast performance, and having auto-tuning apply something and then have it come loose because of a statistics update or instance failover won’t always be popular. I think that’s the true beauty of it, that you can let it just cough politely and say, “Hey, looks like you’ve got a problem over there!”

I do think it could be a big help to those smaller shops, even though the fixes are temporary. If they have fluctuating performance and can figure out which DMV to look at (or bring in someone who does), there is potentially some rich information in the DMV that can help. Assuming they haven’t just been restarting the instance 😉

In my experience, when an execution plan goes horribly wrong for a query that’s executed all the time, it often brings the entire instance to its knees. So having auto-tuning interfere quickly and stabilize the situation could be priceless, and especially so during off-hours.

You mention the risk of something applied by auto-tuning not working anymore — but then auto-tuning will kick in again after a few bad executions, right? And of course each time something is applied by auto-tuning it should be followed up by the DBA team to stabilize the execution in a more permanent manner.

But the near-instant (hopefully) response to the situation by auto-tuning is the real revolution here. I’m trying to see how it could go wrong, but I can’t find a fault in the process.

“Near-instant” won’t always be true, though, depending on the duration of the query and how often it’s executed. Auto-tuning needs to build up a significant enough sample size for the query based on completed executions, and with larger queries, that can take a while — even if we’re just talking about 20-30 executions. (There’s no guaranteed number, though, for all queries.)

In one of my demos it takes 6-8 minutes for auto-tuning to kick back in while I’m repeatedly running the sample query in a single session, for example. The time to kick in will be all over the place depending on environment.

It is true that auto-tuning won’t force a plan that doesn’t “naturally” occur and couldn’t otherwise cause the same thing to happen, just by being the plan in cache. However, that’s a nuance that not all management is going to understand when it comes to postmortems on mission critical systems — some of them will hear that automatic tuning forced a plan and then just check out on the rest of the details.

One thing that I think would be quite nice would be to have an Event Notification option, where new suggestions could kick off a notification to the DBA team on critical systems where folks would rather evaluate the issue right away, rather than forcing a plan.

[…] The final route of the optimizer will not be solely in direction of automation, but in addition in direction of adaptation. Therefore all of the work in 2017 for Adaptive Joins, Reminiscence Grant Suggestions, and Plan Correction. […]

[…] The overall path of the optimizer will not be solely in the direction of automation, but in addition in the direction of adaptation. Therefore all of the work in 2017 for Adaptive Joins, Reminiscence Grant Suggestions, and Plan Correction. […]