{"id":903,"date":"2018-02-26T07:27:16","date_gmt":"2018-02-26T15:27:16","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=903"},"modified":"2018-02-26T07:29:37","modified_gmt":"2018-02-26T15:29:37","slug":"query-store-fix-in-sql-server-2017","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-fix-in-sql-server-2017\/","title":{"rendered":"Query Store Fix in SQL Server 2017"},"content":{"rendered":"

There is a Query Store fix in the latest Cumulative Updates of SQL Server 2017, and if you are keeping current with the latest cumulative updates, there\u2019s a script you need to run if you\u2019ve installed CU2<\/a> at any point.<\/p>\n

First, if you are still on CU2, you might want to consider upgrading to CU4<\/a> (released February 17, 2018).<\/p>\n

Second, if you previously had CU2 installed (and are now on CU3<\/a> or CU4), you want to make sure that you\u2019ve run the script included with CU3 and CU4 release notes.<\/p>\n

The script removes plans from Query Store that were captured when running CU2.\u00a0 If you want to know for certain whether your database is affected before you execute the script, you can run the following query against the databases with Query Store enabled:<\/p>\n

\r\n\/* execute against EACH database that has Query Store enabled *\/\r\nSELECT COUNT([plan_id])\r\nFROM [sys].[query_store_plan]\r\nWHERE [engine_version] = '14.0.3008.27';\r\nGO\r\n<\/pre>\n
As an aside, if you aren\u2019t using Query Store, notice that one of the things it captures for the plan is SQL Server version\u2026how cool is that in terms of testing, upgrading, and troubleshooting?<\/em><\/p>\n
If you have plans from CU2, they need to be removed from Query Store, and this is done in the script using the sys.sp_query_store_remove_plan function.\u00a0 Understand that in addition to removing the query plan, it will also remove the runtime stats tied to that plan from Query Store.\u00a0 But, more important than that, if that plan was forced for a query, it will be un-forced before it is removed<\/strong>.<\/em><\/p>\n
Therefore, before<\/em> you run the script from Microsoft, I strongly recommend you not just check to see if you have plans from CU2, but you also look to see if any of those are forced:<\/p>\n
\r\n\/* execute against EACH database that has Query Store enabled *\/\r\nSELECT\r\n   [p].[query_id],\r\n   [p].[plan_id],\r\n   CASE\r\n      WHEN [q].[object_id] = 0 THEN 'Ad-hoc'\r\n      ELSE OBJECT_NAME([q].[object_id])\r\n   END AS [Object],\r\n   [qt].[query_sql_text],\r\n   [q].*, TRY_CONVERT(XML, [p].[query_plan]) AS [QueryPlan_XML]\r\nFROM [sys].[query_store_plan]\u00a0 [p]\r\nJOIN [sys].[query_store_query] [q]\r\n   ON [p].[query_id] = [q].[query_id]\r\nJOIN [sys].[query_store_query_text] [qt]\r\n   ON [q].[query_text_id] = [qt].[query_text_id]\r\nWHERE\u00a0 [engine_version] = '14.0.3008.27'\r\n   AND [p].[is_forced_plan] = 1;\r\nGO\r\n<\/pre>\n
This script will list any queries that have forced plans, and if they are part of an object (e.g. stored procedure) it will also list the object name.\u00a0 If no rows return from this query, then you don\u2019t have any forced plans which are affected and you can run the script from Microsoft.<\/p>\n
If you do have any queries with forced plans, I recommend that you save a copy of the forced plan as a .sqlplan file, so that you have documentation of what plan was forced.\u00a0 There are two ways to do this:<\/p>\n
    \n
  1. Within the Query Store page for the user database in SSMS, run the Queries with Forced Plans report. In the grid, sort the list by query_id.\u00a0 For each query_id identified by the query above, select the forced plan, then right-click on it and save.<\/li>\n
  2. Within the Query Store page for the user database in SSMS, open the Tracked Queries report. Individually enter each query_id identified by the query above, select the forced plan, then right-click on it and save.<\/li>\n<\/ol>\n
    Once you have saved off a copy of every forced plan, then you can execute the script from Microsoft.<\/p>\n
    Note: If you are using Automatic Plan Correction, this will also un-force those plans (which would have been forced automatically).<\/p>\n
    At this point, queries that previously had a forced plan may have performance issues.\u00a0 It\u2019s possible that changes in your data distribution, changes in statistics, or perhaps even changes in the optimizer have caused a different plan to be generated that might be acceptable performance-wise.\u00a0 If that is not the case, there is no ability to import plans into Query Store.\u00a0 Each query that had a forced plan, that doesn\u2019t get a new, \u201cgood\u201d plan, may need to be run again to get the same plan into Query Store.\u00a0 One of the easiest things to do is to find compiled values within the plan using the ParameterCompiledValue attribute, then re-run the query using those values.\u00a0 You can then use data from Query Store, and compare against the saved plan, to verify it\u2019s a consistent\/stable plan.<\/p>\n
    \"Finding<\/a>
    Finding input parameters from the query plan<\/figcaption><\/figure>\n
     <\/p>\n
     <\/p>\n
     <\/p>\n
     <\/p>\n
     <\/p>\n
     <\/p>\n
     <\/p>\n
     <\/p>\n
     <\/p>\n
     <\/p>\n
     <\/p>\n
     <\/p>\n
     <\/p>\n
    I definitely recommend updating to the current Cumulative Update (and this is a general recommendation, not just because of the issue I’ve discussed here), and part of preparing for that upgrade means checking to see if you’re affecting by this issue, and addressing it as part of the upgrade process, rather than down the road.<\/p>\n","protected":false},"excerpt":{"rendered":"
    There is a Query Store fix in the latest Cumulative Updates of SQL Server 2017, and if you are keeping current with the latest cumulative updates, there\u2019s a script you need to run if you\u2019ve installed CU2 at any point. First, if you are still on CU2, you might want to consider upgrading to CU4 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[46],"tags":[],"yoast_head":"\nQuery Store Fix in SQL Server 2017 - Erin Stellato<\/title>\n<meta name=\"description\" content=\"If you're currently running, or have previously run, SQL Server 2017 CU2, there's a Query Store fix you need to know about.\" \/>\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\/erin\/query-store-fix-in-sql-server-2017\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Query Store Fix in SQL Server 2017 - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"If you're currently running, or have previously run, SQL Server 2017 CU2, there's a Query Store fix you need to know about.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-fix-in-sql-server-2017\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2018-02-26T15:27:16+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-02-26T15:29:37+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/02\/parameters-1024x374.jpg\" \/>\n<meta name=\"author\" content=\"Erin Stellato\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Erin Stellato\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-fix-in-sql-server-2017\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-fix-in-sql-server-2017\/\",\"name\":\"Query Store Fix in SQL Server 2017 - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2018-02-26T15:27:16+00:00\",\"dateModified\":\"2018-02-26T15:29:37+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"If you're currently running, or have previously run, SQL Server 2017 CU2, there's a Query Store fix you need to know about.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-fix-in-sql-server-2017\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-fix-in-sql-server-2017\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-fix-in-sql-server-2017\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Query Store Fix in SQL Server 2017\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\",\"name\":\"Erin Stellato\",\"description\":\"The SQL Sequel\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\",\"name\":\"Erin Stellato\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"caption\":\"Erin Stellato\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/erin\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Query Store Fix in SQL Server 2017 - Erin Stellato","description":"If you're currently running, or have previously run, SQL Server 2017 CU2, there's a Query Store fix you need to know about.","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\/erin\/query-store-fix-in-sql-server-2017\/","og_locale":"en_US","og_type":"article","og_title":"Query Store Fix in SQL Server 2017 - Erin Stellato","og_description":"If you're currently running, or have previously run, SQL Server 2017 CU2, there's a Query Store fix you need to know about.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-fix-in-sql-server-2017\/","og_site_name":"Erin Stellato","article_published_time":"2018-02-26T15:27:16+00:00","article_modified_time":"2018-02-26T15:29:37+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/02\/parameters-1024x374.jpg"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-fix-in-sql-server-2017\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-fix-in-sql-server-2017\/","name":"Query Store Fix in SQL Server 2017 - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2018-02-26T15:27:16+00:00","dateModified":"2018-02-26T15:29:37+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"If you're currently running, or have previously run, SQL Server 2017 CU2, there's a Query Store fix you need to know about.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-fix-in-sql-server-2017\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-fix-in-sql-server-2017\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-fix-in-sql-server-2017\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Query Store Fix in SQL Server 2017"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/","name":"Erin Stellato","description":"The SQL Sequel","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158","name":"Erin Stellato","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","caption":"Erin Stellato"},"sameAs":["http:\/\/3.209.169.194\/blogs\/erin"],"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/903"}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/comments?post=903"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/903\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=903"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=903"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=903"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}