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

Welcome to Automatic Plan Correction with Query Store.

This is a live webcast (but you’re watching an edited recording!)

My name is Kendra Little and I am the founder and creator of SQLWorkbooks.com.

Today, we will be looking at one part of the new Automatic Tuning features in SQL Server. We will be looking at a feature called Automatic Plan Correction, which uses Query Store in your SQL Server 2017 database or higher to look for cases where a query has slowed down significantly.

It can notice that, hey, when you run with this plan, you’re faster

We had a regression, so I’m going to automatically tune you and give you the fast plan, then I’m going to validate and make sure it really is faster.

It’s got some intelligence there about trying out this faster plan, and it is designed as a safety net so that you can make sure that your queries don’t slow down forever.

It’s not going to prevent your queries from ever getting slow, because it has to notice that they got slow, but it can then jump in and say “Hey, maybe I can speed you up.” There’s another part of Automatic Tuning that we won’t see today that’s called automatic index management: it’s currently available in Azure SQL Database. Maybe it will show up somewhere along the roadmap in the version of SQL Server that we install ourselves, later. That’s for another day, because we can spend the whole day today talking about Automatic Plan Correction.

Our mission today in exploring this is…

• How do we turn it on
• What does it do, and…
• If you’re interested in this, what is a starter plan for using it?

I’m really excited about this!

If you have Enterprise Edition, I think you should at least be looking at the recommendations for this, if you’re on 2017 or higher.

Potentially this could even take part in, if you’re making a proposal to upgrade SQL Server to get the money for that, potentially this could be one of the selling points for your upgrade as well.

I think it’s a really interesting feature.

Some folks are frightened of this because it sounds like maybe we won’t need DBAs anymore

I think, really, it’s exciting rather than threatening and I’ll show you why.

So first up, how do I turn it on?

It is not complicated to turn it on at all. The first thing you need to know, though, is that you do have to have the Query Store feature enabled. The plan correction relies on Query Store collecting the plans and aggregate information about performance about, okay, how much average CPU time is that plan using? So we have to have Query Store all set up first and collecting data.

Then, we enable Automatic Tuning. Now here is the bad news. Automatic Tuning is Enterprise Edition only.

Query Store itself is not only in Enterprise Edition. You can use Query Store widely– and I am going to show you, okay if I don’t have Enterprise Edition and I don’t have Automatic Tuning, what can I do with Query Store where I can at least see the types of things it’s making suggestions about? I’ll give you kind of a work-around, the manual version, if you don’t have Enterprise Edition.

The other thing about Automatic Tuning is you enable for the entire database.

You can’t turn this on for just a given schema. You can’t say, “I want to tune everything except that procedure.” It’s all the queries that run in a database, whether the queries are in stored procedures or not.

Now we’ve got a question here from Allen. Allen says, “Is this only available in Enterprise Edition?” Yes, in fact, only Enterprise Edition and I did also do a check that, okay, can I see the suggestions if I’m not in Enterprise Edition? In fact I can’t even see suggestions in the DMV if I’m not in Enterprise Edition.

Here is the command that we use to turn this on

Right now in SQL Server 2017 we can say set AUTOMATIC_TUNING FORCE_LAST_GOOD_PLAN ON. That is the Automatic Plan Correction feature. That doesn’t automatically tune my indexes. This will look for regressions in my query plans and try to see if the last good one, the last fast one, really is faster when I keep running it.

If you try to run this command in Standard Edition, it gives you a nice little message that says: please insert more dollars if you want to turn on the Automatic Tuning feature. Wah wah wah. Sorry about that, but like I said, I will show you if you are in Standard Edition, how can I see the types of queries that this would tune in my Query Store? We’ve got a way to do that.

Let’s now do the fun part and dig in and start looking at Automatic Plan Correction

You’ve got these scripts available for download. What I wanted to do was create a set of scripts where, just using Management Studio, you could restore a small database, this uses the BabbyNames Database that you can download for free, and then, run a workload against that database that gets a procedure that is auto-tuned.

I’ve configured Query Store, I’ve enabled that forced last good plan, and then I’ve already run this workload which runs a query that is sometimes fast and is sometimes slow.