{"id":1018,"date":"2019-05-22T19:07:11","date_gmt":"2019-05-23T02:07:11","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=1018"},"modified":"2019-05-22T19:07:59","modified_gmt":"2019-05-23T02:07:59","slug":"query-store-in-sql-server-2019-ctp-3-0","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-in-sql-server-2019-ctp-3-0\/","title":{"rendered":"Query Store in SQL Server 2019 (CTP 3.0)"},"content":{"rendered":"<p>Friends, <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/sql-server\/what-s-new-in-sql-server-ver15?view=sqlallproducts-allversions#ctp-30-may-2019\">CTP 3.0 dropped today<\/a>, and it includes some changes for Query Store in SQL Server 2019!\u00a0 I am so excited!!\u00a0 I&#8217;ve downloaded it and have WideWorldImporters installed and have a lot of testing planned, but if you&#8217;re impatient, guess what?\u00a0 The documentation is already updated!\u00a0 If you check out the <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/statements\/alter-database-transact-sql-set-options?view=sql-server-2017\">ALTER DATABASE SET page<\/a> you will see that Query Store now has a new option for QUERY_CAPTURE_MODE: <strong>CUSTOM<\/strong>.\u00a0 For those of you with <a href=\"https:\/\/sqlperformance.com\/2019\/05\/sql-plan\/perf-impact-adhoc-workload\">ad hoc workloads<\/a>, <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-overhead\/\">this will help<\/a>.<\/p>\n<p>Remember that with QUERY_CAPTURE_MODE, the default for SQL Server 2016 and SQL Server 2017 is ALL, and for Azure SQL Database it is AUTO.\u00a0 I <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-settings\/\">previously recommended<\/a> that everyone use AUTO, as this means you do not capture queries that are insignificant (thresholds determined by Microsoft and not publicly documented, but it&#8217;s at least 3 executions and some amount of of CPU).<\/p>\n<p>The CUSTOM option allows <em>you <\/em>determine what queries are captured based on:<\/p>\n<ul>\n<li>EXECUTION_COUNT<\/li>\n<li>TOTAL_COMPILE_CPU_TIME_MS<\/li>\n<li>TOTAL_EXECUTION_CPU_TIME_MS<\/li>\n<\/ul>\n<p>These three options operate in an OR manner.\u00a0 For example, if I set the values as follows:<\/p>\n<ul>\n<li>EXECUTION_COUNT = 100<\/li>\n<li>TOTAL_COMPILE_CPU_TIME_MS = 60000<\/li>\n<li>TOTAL_EXECUTION_CPU_TIME_MS = 60000<\/li>\n<\/ul>\n<p>A query would only be captured in Query Store if it executed <em>at least 1<\/em>00 times, if the total compile time for the query was <em>at least<\/em> 60 seconds, <span style=\"text-decoration: underline;\"><strong>or<\/strong><\/span> if the total execution time (for all executions) was <em>at least<\/em> 60 seconds.<\/p>\n<p>You also control the interval across which those options are tracked via the STALE_CAPTURE_POLICY_THRESHOLD setting.\u00a0 This can be as low as 1 hour and as high as 7 days.\u00a0 If I set it to 1 hour, then if the query did not exceed any of the thresholds within the 1 hour time frame, it wouldn&#8217;t be captured.\u00a0 Ultimately, you can control what Query Store captures based on executions, compile time, or duration, which will help keep the size of the Query Store data much more manageable.<\/p>\n<p>In addition to these new options, the default values for two settings changed in SQL Server 2019.\u00a0 Specifically:<\/p>\n<ul>\n<li>MAX_STORAGE_SIZE_MB increased from 100MB to <strong>1000MB<\/strong><\/li>\n<li>QUERY_STORE_CAPTURE_MODE changed from ALL to <strong>AUTO<\/strong><\/li>\n<\/ul>\n<p>I have a fair bit of testing I want to do to see these settings in action, but I wanted to give you all a heads up if you&#8217;re looking to upgrade to 2019 when it&#8217;s released, or if you have had a less-than-positive experience with Query Store and wonder if it&#8217;s something you&#8217;ll ever be able to use.\u00a0 The answer is yes, and this is a step in the direction to make it happen.\u00a0 More to come!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Friends, CTP 3.0 dropped today, and it includes some changes for Query Store in SQL Server 2019!\u00a0 I am so excited!!\u00a0 I&#8217;ve downloaded it and have WideWorldImporters installed and have a lot of testing planned, but if you&#8217;re impatient, guess what?\u00a0 The documentation is already updated!\u00a0 If you check out the ALTER DATABASE SET page [&hellip;]<\/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":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Query Store in SQL Server 2019 (CTP 3.0) - Erin Stellato<\/title>\n<meta name=\"description\" content=\"The settings for Query Store in SQL Server 2019 have changed - both in terms of the defaults and what options are available - read on to see what&#039;s new!\" \/>\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-in-sql-server-2019-ctp-3-0\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Query Store in SQL Server 2019 (CTP 3.0) - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"The settings for Query Store in SQL Server 2019 have changed - both in terms of the defaults and what options are available - read on to see what&#039;s new!\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-in-sql-server-2019-ctp-3-0\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2019-05-23T02:07:11+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-05-23T02:07:59+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=\"2 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-in-sql-server-2019-ctp-3-0\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-in-sql-server-2019-ctp-3-0\/\",\"name\":\"Query Store in SQL Server 2019 (CTP 3.0) - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2019-05-23T02:07:11+00:00\",\"dateModified\":\"2019-05-23T02:07:59+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"The settings for Query Store in SQL Server 2019 have changed - both in terms of the defaults and what options are available - read on to see what's new!\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-in-sql-server-2019-ctp-3-0\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-in-sql-server-2019-ctp-3-0\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-in-sql-server-2019-ctp-3-0\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Query Store in SQL Server 2019 (CTP 3.0)\"}]},{\"@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 in SQL Server 2019 (CTP 3.0) - Erin Stellato","description":"The settings for Query Store in SQL Server 2019 have changed - both in terms of the defaults and what options are available - read on to see what's new!","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-in-sql-server-2019-ctp-3-0\/","og_locale":"en_US","og_type":"article","og_title":"Query Store in SQL Server 2019 (CTP 3.0) - Erin Stellato","og_description":"The settings for Query Store in SQL Server 2019 have changed - both in terms of the defaults and what options are available - read on to see what's new!","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-in-sql-server-2019-ctp-3-0\/","og_site_name":"Erin Stellato","article_published_time":"2019-05-23T02:07:11+00:00","article_modified_time":"2019-05-23T02:07:59+00:00","author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-in-sql-server-2019-ctp-3-0\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-in-sql-server-2019-ctp-3-0\/","name":"Query Store in SQL Server 2019 (CTP 3.0) - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2019-05-23T02:07:11+00:00","dateModified":"2019-05-23T02:07:59+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"The settings for Query Store in SQL Server 2019 have changed - both in terms of the defaults and what options are available - read on to see what's new!","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-in-sql-server-2019-ctp-3-0\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-in-sql-server-2019-ctp-3-0\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-in-sql-server-2019-ctp-3-0\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Query Store in SQL Server 2019 (CTP 3.0)"}]},{"@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\/1018"}],"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=1018"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/1018\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=1018"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=1018"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=1018"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}