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.

Links

Here’s Erin Stellato’s post on Query Store settings.

Here’s the session from the SQL PASS Summit in 2017 where Erin Stellato and Dejan Krakovic presented on Query Store and Automatic Tuning. The session is mostly about Query Store, so if you want more background on that it’s a terrific resource.

Transcript

If you’re excited by seeing the auto-tuning, play with these scripts!

This isn’t a huge database. You can set this up on a little Developer Edition instance and play around with all sorts of things. You can also do things like try un-forcing the plan yourself, try manually forcing your own things in Query Store.

There is a lot of fun to be had here.

Here’s my starter pack for automatic plan correction. The things that go into this are…

Some observations

As we saw, that automatic plan correction is temporary, and that’s for a good reason. Maybe data changing means that, hey, we could get something even faster now. This is really a temporary safety net. If we do enable auto-tuning, it will kick in sometimes and help us sometimes, but if there is ever an auto-tuning we want to ever manually undo, it’s not permanent. It can come back and auto-tune it again.

If we want to use auto-tuning, we need to be in 100%. We need to really want it to auto-tune.

When we’re looking at reports in Query Store, the check boxes indicate: is something actively being auto-tuned right now? It may be that the plan, or that the query you’re looking at has been auto-tuned in the past, and to tell that you need to look at Plan Properties and look for Use Plan = True.

With all this info, here’s my take on this

The first thing to do, if you’re on 2017+, make sure that you’re comfortable running Query Store and are getting some data in that as the first part. That should be its own separate change.

Erin Stellato of SQLSkills has a great blog post where she walks you through every setting on Query Store, talks about what the default value is, whether she uses a different value, and the considerations on how to set that for your own database.

So, if you haven’t set up Query Store yet, I would start with Erin’s recommendations.

Look through all those settings and work on that change to get Query Store in play in a Read-Write mode. It is per database. Then, the rest of this is if you have Enterprise Edition.

If you have Enterprise Edition…

I would just start looking at those tuning recommendations. Without enabling auto-tuning, with Enterprise Edition, it’ll start showing up those recommendations. Just start looking at them and looking at the queries yourself. For critical databases where people are not going to like big fluctuations in performance, I wouldn’t just turn on auto-tuning, because as you saw, things can be fast, things can be slow.

But for less critical databases, if I have Enterprise Edition, and it’s a less critical database, I am going to be much more open to just saying: yeah auto-tune some stuff and I’ll come around and periodically review it. I’m not going to look at it everyday, but I’ll periodically review it and see, okay, this stuff is being auto-tuned, I want to make a change to stabilize it so it doesn’t have to be auto-tuned.

If I have Standard Edition…

I want to set up Query Store, and then I want to start looking at those queries that have the high variation. I like to do that with the CPU time metric based on standard deviation. That’ll help show, okay, these are the queries where they have very varying run times. Sometimes they’re slow and sometimes they’re fast, and you can look at what the plans are for those and manually yourself figure out, okay, how can I stabilize this? Because in Standard Edition, or anything other than Enterprise and Developer, I don’t get those nice suggestions in the DMV for me.

Thank you guys, for attending today

Thank you for all the great questions. I really, really like this topic.

I’m going to start looking through the comments. Hi, Esther! I’m glad you enjoyed it, thank you.

John says what are the options for Standard Edition?

The options for Standard Edition are writing T-SQL against Query Store, using the reports for Query Store. I do really like that high variation report as a workaround, I’m glad we have that.

Ah, great question from Lizzie. Is this only available on primary replicas in an AG since it uses Query Store?

Yes. Now in a session at PASS with Erin and a member of the product group, whose name I really wish I could remember, but I can’t {note: it’s Dejan Krakovic!}. The member of the product group said, it’s on the roadmap to somehow get Query Store on readable secondaries in Availability Groups, so there was much optimism in the room. But as of this moment, yes, we only have Query Store on the primary replicas and so we do not have auto-tuning on secondary replicas.

Thank you so much folks, for joining. I really had fun on this one. All right, so I’m going to head out. I will see you guys in a couple of weeks, and thanks for joining me today. Bye folks!