{"id":1409,"date":"2018-10-29T12:37:05","date_gmt":"2018-10-29T19:37:05","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=1409"},"modified":"2019-03-04T12:43:54","modified_gmt":"2019-03-04T20:43:54","slug":"automatic-plan-correction-is-enterprise-edition-only-feature","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/","title":{"rendered":"Automatic Plan Correction is Enterprise Edition Only Feature in SQL Server 2017"},"content":{"rendered":"<p><span style=\"font-size: small;\">One interesting and useful new feature in SQL Server 2017 is <a href=\"https:\/\/blogs.msdn.microsoft.com\/sqlserverstorageengine\/2017\/05\/17\/automatic-plan-correction-in-sql-server-2017\/\">Automatic plan correction<\/a>. This feature relies on having <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/performance\/monitoring-performance-by-using-the-query-store\">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 <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-stored-procedures\/sp-query-store-force-plan-transact-sql?view=sql-server-2017\">sp_query_store_force_plan<\/a> for CPU-related plan regressions. <span style=\"font-size: small;\">Microsoft has more details <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/automatic-tuning\/automatic-tuning?view=sql-server-2017\">about automatic tuning here<\/a>.<\/span><\/span><\/p>\n<p><span style=\"font-size: small;\">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<p><span style=\"font-size: small;\">You use the T-SQL shown in Figure 1 to enable automatic plan correction for the current database.<\/span><\/p>\n<p><span style=\"font-size: small;\">\u00a0<\/span><\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-content\/uploads\/2018\/10\/image-1.png\"><img fetchpriority=\"high\" decoding=\"async\" style=\"display: inline; background-image: none;\" title=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-content\/uploads\/2018\/10\/image_thumb-1.png\" alt=\"image\" width=\"1028\" height=\"542\" border=\"0\" \/><\/a><\/p>\n<p><span style=\"font-size: small;\"><strong>Figure 1: T-SQL to Enable Automatic Plan Correction in SQL Server 2017<\/strong><\/span><\/p>\n<p><span style=\"font-size: small;\">\u00a0<\/span><\/p>\n<p><span style=\"font-size: small;\">As it turns out, this feature <strong><u>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<p><span style=\"font-size: small;\">\u00a0<\/span><\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-content\/uploads\/2018\/10\/image-2.png\"><img decoding=\"async\" style=\"display: inline; background-image: none;\" title=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-content\/uploads\/2018\/10\/image_thumb-2.png\" alt=\"image\" width=\"1028\" height=\"542\" border=\"0\" \/><\/a><\/p>\n<p><span style=\"font-size: small;\"><strong>Figure 2: Error Message from SQL Server 2017 Standard Edition<\/strong><\/span><\/p>\n<p><span style=\"font-size: small;\">Yo can learn more about this feature by watching Erin Stellato\u2019s Pluralsight course, <a href=\"https:\/\/www.pluralsight.com\/courses\/sqlserver-azure-database\">SQL Server: Automatic Tuning in SQL Server 2017 and Azure SQL Database<\/a>. You can also read her article on <a href=\"https:\/\/sqlperformance.com\/\">SQLPerformance.com<\/a>, which is <a href=\"https:\/\/sqlperformance.com\/2018\/02\/sql-plan\/automatic-plan-correction-in-sql-server\">Automatic Plan Correction in SQL Server<\/a>.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/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":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Automatic Plan Correction is Enterprise Edition Only Feature in SQL Server 2017 - Glenn Berry<\/title>\n<meta name=\"description\" content=\"Glenn Berry demonstrates that automatic plan correction is an Enterprise Edition only feature in SQL Server 2017, and explains why that is important.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Automatic Plan Correction is Enterprise Edition Only Feature in SQL Server 2017 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"Glenn Berry demonstrates that automatic plan correction is an Enterprise Edition only feature in SQL Server 2017, and explains why that is important.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2018-10-29T19:37:05+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-03-04T20:43:54+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-content\/uploads\/2018\/10\/image_thumb-1.png\" \/>\n<meta name=\"author\" content=\"Glenn Berry\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Glenn Berry\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/\",\"name\":\"Automatic Plan Correction is Enterprise Edition Only Feature in SQL Server 2017 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-content\/uploads\/2018\/10\/image_thumb-1.png\",\"datePublished\":\"2018-10-29T19:37:05+00:00\",\"dateModified\":\"2019-03-04T20:43:54+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\"},\"description\":\"Glenn Berry demonstrates that automatic plan correction is an Enterprise Edition only feature in SQL Server 2017, and explains why that is important.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/#primaryimage\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-content\/uploads\/2018\/10\/image_thumb-1.png\",\"contentUrl\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-content\/uploads\/2018\/10\/image_thumb-1.png\",\"width\":1028,\"height\":542},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Automatic Plan Correction is Enterprise Edition Only Feature in SQL Server 2017\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\",\"name\":\"Glenn Berry\",\"description\":\"Semi-random musings about SQL Server performance\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\",\"name\":\"Glenn Berry\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"caption\":\"Glenn Berry\"},\"sameAs\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Automatic Plan Correction is Enterprise Edition Only Feature in SQL Server 2017 - Glenn Berry","description":"Glenn Berry demonstrates that automatic plan correction is an Enterprise Edition only feature in SQL Server 2017, and explains why that is important.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/","og_locale":"en_US","og_type":"article","og_title":"Automatic Plan Correction is Enterprise Edition Only Feature in SQL Server 2017 - Glenn Berry","og_description":"Glenn Berry demonstrates that automatic plan correction is an Enterprise Edition only feature in SQL Server 2017, and explains why that is important.","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/","og_site_name":"Glenn Berry","article_published_time":"2018-10-29T19:37:05+00:00","article_modified_time":"2019-03-04T20:43:54+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-content\/uploads\/2018\/10\/image_thumb-1.png","type":"","width":"","height":""}],"author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/","name":"Automatic Plan Correction is Enterprise Edition Only Feature in SQL Server 2017 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-content\/uploads\/2018\/10\/image_thumb-1.png","datePublished":"2018-10-29T19:37:05+00:00","dateModified":"2019-03-04T20:43:54+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7"},"description":"Glenn Berry demonstrates that automatic plan correction is an Enterprise Edition only feature in SQL Server 2017, and explains why that is important.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-content\/uploads\/2018\/10\/image_thumb-1.png","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-content\/uploads\/2018\/10\/image_thumb-1.png","width":1028,"height":542},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/automatic-plan-correction-is-enterprise-edition-only-feature\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/glenn\/"},{"@type":"ListItem","position":2,"name":"Automatic Plan Correction is Enterprise Edition Only Feature in SQL Server 2017"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/","name":"Glenn Berry","description":"Semi-random musings about SQL Server performance","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7","name":"Glenn Berry","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","caption":"Glenn Berry"},"sameAs":["https:\/\/www.sqlskills.com\/blogs\/glenn\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1409","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/comments?post=1409"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1409\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1409"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1409"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1409"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}