{"id":1027,"date":"2019-06-25T05:28:37","date_gmt":"2019-06-25T12:28:37","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=1027"},"modified":"2020-11-18T13:31:27","modified_gmt":"2020-11-18T21:31:27","slug":"query-store-best-practices","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-best-practices\/","title":{"rendered":"Query Store Best Practices"},"content":{"rendered":"<p>I\u2019m a huge fan of Query Store, which regular readers may know, but there&#8217;s a need to write a bit more about Query Store best practices.\u00a0 This isn\u2019t a \u201cyou must use this feature\u201d post, this is a \u201chere is what you must know if you want to use this feature\u201d post.<\/p>\n<p>I have a lot of content about Query Store, but maybe <em>what\u2019s really important<\/em> gets lost amongst everything else.\u00a0 Glenn prompted me to write this, after I worked with two customers last week that ran into issues related to their configuration of Query Store.\u00a0 Listed below are the things you <strong>must<\/strong> know before you enable Query Store.\u00a0 If you already have Query Store enabled and running, I recommend reviewing the information to ensure you are following best practices.<\/p>\n<h2>Settings<\/h2>\n<ul>\n<li>Review my post on <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-settings\/\">Query Store Settings<\/a> to understand what different settings exist for Query Store, what values are recommended for each setting, and <em>why<\/em>.\n<ul>\n<li>Most important: QUERY_CAPTURE_MODE set to AUTO, MAX_STORAGE_SIZE_MB set to 10GB at the absolute max, something less ideally (you may need to adjust CLEANUP_POLICY to keep less data, depending on your workload).<\/li>\n<li><strong>Edit: November 17, 2020:<\/strong> If you are using SQL Server 2019 and have an ad hoc workload, I recommend CUSTOM for CAPTURE_MODE.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2>Trace Flags<\/h2>\n<ul>\n<li>Review my post on <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-trace-flags\/\">Query Store Trace Flags<\/a> to understand the two Trace Flags which are Query Store-related, what they do, and why you want to enable them.\n<ul>\n<li>I definitely recommend both 7752 and 7745.\u00a0 If you have 7752 enabled and you are trying to make changes to Query Store configuration after a restart, please review <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-database-set-query_store-command-is-blocked\/\">my post discussing the Query Store data load<\/a>.\u00a0 There is no shortcut to get this to load faster, and no way to kill it.\u00a0 This is why proper settings are important.<\/li>\n<li><strong>Edit: November 17, 2020:<\/strong> Trace flag 7752 is enabled by default in SQL Server 2019.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2>Performance<\/h2>\n<ul>\n<li>Review my post on <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-updated\/\">Query Store Performance Overhead<\/a> (updated November 2020). If you have a high-volume, ad hoc workload, it is quite possible that you should <em>not<\/em> enable Query Store.\u00a0 Jonathan told me about a customer of his that enabled Query Store and within two (2) hours, they had filled up 10GB of Query Store.\u00a0 We recommended that they not use Query Store at this time.\u00a0 There are <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-in-sql-server-2019-ctp-3-0\/\">improvements coming in SQL Server 2019<\/a> that will hopefully help manage Query Store with this type of workload.<\/li>\n<\/ul>\n<h2>Version<\/h2>\n<ul>\n<li>I highly recommend that you run SQL Server 2016 SP2 CU7 or SQL Server 2017 CU15 (current CUs at the time of writing, I\u2019ll try to keep this updated). There is a <a href=\"https:\/\/support.microsoft.com\/en-us\/help\/4501205\/fix-non-yielding-scheduler-occurs-when-you-clean-up-in-memory-runtime\">very important fix<\/a> in CU7 that I strongly suggest you have in place.\u00a0 I don\u2019t see this fix in a CU for SQL Server 2017 yet, so it\u2019s possible you could run into a problem if you have an ad hoc workload.\n<ul>\n<li><strong>Edit: November 17, 2020: <\/strong>The current CUs for each release at the time of this writing are strongly recommended (please see <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-updated\/\">Query Store Performance Overhead&#8230;Updated)<\/a>.\u00a0 The CUs are:\n<ul>\n<li>SQL Server 2019 CU8<\/li>\n<li>SQL Server 2017 CU22<\/li>\n<li>SQL Server 2016 SP2 CU15<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2>Ad hoc?<\/h2>\n<ul>\n<li>Not sure whether you have an ad hoc workload? Check out my post on <a href=\"https:\/\/sqlperformance.com\/2019\/05\/sql-plan\/perf-impact-adhoc-workload\">Examining the Performance Impact of an Adhoc Workload<\/a>.<\/li>\n<\/ul>\n<p>If you still have questions about how to configure Query Store or what to watch out for, please leave a comment!\u00a0 I\u2019m happy to share as much information and experience as possible.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I\u2019m a huge fan of Query Store, which regular readers may know, but there&#8217;s a need to write a bit more about Query Store best practices.\u00a0 This isn\u2019t a \u201cyou must use this feature\u201d post, this is a \u201chere is what you must know if you want to use this feature\u201d post. I have a [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[15,46],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Query Store Best Practices - Erin Stellato<\/title>\n<meta name=\"description\" content=\"Query Store is a fantastic tool for troubleshooting and addressing performance issues in SQL Server, but proper configuration is essential.\" \/>\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-best-practices\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Query Store Best Practices - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"Query Store is a fantastic tool for troubleshooting and addressing performance issues in SQL Server, but proper configuration is essential.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-best-practices\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2019-06-25T12:28:37+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-11-18T21:31:27+00:00\" \/>\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=\"3 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-best-practices\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-best-practices\/\",\"name\":\"Query Store Best Practices - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2019-06-25T12:28:37+00:00\",\"dateModified\":\"2020-11-18T21:31:27+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"Query Store is a fantastic tool for troubleshooting and addressing performance issues in SQL Server, but proper configuration is essential.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-best-practices\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-best-practices\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-best-practices\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Query Store Best Practices\"}]},{\"@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 Best Practices - Erin Stellato","description":"Query Store is a fantastic tool for troubleshooting and addressing performance issues in SQL Server, but proper configuration is essential.","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-best-practices\/","og_locale":"en_US","og_type":"article","og_title":"Query Store Best Practices - Erin Stellato","og_description":"Query Store is a fantastic tool for troubleshooting and addressing performance issues in SQL Server, but proper configuration is essential.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-best-practices\/","og_site_name":"Erin Stellato","article_published_time":"2019-06-25T12:28:37+00:00","article_modified_time":"2020-11-18T21:31:27+00:00","author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-best-practices\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-best-practices\/","name":"Query Store Best Practices - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2019-06-25T12:28:37+00:00","dateModified":"2020-11-18T21:31:27+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"Query Store is a fantastic tool for troubleshooting and addressing performance issues in SQL Server, but proper configuration is essential.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-best-practices\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-best-practices\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-best-practices\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Query Store Best Practices"}]},{"@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\/1027"}],"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=1027"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/1027\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=1027"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=1027"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=1027"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}