{"id":1182,"date":"2020-11-12T06:00:00","date_gmt":"2020-11-12T14:00:00","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=1182"},"modified":"2020-11-16T10:49:29","modified_gmt":"2020-11-16T18:49:29","slug":"query-store-performance-updated","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-updated\/","title":{"rendered":"Query Store Performance Overhead&#8230;Updated"},"content":{"rendered":"\n<p>I wrote the original <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-overhead\/\">Query Store performance overhead<\/a> post just over two years ago, and just like the data in your database keeps changing, so does SQL Server.&nbsp; However, the question, \u201cWhat is the performance overhead of enabling Query Store?\u201d is <em>still<\/em> the most frequent question I am asked.&nbsp;<\/p>\n\n\n\n<p>So why am I writing this post?&nbsp; Because there have been many improvements specific to Query Store that have taken the feature to the point where it can support <em>all<\/em> workloads, including those that are ad-hoc.&nbsp; This is a big deal.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Quick history (in case you didn\u2019t read the initial post)<\/h3>\n\n\n\n<p>When Query Store was originally released in Azure, the Microsoft engineers had the benefit of terabytes of telemetry to help them understand what was working, and what wasn\u2019t.&nbsp; This allowed them to make changes to the feature so that when it was released in SQL Server 2016, it was fully-functioning and reliable.<\/p>\n\n\n\n<p>However, not every workload that runs in an on-premises environment also runs in Azure SQL Database.&nbsp; As a result, many interesting problems were exposed when customers beginning turning on Query Store in their environment.&nbsp; Many users have been critical of these initial limitations, but I\u2019ve been impressed with the commitment of the SQL Server team to the Query Store feature.&nbsp; In addition to making Query Store more robust throughout the SQL Server 2017 and SQL Server 2019 releases, the team has continued to add functionality.&nbsp; In case you missed it, during yesterday\u2019s PASS Summit keynote, Bob Ward and Conor Cunningham debuted the ability to add hints to queries via Query Store.&nbsp; That\u2019s big.&nbsp; Query Store is feature that\u2019s here to stay.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">What\u2019s new in Query Store?<\/h3>\n\n\n\n<p>There are multiple fixes and improvements since the initial SQL Server 2016 release, which include:<\/p>\n\n\n\n<ul><li>Internal memory limits<\/li><li>Smaller transactions for background flushes of data<\/li><li>Limiting the number of rows deleted as part of Query Store\u2019s cleanup mechanism<\/li><li>The ability to customize the capture settings<\/li><\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Performance details<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Internal memory limits<\/strong><\/h4>\n\n\n\n<p>The most recent CU for each major release that supports Query Store limits the amount of internal memory used by Query Store at both the database and instance level.&nbsp; I\u2019ve talked about the memory that Query Store uses previously, and this limitation prevents the Query Store memory overhead from growing beyond a limit relative to the total available server memory.&nbsp; From the <a href=\"https:\/\/support.microsoft.com\/en-us\/help\/4577194\/cumulative-update-8-for-sql-server-2019\">documentation<\/a>:<\/p>\n\n\n\n<p><img fetchpriority=\"high\" decoding=\"async\" width=\"580\" height=\"137\" class=\"wp-image-1183\" style=\"width: 550px;\" src=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/11\/QSmemory.jpg\" alt=\"Query Store scalability improvement\" srcset=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/11\/QSmemory.jpg 580w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/11\/QSmemory-300x71.jpg 300w\" sizes=\"(max-width: 580px) 100vw, 580px\" \/><\/p>\n\n\n\n<p>The purpose of these memory limits is to prevent other performance issues such as high waits, memory pressure, and locking contention that could be exposed through the use of Query Store for extremely ad hoc workloads.&nbsp; Without limits, if the memory used by Query Store grows by too large, it has to continually try and catch up with the workload, eventually causing bigger problems.&nbsp; With memory limits in place, Query Store will back off when the internally managed limits are reached.&nbsp; This ensures that impact on user workload remains minimal.&nbsp; Keeping the overhead of Query Store use low is a core priority feature, so there was a decision to maintain overall performance even if it means that Query Store temporarily stops capturing queries and their runtime statistics.<\/p>\n\n\n\n<p>If Query Store switches to a READ_ONLY state because memory limitations are encountered, you will see one of the following read_only_reasons in <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-catalog-views\/sys-database-query-store-options-transact-sql?view=sql-server-ver15\">sys.database_query_store_options<\/a>:<\/p>\n\n\n\n<p>131072 &#8211; The number of different statements in Query Store has reached the internal memory limit. Consider removing queries that you do not need or upgrading to a higher service tier to enable transferring Query Store to read-write mode.<\/p>\n\n\n\n<p>262144 &#8211; Size of in-memory items waiting to be persisted on disk has reached the internal memory limit. Query Store will be in read-only mode temporarily until the in-memory items are persisted on disk.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Smaller transactions for background flushes of data<\/strong><\/h4>\n\n\n\n<p>Query Store asynchronously flushes data from memory to disk, and this is a background activity that previously could take an extended period of time, particularly for a high-volume workload.&nbsp; The background flush occurs in smaller transactions, allowing Query Store settings to be altered quickly, without being blocked.&nbsp; In addition, for extremely intensive workloads this could previously become a cycle of never-ending flushes that could become problematic.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Limiting the number of rows deleted as part of Query Store\u2019s cleanup mechanism<\/strong><\/h4>\n\n\n\n<p>Cleanup of the Query Store data occurs regularly, as part of the process to keep a limited amount of data (determined by STALE_QUERY_THRESHOLD_DAYS), and as necessary when the amount of Query Store data gets close to the value set for MAX_STORAGE_SIZE_MB.&nbsp; Limiting the number of rows that are deleted as part of cleanup reduces the impact on the database transaction log.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>The ability to customize the capture settings<\/strong><\/h4>\n\n\n\n<p>SQL Server 2019 introduced a new option for QUERY_CAPTURE_MODE, which is <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-in-sql-server-2019-ctp-3-0\/\">CUSTOM<\/a>.\u00a0 This option provides the ability to set thresholds that control whether queries are captured based on execution count, compile CPU time, or execution CPU time.\u00a0 For all workloads that are ad-hoc, this is the silver bullet we\u2019ve been waiting for the SQL Server team to provide.<\/p>\n\n\n\n<p><img decoding=\"async\" width=\"380\" height=\"163\" class=\"wp-image-1184\" style=\"width: 500px;\" src=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/11\/QS_Custom.jpg\" alt=\"CUSTOM capture mode options\" srcset=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/11\/QS_Custom.jpg 380w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/11\/QS_Custom-300x129.jpg 300w\" sizes=\"(max-width: 380px) 100vw, 380px\" \/><\/p>\n\n\n\n<p>The aforementioned improvements, with the exception of CUSTOM capture mode, exist in the following releases (current as of this writing):<\/p>\n\n\n\n<ul><li>SQL Server 2019 CU8<\/li><li>SQL Server 2017 CU22<\/li><li>SQL Server 2016 SP2 CU15<\/li><\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">What do you need to do?<\/h3>\n\n\n\n<p><em>Upgrade.<\/em><\/p>\n\n\n\n<p>For anyone that does <em>not <\/em>have an ad hoc workload, get to the latest CU of the version you\u2019re running.&nbsp; For anyone <em>with <\/em>an ad hoc workload, SQL Server 2019 CU8+ is where you need to be to get the best Query Store experience and prevent a degradation in performance with it enabled.  <\/p>\n\n\n\n<p>Systems with an ad hoc workload on SQL Server 2019 should use the CUSTOM capture mode.  For more parameterized workloads, AUTO can be used.  These options are recommended to help prevent Query Store from becoming read_only, as described above (which is much more likely with capture mode ALL).<\/p>\n\n\n\n<p>Finally, if you find yourself in a state where you believe Query Store is creating a performance problem (perhaps the CUSTOM capture settings were not properly configured), you now have the ability to <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/how-to-turn-off-query-store-in-an-emergency\/\">forcibly turn off Query Store<\/a>.&nbsp; I view this as a last resort\/panic button, but it\u2019s a nice option to have in your back pocket.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I wrote the original Query Store performance overhead post just over two years ago, and just like the data in your database keeps changing, so does SQL Server.&nbsp; However, the question, \u201cWhat is the performance overhead of enabling Query Store?\u201d is still the most frequent question I am asked.&nbsp; So why am I writing this [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":1183,"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 Performance Overhead...Updated - Erin Stellato<\/title>\n<meta name=\"description\" content=\"As the Query Store feature continues to evolve, there are multiple improvements that have addressed concerns around performance.\" \/>\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-performance-updated\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Query Store Performance Overhead...Updated - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"As the Query Store feature continues to evolve, there are multiple improvements that have addressed concerns around performance.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-updated\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2020-11-12T14:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-11-16T18:49:29+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/11\/QSmemory.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"580\" \/>\n\t<meta property=\"og:image:height\" content=\"137\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\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=\"5 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-performance-updated\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-updated\/\",\"name\":\"Query Store Performance Overhead...Updated - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2020-11-12T14:00:00+00:00\",\"dateModified\":\"2020-11-16T18:49:29+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"As the Query Store feature continues to evolve, there are multiple improvements that have addressed concerns around performance.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-updated\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-updated\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-updated\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Query Store Performance Overhead&#8230;Updated\"}]},{\"@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 Performance Overhead...Updated - Erin Stellato","description":"As the Query Store feature continues to evolve, there are multiple improvements that have addressed concerns around performance.","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-performance-updated\/","og_locale":"en_US","og_type":"article","og_title":"Query Store Performance Overhead...Updated - Erin Stellato","og_description":"As the Query Store feature continues to evolve, there are multiple improvements that have addressed concerns around performance.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-updated\/","og_site_name":"Erin Stellato","article_published_time":"2020-11-12T14:00:00+00:00","article_modified_time":"2020-11-16T18:49:29+00:00","og_image":[{"width":580,"height":137,"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/11\/QSmemory.jpg","type":"image\/jpeg"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-updated\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-updated\/","name":"Query Store Performance Overhead...Updated - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2020-11-12T14:00:00+00:00","dateModified":"2020-11-16T18:49:29+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"As the Query Store feature continues to evolve, there are multiple improvements that have addressed concerns around performance.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-updated\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-updated\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-updated\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Query Store Performance Overhead&#8230;Updated"}]},{"@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\/1182"}],"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=1182"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/1182\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media\/1183"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=1182"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=1182"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=1182"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}