One interesting and useful new feature in SQL Server 2017 is Automatic plan correction. This feature, which relies on having Query Store enabled for your database, will look for query plan choice regressions where there has been a significant regression based on CPU time for a given query. This feature essentially automates the usage of sp_query_store_force_plan for CPU-related plan regressions. Microsoft has more details about automatic tuning here.

One important detail that isn’t easy to find (in Microsoft documentation) about automatic plan correction is whether it is available in SQL Server 2017 Standard Edition, or whether it is an Enterprise Edition-only feature. One way to easily confirm whether it is Enterprise-only, is to try and enable the feature on a SQL Server 2017 Standard Edition instance, and see what happens.

You use the T-SQL shown in Figure 1 to enable automatic plan correction for the current database.


image

Figure 1: T-SQL to Enable Automatic Plan Correction in SQL Server 2017


As it turns out, this feature is not available unless you are using SQL Server 2017 Enterprise Edition, as you can see in Figure 2. This is another reason that you should prefer Enterprise Edition if your budget allows it.


image

Figure 2: Error Message from SQL Server 2017 Standard Edition

If you want to learn more about this feature, you should watch Erin Stellato’s Pluralsight course, SQL Server: Automatic Tuning in SQL Server 2017 and Azure SQL Database. You can also read her article on SQLPerformance.com, Automatic Plan Correction in SQL Server.