Posted on Leave a comment

Auto-Tuning: Automatic Plan Correction is Cleared on Restart

The most delicious plans are mauve, but teal is a close second

The new Enterprise Automatic Tuning feature in SQL Server 2017 may sound intimidating at first — one question I get a lot lately is whether or not there’s a future for DBAs. Will Auto-Tune mean we don’t need any more human tuning?

Well, not anytime super soon.

I’m a big fan of the Automatic Plan Correction feature in SQL Server 2017, but it reminds me a bit of the missing index DMVs we got in SQL Server 2005: the suggestions are a fantastic indication of where you need to look and use your powerful human brain.

Automatic plan correction is temporary — and that’s a good thing

One of the things I love about this feature is that it wisely includes some caution.

If you’ve enabled automatic tuning and it finds what looks like a plan regression, it won’t just force a plan and forget about it.

Let’s say we have a query where the average CPU time with Plan A is 2 seconds, and the average CPU time for Plan B is 10 seconds. If Automatic Plan Correction kicks in, we go into a state where Plan A is being verified.

Trust, but verify

Is Plan A really always faster? It may be that after Plan A is being ever so gently forced (ok, that’s not gentle), the query is run with different parameters, that make Plan A skew horribly wrong, and our average CPU time goes to 12 seconds. SQL Server is looking for that.

Un-force when things change

Automatic Plan Correction is also pretty skittish when it comes to change.

Change indexes on the table? Whoops, we better un-force everything in case there’s something that could be faster! Update statistics on the table? Same thing!

And also…

Automatic Plan Corrections don’t persist over restarts

I did a little demo of this, just to prove it to myself.

I have an Automatic Plan Correction in verification state. Here’s a view of it in sys.dm_db_tuning_recommendations:

But then, I restart my SQL Server instance:

Don’t tease me about instance stacking on my test machine! I keep the Standard Edition instance off most of the time ūüėÄ

Once the instance comes back up, Automatic Plan correction is no longer in place.

Here’s a different view, after restart. Looking at Query Id 25 in the Queries with High Variation report, there are NO check boxes in those bubbles to the right. Nothing is being forced.

Everything is also gone from view in the sys.dm_db_tuning_recommendations. That data isn’t persisted after restarts.

The good news: we don’t completely start from scratch

My Query Store still has data about past performance of the plan.

After restart, if my query happens to compile with the “slow plan”, the fast plan can be identified from the history capture in Query Store before the restart.

In other words, Automatic Plan Correction doesn’t only consider information since the last restart when making its suggestions. I tested, and just by repeatedly running the “slow plan” for Query Id 25, a recommendation popped up. Since I have automatic tuning enabled for this database, it was automatically applied.

Check boxes indicate that plan forcing is currently active

Think of Automatic Tuning as an assistant DBA

And it’s better than just a random assistant! It’s an assistant that can help point you in the direction of problems like bad parameter sniffing, which are really hard to track down.

If your environment is so sensitive that you don’t trust your assistant to just go tuning things, that’s fine! You don’t have to enable Automatic Tuning, and you can still look at the suggestions manually¬†(as long as you’ve got Enterprise Edition).

But at this point, and for the foreseeable future, your assistant still needs you to dig into the code, indexes, and architecture to find a better long term solution than just freezing the plan. Because if just freezing the plan was enough, this feature would be a whole lot simpler.

Posted on Leave a comment
Posted on 2 Comments

Forced Plan Confusion: Is_Forced vs Use Plan = True

Identifying that a query plan has been bossed around in Query Store can be a bit tricky, because it can appear in different ways.

This is a long post, but there is a recap at the bottom if you’re short on time. Or just vote up my suggestion to make this easier to see: vote here.¬†

1. The “Original FORCER” plan

This type of forced plan will have a check mark in its bubble on the graphical Query Store reports, and also have is_forced = 1 in sys.query_store_plan.

However, this type of forced plan will NOT have “Use plan” = true in the properties of the top leftmost operator.

2. The “Slightly Different FORCEE” plan

This type of forced plan with NOT have a check mark on in its bubble on the Query Store reports, and will NOT have is_forced = 1 in sys.query_store plan.

But it WILL have “Use plan” = true in the properties of the top leftmost operator.

I see these differences both if a plan is manually forced, or if I’m using Automatic Tuning

Whether or not you have the 2017 Automatic Plan Correction feature enabled (part of Auto-Tuning), you’re clicking the graphical “Force Plan” button in the Query Store reports, or you’re running sys.sp_query_store_force_plan, you will see these differences.

Let’s take a look at a demo

In this post, I’m going to show you how this works if I manually run¬†sys.sp_query_store_force_plan. The demo code is at the bottom of the post and also supports letting Automatic Plan Correction force the plan, so you can run it either way and play with it for yourself.

Thanks to Jovan Popovic of Microsoft for publishing the original code under the MIT license. I adapted his code for this demo.

This is a parameter sniffing problem. First we get the ‘fast plan’ in cache

I’m using manual plan forcing in Query Store for this demo, so first up I:

  • Restore the WideWorldImporters sampled database from WideWorldImporters-Full.bak
  • Configure Query Store and make sure it’s enabled

Then I start a workload. I have changed the demo slightly from Jovan’s original, and in our version we run a SELECT query from Sales.OrderLines where the PackageTypeID = 5.

