{"id":1101,"date":"2020-03-10T07:04:32","date_gmt":"2020-03-10T14:04:32","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=1101"},"modified":"2020-03-10T07:04:59","modified_gmt":"2020-03-10T14:04:59","slug":"t-sql-tuesday-124-using-query-store-and-why-you-might-not-be-yet","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-124-using-query-store-and-why-you-might-not-be-yet\/","title":{"rendered":"T-SQL Tuesday #124: Using Query Store (and why you might not be…yet)"},"content":{"rendered":"
\"T-SQL<\/a>
T-SQL Tuesday<\/figcaption><\/figure>\n

 <\/p>\n

This month\u2019s T-SQL Tuesday topic is courtesy of Tracy Boggiano<\/a> and it\u2019s related to using Query Store!\u00a0 Tracy asked people to write about their experience adopting Query Store<\/a> (or not, if you haven\u2019t tried it yet or have had issues). I have so much to share\u2026 😊<\/p>\n

I can spend a full day talking about this favorite feature of mine<\/a>, but will do my best to succinctly summarize the usage patterns I\u2019ve seen with customers.<\/p>\n

Successful Adoption<\/h2>\n

We have several customers that turned Query Store on the moment they upgraded to SQL Server 2016 or higher and were off and running. We have one that uses Enterprise Edition and also uses Automatic Plan Correction<\/a>, and there are some plans that we force manually as well.\u00a0 Those customers have parameterized queries and procedure-based workloads.<\/p>\n

Delayed Adoption<\/h2>\n

There are a few customers that upgraded to SQL Server 2016 or higher and had concerns about Query Store. In one case they already had a third-party monitoring tool in place<\/a> and didn\u2019t think they needed it. Other customers were very hesitant to make changes to the environment, and wanted to do testing with it – after the upgrade – to make sure there were no adverse effects. In all cases, the customers finally implemented Query Store, with success.<\/p>\n

Successful Adoption\u2026but not without some struggles<\/h2>\n

Last fall we had a previous customer reach out for help after they had to wait 45 minutes for a database to come online after a server reboot. The database queries were blocked by QDS_LOADDB waits<\/a>. There were three things in play here \u2013 the first was that they had CAPTURE_MODE set to ALL, and it should be AUTO. Second, they didn\u2019t have trace flag 7752<\/a> enabled (the behavior of which is now the default in SQL Server 2019). And the third was that their Query Store was 100GB in size. The workload was fairly ad hoc, so these three things together caused the problem initially described. They implemented the TF, made multiple changes to the settings<\/a> (set CAPTURE_MODE to AUTO, changed MAX_STORAGE_SIZE_MB to 10GB, decreased CLEANUP_POLICY to 3 days), and then Query Store was usable for them.<\/p>\n

Failed Adoption<\/h2>\n

Lastly, there are a few customers that have been unable to implement Query Store because of their extremely ad hoc workload<\/a>. For one customer, it was very frustrating because they benefited from plan forcing. At the time, they were running the latest version of SQL Server 2017 but ran into the bug where the transaction log would fill up<\/a> (fixed in 2017 CU16 and 2016 SP2 CU8). Even after applying this CU and having optimal settings, they were still unable to use Query Store because of their workload.<\/p>\n

Query Store is *still* a possibility<\/h2>\n

For anyone with an ad hoc workload that is at the point of dismissing Query Store, I recommend that you verify you are following best practices<\/a>, and it might be necessary to look at SQL Server 2019<\/a> or Azure SQL Database. There is new option, CUSTOM, for QUERY_CAPTURE_MODE<\/a> that allow you to control exactly what queries are captured based on execution frequency, compile CPU, or execution CPU. Further, the SQL Server team continues to actively evolve the Query Store feature and address issues as they are found. If this is a feature you want to use, and you are running into a problem, you need to open a User Voice item<\/a>.\u00a0 The SQL Server team can only fix the problems and limitations they know about.<\/p>\n","protected":false},"excerpt":{"rendered":"

  This month\u2019s T-SQL Tuesday topic is courtesy of Tracy Boggiano and it\u2019s related to using Query Store!\u00a0 Tracy asked people to write about their experience adopting Query Store (or not, if you haven\u2019t tried it yet or have had issues). I have so much to share\u2026 😊 I can spend a full day talking […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[46,19],"tags":[],"yoast_head":"\nT-SQL Tuesday #124: Using Query Store (and why you might not be...yet) - Erin Stellato<\/title>\n<meta name=\"description\" content=\"For those interested in using Query Store but have heard conflicting information, the problems and successes shared here should help.\" \/>\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\/t-sql-tuesday-124-using-query-store-and-why-you-might-not-be-yet\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"T-SQL Tuesday #124: Using Query Store (and why you might not be...yet) - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"For those interested in using Query Store but have heard conflicting information, the problems and successes shared here should help.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-124-using-query-store-and-why-you-might-not-be-yet\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2020-03-10T14:04:32+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-03-10T14:04:59+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/02\/t-sql-tuesday-350x350-300x300.png\" \/>\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\/t-sql-tuesday-124-using-query-store-and-why-you-might-not-be-yet\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-124-using-query-store-and-why-you-might-not-be-yet\/\",\"name\":\"T-SQL Tuesday #124: Using Query Store (and why you might not be...yet) - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2020-03-10T14:04:32+00:00\",\"dateModified\":\"2020-03-10T14:04:59+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"For those interested in using Query Store but have heard conflicting information, the problems and successes shared here should help.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-124-using-query-store-and-why-you-might-not-be-yet\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-124-using-query-store-and-why-you-might-not-be-yet\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-124-using-query-store-and-why-you-might-not-be-yet\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"T-SQL Tuesday #124: Using Query Store (and why you might not be…yet)\"}]},{\"@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":"T-SQL Tuesday #124: Using Query Store (and why you might not be...yet) - Erin Stellato","description":"For those interested in using Query Store but have heard conflicting information, the problems and successes shared here should help.","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\/t-sql-tuesday-124-using-query-store-and-why-you-might-not-be-yet\/","og_locale":"en_US","og_type":"article","og_title":"T-SQL Tuesday #124: Using Query Store (and why you might not be...yet) - Erin Stellato","og_description":"For those interested in using Query Store but have heard conflicting information, the problems and successes shared here should help.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-124-using-query-store-and-why-you-might-not-be-yet\/","og_site_name":"Erin Stellato","article_published_time":"2020-03-10T14:04:32+00:00","article_modified_time":"2020-03-10T14:04:59+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/02\/t-sql-tuesday-350x350-300x300.png"}],"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\/t-sql-tuesday-124-using-query-store-and-why-you-might-not-be-yet\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-124-using-query-store-and-why-you-might-not-be-yet\/","name":"T-SQL Tuesday #124: Using Query Store (and why you might not be...yet) - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2020-03-10T14:04:32+00:00","dateModified":"2020-03-10T14:04:59+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"For those interested in using Query Store but have heard conflicting information, the problems and successes shared here should help.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-124-using-query-store-and-why-you-might-not-be-yet\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-124-using-query-store-and-why-you-might-not-be-yet\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-124-using-query-store-and-why-you-might-not-be-yet\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"T-SQL Tuesday #124: Using Query Store (and why you might not be…yet)"}]},{"@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\/1101"}],"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=1101"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/1101\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=1101"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=1101"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=1101"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}