{"id":969,"date":"2018-11-06T11:21:52","date_gmt":"2018-11-06T19:21:52","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=969"},"modified":"2021-05-18T07:44:40","modified_gmt":"2021-05-18T14:44:40","slug":"query-store-performance-overhead","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-overhead\/","title":{"rendered":"Query Store Performance Overhead: What you need to know"},"content":{"rendered":"\t\t<div data-elementor-type=\"wp-post\" data-elementor-id=\"969\" class=\"elementor elementor-969\" data-elementor-post-type=\"post\">\n\t\t\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-443216fd elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"443216fd\" data-element_type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-1943cb88\" data-id=\"1943cb88\" data-element_type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-6a41a7e7 elementor-widget elementor-widget-text-editor\" data-id=\"6a41a7e7\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<blockquote>\n<p><em><strong>Edit:<\/strong> November 12, 2020 &#8211; Updated information about Query Store performance overhead can be found in <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-updated\/\">this post<\/a>.<\/em><\/p>\n<\/blockquote>\n<p>\u201cWhat is the performance overhead of enabling Query Store?\u201d<\/p>\n<p>I get asked this question almost every time I present on a topic related to Query Store.&nbsp; What people are really asking is \u201cDoes enabling Query Store affect the performance of <strong><em>my<\/em><\/strong> queries?\u201d&nbsp; Where \u201cmy queries\u201d are user queries, queries from the application, etc.<\/p>\n<h2>The short answer:<\/h2>\n<ul>\n<li>The majority of workloads won\u2019t see an impact on system performance\n<ul>\n<li>Will there be an increase in resource use (CPU, memory)?&nbsp; Yes.<\/li>\n<li>Is there a &#8220;magic number&#8221; to use to figure out Query Store performance and the increase in resource use?&nbsp; No, it will depend on the type of workload.&nbsp; Keep reading.<\/li>\n<\/ul>\n<\/li>\n<li>An impact on system performance can be seen with ad-hoc workloads (think Entity Framework, NHibernate), <em>but I still think it\u2019s worth enabling<\/em>. With an ad-hoc workload there are additional factors to consider when using Query Store.<\/li>\n<li>You should be running the latest version CU for SQL Server 2017 and latest CU for SQL Server 2016 SP2 to get all performance-related improvements Microsoft has implemented specific to Query Store<\/li>\n<\/ul>\n<h2>The long answer\u2026<\/h2>\n<p>One reason the SQL Server 2016 release was such a solid release was that it was data driven.&nbsp; \u201cData Driven\u201d was frequently used in Microsoft marketing materials for 2016, but it wasn\u2019t hype; it was true.&nbsp; At the time of the SQL Server release, Azure SQL Database had been in existence for over two years and Microsoft had been capturing telemetry and using that data to understand how features were being used, as well as improve existing features.<\/p>\n<p>One of the features that benefited most from the insight provided by the telemetry data was Query Store, which was originally released in private preview for Azure SQL Database in early 2015.&nbsp; As Query Store was implemented for more databases, the information captured was used to enhance its functionality and improve its performance.&nbsp; Query Store was made publicly available in late 2015, and included in the SQL Server 2016 release.&nbsp; The telemetry data was invaluable to Microsoft\u2019s developers as they prepared Query Store for release, but the variety in size and workload that exist in on-premises solutions was not accurately represented.&nbsp; Much of this was due to limitations in Azure tiers at the time and the limited number (comparatively) of companies that had embraced using a cloud solution.<\/p>\n<p>Thus, while the initial internal thresholds for Query Store were determined based upon Azure SQL Database solutions and appropriate for most on-prem systems, they were not fully suited to <em>every variation<\/em> of an on-prem solution.&nbsp; This is not atypical \u2013 it\u2019s extremely difficult to develop software that accommodates every single workload in the world both in the cloud and on-prem.<\/p>\n<p>This history is relevant when people ask about solution performance and Query Store.<\/p>\n<p>First, understand that there are differences in how Query Store works in Azure SQL Database compared to on-prem.&nbsp; A good example is the amount of space that you can allocate to Query Store within the user database (MAX_STORAGE_SIZE_MB). In Azure SQL Database the maximum value one can set for MAX_STORAGE_SIZE_MB is 10GB.&nbsp; There is no a limit for SQL Server 2016 or 2017.&nbsp; As a result of this limitation for Azure SQL DB, the amount of data that Query Store has to manage can be significantly less than what we see for an on-prem solution.&nbsp; There are many production environments with a Query Store that is 10GB or less in size, but I know of Query Stores that are 200-250GB in size on disk, which typically indicates an anti-pattern with the workload.<\/p>\n<p>Separate from storing the Query Store in the user database, <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-in-memory\/\">data is also held in different memory buffers<\/a> (e.g. query hash map, runtime statistics cache store).&nbsp; Data is inserted into these memory buffers for new queries, updated for previously-executed queries, and while data is flushed to disk regularly, it is expected that data continuously resides in these buffers.&nbsp; The data for the query hash map is consistent, but the volume of data in the runtime statistics cache store fluctuates depending on the workload.<\/p>\n<p>There are multiple ways to characterize a workload, but in the case of Query Store, we\u2019re most interested in the number of unique queries generated.&nbsp; We tend to characterize workloads with a high number of unique queries as ad-hoc \u2013 those that use Entity Framework or NHibernate, for example.&nbsp; But there are other variations, such as multi-versioned tables, which also create a significant number of unique queries.&nbsp; To be clear, the following are unique queries:<\/p>\n<pre>SELECT <br>   e.FirstName, <br>   e.LastName, <br>   d.Name\nFROM dbo.Employees e\nJOIN dbo.Department d\n   ON e.department_id = d.department_id\nWHERE e.LastName = \u2018Harbaugh\u2019;<br>\n\nSELECT <br>   e.FirstName, <br>   e.LastName, <br>   d.Name\nFROM dbo.Employees e\nJOIN dbo.Department d <br>   ON e.department_id = d.department_id<br>WHERE e.LastName = \u2018Winovich\u2019;<br>\n\nSELECT <br>   e.FirstName, <br>   e.LastName, <br>   d.Name\nFROM dbo.Employees e\nJOIN dbo.Department d\n   ON e.department_id = d.department_id\nWHERE e.lastname = \u2018Carr\u2019;\n<\/pre>\n<p>Just like the plan cache, Query Store identifies each of the above queries as unique (even though they all have the same query_hash) based on the text, and assigns each its own query_text_id in Query Store.&nbsp; This query_text_id, combined with context_settings_id, object_id, batch_sql_handle, and query_parameterization_type create a unique hash for each query which Query Store uses internally, and is stored in buffers in memory, along with the runtime statistics for each unique hash.&nbsp; The more unique query texts in a workload, the more overhead there may be to manage the data.<\/p>\n<p>Understand that if you have an ad hoc workload, you already have a system that is prone to performance issues due to high compiles, plan cache bloat, and variability in query performance across queries that are textually the same in terms of query_hash, but have different literal values (as shown above).&nbsp; For an ad-hoc workload that is also high volume (high number of batch requests\/sec), when you enable Query Store it can appear that a performance problem has been introduced.&nbsp; It is tempting to look at any decrease in performance as a problem with Query Store.&nbsp; However, it\u2019s a function of the type of the workload and simply the cost of doing business for said workload.&nbsp; If you want to capture Query Store data about an ad-hoc workload (to then identify query patterns and address them) then you\u2019ll have to expect and plan for the overhead associated with it.<\/p>\n<p>You can control, to a small degree, the number of queries captured using the QUERY_CAPTURE_MODE setting. &nbsp;The default value of ALL means that every single executed will be captured. The value of AUTO means that only queries that exceed a threshold (set internally by Microsoft) will be captured.&nbsp; As noted in my <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-settings\/\">Query Store Settings post<\/a>, AUTO is the recommendation for a production environment, particularly one that is ad-hoc.<\/p>\n<p>The SQL Server team made performance-related improvements in Query Store in the SQL Server 2017 release, and these were back-ported to SQL Server 2016 SP2.&nbsp; There have been a few additional fixes in SQL Server 2017, such as <a href=\"https:\/\/support.microsoft.com\/en-us\/help\/4461562\/transactions-and-log-truncation-may-be-blocked-when-using-query-store\" target=\"_blank\" rel=\"noopener noreferrer\">this one<\/a>, in CU11.&nbsp; I know of a couple people who have run into this issue, so if you\u2019re on SQL Server 2017 and using Query Store, I definitely recommend applying the latest CU.<\/p>\n<h2>Final thoughts<\/h2>\n<p>Now we can answer, &#8220;Can enabling Query Store make some of your queries run slower?&#8221;&nbsp; It depends on your workload, your version of SQL Server, and the settings you have enabled.&nbsp; For those folks with a mostly procedure-type workload, I haven\u2019t seen many issues.&nbsp; For those with ad-hoc, high volume workloads, you are now aware of the potential overhead and you can plan accordingly.&nbsp; If you\u2019re on the fence about it, enable it during a low-volume time and monitor the system.&nbsp; If you feel there\u2019s a problem, turn it off.&nbsp; But the data gathered on any system can be used to help make that system better, even if you have to do it incrementally.&nbsp; Whether your workload is procedure-based, ad-hoc, or a mix, Query Store is an invaluable resource that can be used to capture query metrics, find queries that perform poorly or execute frequently, and force plans to stabilize query performance.<\/p>&nbsp; <br>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>Edit: November 12, 2020 &#8211; Updated information about Query Store performance overhead can be found in this post. \u201cWhat is the performance overhead of enabling Query Store?\u201d I get asked this question almost every time I present on a topic related to Query Store.&nbsp; What people are really asking is \u201cDoes enabling Query Store affect [&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 Performance Overhead: What you need to know - Erin Stellato<\/title>\n<meta name=\"description\" content=\"I often get asked about the Query Store performance overhead for a system, and what it will be, but there are multiple factors to consider.\" \/>\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-overhead\/\" \/>\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: What you need to know - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"I often get asked about the Query Store performance overhead for a system, and what it will be, but there are multiple factors to consider.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-overhead\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2018-11-06T19:21:52+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-05-18T14:44:40+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=\"7 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-overhead\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-overhead\/\",\"name\":\"Query Store Performance Overhead: What you need to know - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2018-11-06T19:21:52+00:00\",\"dateModified\":\"2021-05-18T14:44:40+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"I often get asked about the Query Store performance overhead for a system, and what it will be, but there are multiple factors to consider.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-overhead\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-overhead\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-overhead\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Query Store Performance Overhead: What you need to know\"}]},{\"@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: What you need to know - Erin Stellato","description":"I often get asked about the Query Store performance overhead for a system, and what it will be, but there are multiple factors to consider.","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-overhead\/","og_locale":"en_US","og_type":"article","og_title":"Query Store Performance Overhead: What you need to know - Erin Stellato","og_description":"I often get asked about the Query Store performance overhead for a system, and what it will be, but there are multiple factors to consider.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-overhead\/","og_site_name":"Erin Stellato","article_published_time":"2018-11-06T19:21:52+00:00","article_modified_time":"2021-05-18T14:44:40+00:00","author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-overhead\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-overhead\/","name":"Query Store Performance Overhead: What you need to know - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2018-11-06T19:21:52+00:00","dateModified":"2021-05-18T14:44:40+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"I often get asked about the Query Store performance overhead for a system, and what it will be, but there are multiple factors to consider.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-overhead\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-overhead\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-performance-overhead\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Query Store Performance Overhead: What you need to know"}]},{"@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\/969"}],"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=969"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/969\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=969"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=969"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=969"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}