EXEC sp_executesql N'select avg([UnitPrice]*[Quantity])
            from Sales.OrderLines
            where PackageTypeID = @packagetypeid', N'@packagetypeid int',
          @packagetypeid = 5;
GO 60 -

We run this query 60 times.

This gets a nested loop plan in cache for our query with an average CPU time of .06 milliseconds. Here’s what it looks like in the Top Resource Consumers Report:

Click for a larger image

But now… we have a recompile, and a different parameter is sniffed

Lots of things can cause a recompile: memory pressure, data in the tables changing, or someone running ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE.

In this case, it’s the third one.


/* Get slow plan in cache */
EXEC sp_executesql N'select avg([UnitPrice]*[Quantity])
            from Sales.OrderLines
            where PackageTypeID = @packagetypeid', N'@packagetypeid int',
          @packagetypeid = 0;

The query that happens to compile now for our query has @packagetypeid = 0.

This query doesn’t get a nested loop join, it gets a hash match join. It doesn’t take too long to run by itself, but unfortunately this plan is slow as dirt when it is reused for a value like @packagetypeid = 7.

That runs 20 times, and this plan has an average CPU time of 389 seconds.

EXEC sp_executesql N'select avg([UnitPrice]*[Quantity])
            from Sales.OrderLines
            where PackageTypeID = @packagetypeid', N'@packagetypeid int', @packagetypeid = 7;
go 20

I haven’t enabled Automatic Plan Correction, but I get a suggestion

Because I’m on SQL Server 2017 and I have Query Store configured, sys.dm_db_tuning_recommendations has a little heads-up for me, if I know to look there.

Click for a larger image

It looks like I’ve got a plan re-use problem, aka ‘Bad Parameter Sniffing’.

The right thing to do would be to look into why I’m getting different plans

If I were to do the right thing, I would try to figure out how to stabilize the plans without forcing anything.

But this post isn’t about doing the right thing, it’s about forcing!

Let’s say I take the script from the suggestion, and manually force this plan with TSQL

exec sys.sp_query_store_force_plan @query_id = 18, @plan_id = 1

I have now forced plan_id 1!

If I look at Top Resource Consumers, plan_id 1 now has a check mark by it:

After forcing the plan, the query runs some more

Our query just runs once, again with @packagetypeid = 7.

This is the first time the query has run with @packagetypeid = 7 while being forced to use a plan compiled for @packagetypeid=7.

EXEC sp_executesql N'select avg([UnitPrice]*[Quantity])
            from Sales.OrderLines
            where PackageTypeID = @packagetypeid', N'@packagetypeid int', @packagetypeid = 7;

Whoa, now we have THREE plans

Our query uses the forced plan, but, wow, it gets a new plan_id: 10. Here’s what plan_id 10 looks like with its plan:

Click for a larger image

Plan_id 10 has no check-mark, but it has been forced to use the nested loop plan shape.

However, notice that it got a missing index request, and the bars in the plan are much thicker than the nested loop plan in the screenshot above.

Plan_id 10 is a forced plan, but it was compiled for @packagetypeid=7

If we look in the properties of the SELECT operator on plan_id 10, I can see a couple of things:

Click for a larger image

Parameter compiled value

In the Parameter List, @packagetypeid = 7. That is the value this was compiled for. That influenced the costing. That’s good, because there WERE more rows flowing into that stream aggregate. The higher estimates are also responsible for the green hint notifying us that we should take a look at our indexes, because it could be better.

Use plan = true

This plan was generated with a directive to use this plan shape. It didn’t just happen.

Plan 10[not forced]

Look right above the SELECT operator in the plan. It says “not forced”.

But, uh….. Use plan = true.

If it wasn’t forced, it was leaned on pretty heavily!

I read that “not forced” as, “This is the¬†exact plan that was forced.” To know if it was forced but has different costs, you have to look for that use_plan=true.

Here’s what it looks like in sys.query_store_plan

The checkmark in the graphic Query Store reports is reflecting the plan which has is_forced_plan = 1.

For plan_id 10, if you dig into the plan stored in the¬†query_plan column in the DMV, you will find that “Use plan=true” property, just like we saw in the report.

Recap: Plan FORCER and Plan FORCEE

This isn’t easy to summarize, but I’m going to give it my best shot!

When plans are forced in Query Store, whether it’s done by a user or by Automatic Tuning, there is more than one type of “forced” plan.

Plan FORCER: The exact plan which is forced will show with a check mark in Query Store reports, and will have Is_Forced = 1 in sys.query_store_plan.

Sometimes (probably often), you will see Slightly Different Plan FORCEEs: Once a plan is forced, either that exact plan can be compiled (which will have a checkmark and is_forced=1, it’s the same plan id), or sometimes a slightly different FORCEE can be compiled with different costs and different row estimates. This has the same shape as the forced plan, but you’ll see different shaped bars in it, perhaps a missing index suggestion and different memory grants. This FORCEE plan does not show with a check mark, has Is_forced = 0, but¬†does have a plan property of ‘Use plan’ = true on the top leftmost operator.

I totally made up the words “FORCER” and “Slightly Different Plan FORCEE”, for the lack of better terms. And they make me laugh.

Wanna play with it yourself?

Here’s the demo code in a gist.

Want this to be easier to see?

Vote up my suggestion to make this easier to see: vote here.

Subscribers: watch live demos of this with more scripts in the course on Automatic Plan Correction! New users, subscription options are here.

Posted on 2 Comments