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

We have some useful reports in Management Studio.

One of the ones we’ve gotten recently is the Queries With High Variation Query Store Report

This is all database level. I’m in my user database BabbyNames, and I’m going to open up Queries With High Variation.

I like this report because it’s designed around– if we think about what is queries with high variation, what queries are sometimes fast, and sometimes slow. Now the default it uses here is to look at the metric ‘duration’ based on ‘variation’.

First thing I’m going to do is change this up

I’m going to look at CPU time

Not just duration but actually how much CPU are you burning.

I also want to base it on the standard deviation

I want to see the queries where there is a wide distribution, where things aren’t near the average, where, hey, sometimes it’s really slow, and sometimes it’s really fast.

I also like to change this button here..

Instead of viewing this top left quadrant as a graph, I find the grid format much easier to deal with

I’m going to click that button, and now I can see what my queries actually are.

My top object here is my PopularNames procedure. When I click on different procedures, it changes up all the other graphs, so I drive the report here in the top left.

Right now I’m looking at PopularNames. I can see over here– this is currently configured for the last hour– that I have two different plans. I have the blue plan and the green plan.

The green plan has this checkbox.

Whenever you see the checkbox, that indicates that some plan forcing is active right now

There might have been a plan forcing in the past that isn’t active right now, and I might not see the checkbox, so the checkbox doesn’t indicate if plan forcing ever happened, it’s more an indication of: is plan forcing happening right now?

The forced query plan number five– Rather, it is forced but it’s being applied, it’s the last good plan.

I’m clicked on that guy so I see that plan in the bottom pane. Sure enough, that is our plan with the window aggregate operator.

Plan_id six, the slow plan, is our plan who has all those windows spools and segments. It’s using the old rowstore operators. Well, okay, so this is being actively forced right now.

Note that I could unforce this

I can manually unforce this plan. If you’re like me your first question is: if I manually unforce it, does that mean that it won’t auto-tune it again? I can unforce it, but as long as auto-tuning is enabled in the database it might be auto-tuned again right back to that plan.

If I don’t want it to be auto-tuned, just unforcing it once, it isn’t forever. That makes sense because if we’re auto-tuning the database, we are auto-tuning the database. If we don’t want to auto-tune the database, maybe we just want to look at those suggestions and handle everything ourselves.