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.