Posted on

Why I Love the ‘Automatic Plan Correction’ Auto-Tuning Feature

I’m thrilled to have just finished and published a new course on Automatic Tuning in SQL Server. This feature is available in SQL Server 2017+ and Azure SQL Database.

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

Cynical me assumed…

  • This feature won’t be that cool
  • 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

The first two lessons in the course cover:

No signup or login is required to check out those lessons. Click the links and preview away.

Subscribers also get…

Demo scripts, a PDF of the slides, and a quiz, plus modules on:

  • The Queries With High Variation Query Store Report (4 minutes)
  • How to Tell if a Query Plan Has Been ‘Corrected’ (6 minutes)
  • Expiring an Automatic Correction With Update Statistics (10 minutes)
  • Automatic Plan Correction Can Re-Correct! Plus: Our Correction Survives a Plan Cache Flush (10 minutes)
  • Why Automatic Plan Correction Skips Queries Using Recompile (3 minutes)
  • Demo: Recompile, Automatic Tuning, and Manual Plan Forcing in Query Store (14 minutes)
  • Observations, Starter Notes, and Final Q&A (5 minutes)

I also talk in the course about things you can do to manually find these problem plans in Query Store if you don’t have Enterprise Edition.

Interested in subscribing?

I’d love to have you join. Sign up here.

 

4 thoughts on “Why I Love the ‘Automatic Plan Correction’ Auto-Tuning Feature

  1. So far I’ve only seen people recommend it for small systems that don’t have full-time DBAs — have you used or seen it used in large and busy mission-critical systems?
    I have great hopes for this one.

    1. 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 😉

      1. 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.

        1. “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.

Leave a Reply

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