One interesting and useful new feature in SQL Server 2017 is Automatic plan correction<\/a>. This feature relies on having Query Store<\/a> enabled for your database. It 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<\/a> for CPU-related plan regressions. Microsoft has more details about automatic tuning here<\/a>.<\/span><\/span><\/p>\n

One important detail that isn\u2019t easy to find about automatic plan correction is whether it is available in SQL Server 2017 Standard Edition or not. We can easily confirm whether it is Enterprise-only feature with a simple test. You can do this by trying to enable the feature on a SQL Server 2017 Standard Edition instance, and see what happens.<\/span><\/p>\n

You use the T-SQL shown in Figure 1 to enable automatic plan correction for the current database.<\/span><\/p>\n

\u00a0<\/span><\/p>\n

<\/a><\/p>\n

Figure 1: T-SQL to Enable Automatic Plan Correction in SQL Server 2017<\/strong><\/span><\/p>\n

\u00a0<\/span><\/p>\n

As it turns out, this feature is not available<\/u><\/strong> 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.<\/span><\/p>\n

\u00a0<\/span><\/p>\n

<\/a><\/p>\n

Figure 2: Error Message from SQL Server 2017 Standard Edition<\/strong><\/span><\/p>\n

Yo can learn more about this feature by watching Erin Stellato\u2019s Pluralsight course, SQL Server: Automatic Tuning in SQL Server 2017 and Azure SQL Database<\/a>. You can also read her article on SQLPerformance.com<\/a>, which is Automatic Plan Correction in SQL Server<\/a>.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

One interesting and useful new feature in SQL Server 2017 is Automatic plan correction. This feature relies on having Query Store enabled for your database. It 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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[397,342],"tags":[398],"class_list":["post-1409","post","type-post","status-publish","format-standard","hentry","category-enterprise-edition","category-sql-server-2017","tag-automatic-plan-correction"],"yoast_head":"\n