What is the Automatic Plan Correction in SQL Server 2017?

Guy-Glantser, 2017-12-25

SQL Server 2017 brings a new interesting feature, that might be a game changer in some environments. I’m talking about Automatic Plan Correction. This feature automates the on-going tuning process of identifying regressed execution plans and then replacing them with the last known good plan. Let me explain…

 

One of the common performance issues in every SQL Server database is plan regression. There are all kinds of reasons for recompilations of plans in SQL Server, such as statistics update. So once in a while a plan might be recompiled (either automatically by the system or manually by a user). When that happens, the new generated plan might be identical to the previous one, it might be better than the previous one, but it also might be worse. Again, there are all kinds of reasons why a plan would change after a recompile, and specifically why it would become worse than the previous plan. But believe me – it happens.

 

When a plan becomes worse after a recompile, it is called a regressed plan, and this process is referred to as plan regression. This is a problem, of course. While there are ways to prevent it, it’s not always possible or practical. Instead, it is more practical to monitor and identify plan regressions, and then force the last known good plan. It is also important to continue to monitor performance after forcing the plan in order to make sure that it indeed improved performance. If not, then we should unforce the plan and continue to monitor.

 

We could already do that in SQL Server 2016 using the Query Store. But we had to do everything manually, and it’s quite tedious. SQL Server 2017 builds on the Query Store, and provides this functionality out of the box. It will identify regressed plans, force the last known good plan, continue to monitor, unforce the plan if necessary, and so on. SQL Server 2017 will do all of that for you automatically.

 

I wouldn’t enable this feature so fast for a mission-critical database, where there is a team of experienced DBAs. But there are many environments, which are not mission-critical, where there is no DBA to do any type of performance tuning. In these environments, enabling this feature can bring a huge performance boost without paying and cost. And it’s not just a one-time performance tuning activity. It’s like having a DBA 24×7, who sits inside the database, constantly monitoring and adjusting performance. How amazing is that?

Check it out: https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning.

The post What is the Automatic Plan Correction in SQL Server 2017? appeared first on Madeira Data Solutions.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads