{"id":1205,"date":"2021-08-05T08:25:10","date_gmt":"2021-08-05T15:25:10","guid":{"rendered":"https:\/\/www.sqlskills.com\/blogs\/erin\/?p=1205"},"modified":"2021-08-06T00:55:08","modified_gmt":"2021-08-06T07:55:08","slug":"remove-data-from-query-store","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/remove-data-from-query-store\/","title":{"rendered":"Remove Data from Query Store"},"content":{"rendered":"\n<p>There are multiple methods to remove data from Query Store, both manual and automatic, and I\u2019ve been asked about it several times.  I finally took the time to detail the options here.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Automatic<\/h2>\n\n\n\n<p>Automatic removal of data from Query Store is based on configuration, notably the <code>MAX_STORAGE_SIZE_MB<\/code>, <code>CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS)<\/code>, and <code>SIZE_BASED_CLEANUP_MODE<\/code> settings.&nbsp; While these are <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-settings\/\">detailed in another post<\/a>, it\u2019s worth reiterating that ideally, size-based cleanup never kicks in.&nbsp; A database\u2019s Query Store should be sized to accommodate N days\u2019 worth of data, where N is set by <code>CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS)<\/code>.&nbsp; When the size of the Query Store exceeds 90% of <code>MAX_STORAGE_SIZE_MB<\/code>, and if <code>SIZE_BASED_CLEANUP_MODE<\/code> is set to <code>AUTO<\/code>, then clean up will kick in.&nbsp; This algorithm is not efficient, and it runs single-threaded.&nbsp; It looks for queries that are infrequently executed\/less important and deletes those, one by one, until the size is less than 80% of <code>MAX_STORAGE_SIZE_MB<\/code>.&nbsp; Avoid this type of cleanup if at all possible.<\/p>\n\n\n\n<p>Automatic removal also occurs when size-based cleanup kicks in.&nbsp; This process removes data based on date (anything older than the <code>STALE_QUERY_THRESHOLD_DAYS<\/code> value is removed), and while it is more efficient than the aforementioned method, it could be improved.<\/p>\n\n\n\n<p>In these two scenarios, forced plans (and their queries) are <em>not<\/em> removed from Query Store.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Manual<\/h2>\n\n\n\n<p>Data can be removed manually using one of three methods:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nEXEC sp_query_store_remove_query @query_id = ZZZ; \n\nEXEC sp_query_store_remove_plan @plan_id = YYY;\n\nEXEC sp_query_store_reset_exec_stats @plan_id = YYY;\n\nALTER DATABASE &#x5B;DBName] SET QUERY_STORE CLEAR;\n<\/pre><\/div>\n\n\n<p>The sp_query_store_remove_query procedure removes all information (query, plan, runtime statistics) for a query_id.&nbsp; The sp_query_store_remove_plan procedure removes the plan and runtime statistics for a specific plan_id.&nbsp; The third procedure, sp_query_store_reset_exec_stats, removes runtime statistics for the specified plan_id.  The ALTER DATABASE command removes all data from Query Store; it truncates the tables that contain Query Store data, and re-seeds the identity columns (e.g. query_id, plan_id).&nbsp;<\/p>\n\n\n\n<p>If you have a plan that is forced, it cannot be removed with sp_query_store_remove_plan.&nbsp; If you have a query with a forced plan, the query (and related data) cannot be removed with sp_query_store_remove_query.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-style-default\"><a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2021\/08\/QSdelerror.png\"><img fetchpriority=\"high\" decoding=\"async\" width=\"1024\" height=\"118\" src=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2021\/08\/QSdelerror-1024x118.png\" alt=\"Query or plan with provided id cannot be deleted since it has an active forcing policy.\" class=\"wp-image-1206\" srcset=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2021\/08\/QSdelerror-1024x118.png 1024w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2021\/08\/QSdelerror-300x35.png 300w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2021\/08\/QSdelerror-768x89.png 768w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2021\/08\/QSdelerror-1536x177.png 1536w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2021\/08\/QSdelerror.png 1604w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><figcaption>Message when trying to delete a query with a forced plan, or a plan that is forced<\/figcaption><\/figure>\n\n\n\n<p>However, if you use the ALTER DATABASE command to clear the Query Store data, <strong>everything <\/strong>is removed, forced plans and all.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Manual 2.0<\/h2>\n\n\n\n<p>If you need to remove data from Query Store on a regular basis, and want to automate it, you have another option.  The team over at Channel Advisor has created a set of scripts for Query Store, named qdstoolbox, that you can <a href=\"https:\/\/github.com\/channeladvisor\/qdstoolbox\">download from GitHub<\/a>.  Within these scripts is a stored procedure for cleanup (QDSCacheCleanup), which relies on the stored procedures mentioned above, but provides additional flexibility via input parameters.  For example, you can delete queries and plans that have not executed in the last N number of hours, internal SQL Server queries (e.g. <code>UPDATE STATISTICS<\/code>) which are captured in Query Store, or ad hoc queries that are not part of any stored procedure.  As an added benefit, the procedure can display or save a report that details what was removed.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>There are multiple methods to remove data from Query Store, both manual and automatic, and I\u2019ve been asked about it several times. I finally took the time to detail the options here. Automatic Automatic removal of data from Query Store is based on configuration, notably the MAX_STORAGE_SIZE_MB, CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS), and SIZE_BASED_CLEANUP_MODE settings.&nbsp; While these are [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Remove Data from Query Store - Erin Stellato<\/title>\n<meta name=\"description\" content=\"There are multiple methods to remove data from Query Store, both manual and automatic, which should be understood to manage Query Store size.\" \/>\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\/remove-data-from-query-store\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Remove Data from Query Store - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"There are multiple methods to remove data from Query Store, both manual and automatic, which should be understood to manage Query Store size.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/remove-data-from-query-store\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2021-08-05T15:25:10+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-08-06T07:55:08+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2021\/08\/QSdelerror-1024x118.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\/remove-data-from-query-store\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/remove-data-from-query-store\/\",\"name\":\"Remove Data from Query Store - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2021-08-05T15:25:10+00:00\",\"dateModified\":\"2021-08-06T07:55:08+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"There are multiple methods to remove data from Query Store, both manual and automatic, which should be understood to manage Query Store size.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/remove-data-from-query-store\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/remove-data-from-query-store\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/remove-data-from-query-store\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Remove Data from Query Store\"}]},{\"@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":"Remove Data from Query Store - Erin Stellato","description":"There are multiple methods to remove data from Query Store, both manual and automatic, which should be understood to manage Query Store size.","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\/remove-data-from-query-store\/","og_locale":"en_US","og_type":"article","og_title":"Remove Data from Query Store - Erin Stellato","og_description":"There are multiple methods to remove data from Query Store, both manual and automatic, which should be understood to manage Query Store size.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/remove-data-from-query-store\/","og_site_name":"Erin Stellato","article_published_time":"2021-08-05T15:25:10+00:00","article_modified_time":"2021-08-06T07:55:08+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2021\/08\/QSdelerror-1024x118.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\/remove-data-from-query-store\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/remove-data-from-query-store\/","name":"Remove Data from Query Store - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2021-08-05T15:25:10+00:00","dateModified":"2021-08-06T07:55:08+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"There are multiple methods to remove data from Query Store, both manual and automatic, which should be understood to manage Query Store size.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/remove-data-from-query-store\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/remove-data-from-query-store\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/remove-data-from-query-store\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Remove Data from Query Store"}]},{"@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\/1205"}],"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=1205"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/1205\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=1205"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=1205"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=1205"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}