{"id":1179,"date":"2020-10-16T06:24:42","date_gmt":"2020-10-16T13:24:42","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=1179"},"modified":"2020-10-16T06:24:44","modified_gmt":"2020-10-16T13:24:44","slug":"how-to-turn-off-query-store-in-an-emergency","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/how-to-turn-off-query-store-in-an-emergency\/","title":{"rendered":"How to Turn Off Query Store&#8230;in an emergency"},"content":{"rendered":"\n<p>Have you ever tried to turn off Query Store when there was an issue, and you thought the problem <em>might<\/em> be related to Query Store, and the ALTER DATABASE statement was blocked?&nbsp; And then you couldn\u2019t do anything but wait?&nbsp; Me too.&nbsp; Imagine my excitement when I discovered that the SQL Server team snuck a helpful back door into <em>ALL<\/em> versions for which Query Store is supported.&nbsp;<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Wait, what?<\/h2>\n\n\n\n<p>If you check the documentation for <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/statements\/alter-database-transact-sql-set-options?view=sql-server-ver15\">ALTER DATABASE SET<\/a> and review the QUERY_STORE section, you\u2019ll see that OFF now includes the optional FORCED parameter.<\/p>\n\n\n\n<p>If you are running:<\/p>\n\n\n\n<ul><li>SQL Server 2016 SP2 CU14+<\/li><li>SQL Server 2017 C21+<\/li><li>or SQL Server 2019 CU6+<\/li><\/ul>\n\n\n\n<p>you have the ability to forcibly disable Query Store, even if it\u2019s in the middle of something like flushing data to disk or purging data.\u00a0 The FORCED option stops all related background tasks that are currently running, and skips any flushing that would occur (to persist data that was in memory to disk). Essentially, Query Store is shut down as quickly as possible.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">How to disable Query Store<\/h2>\n\n\n\n<p>Typically, if you wanted to turn off Query Store, you would run:<\/p>\n\n\n\n<p><code>ALTER DATABASE [DBName] SET QUERY_STORE = OFF<\/code><\/p>\n\n\n\n<p>In working with a customer who was running SQL Server 2017, their 100GB Query Store took almost 45 minutes to load (side note: that is way beyond the <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-settings\">recommended 10GB maximum<\/a>).\u00a0 When we tried to run the statement above to disable Query Store, it was blocked by a thread with a QDS_LOAD wait type (loading the data into memory), because <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-trace-flags\/\">Trace Flag 7752<\/a> was not enabled.\u00a0<\/p>\n\n\n\n<p>For this scenario, or any other where we want this feature turned off immediately, we can now run:<\/p>\n\n\n\n<p><code>ALTER DATABASE [DBName] SET QUERY_STORE = OFF (FORCED)<\/code><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Parting thoughts<\/h2>\n\n\n\n<p>Be aware that if you forcibly turn off Query Store, any data that is in memory that has not been flushed to disk will be lost.  Depending on the issue, that data may have been helpful when troubleshooting.<\/p>\n\n\n\n<p>I don\u2019t expect this option to be used frequently in SQL Server 2019 because of numerous improvements and the new CUSTOM capture mode (and because Trace Flag 7752 functionality is enabled by default).  But I think this option will be helpful for anyone running SQL Server 2016 or SQL Server 2017 who might not have their CAPTURE_MODE properly set, or who might have a high-volume ad hoc workload that isn\u2019t suitable for Query Store unless the CUSTOM capture mode can be used.  <\/p>\n","protected":false},"excerpt":{"rendered":"<p>Have you ever tried to turn off Query Store when there was an issue, and you thought the problem might be related to Query Store, and the ALTER DATABASE statement was blocked?&nbsp; And then you couldn\u2019t do anything but wait?&nbsp; Me too.&nbsp; Imagine my excitement when I discovered that the SQL Server team snuck a [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":1180,"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>How to Turn Off Query Store...in an emergency - Erin Stellato<\/title>\n<meta name=\"description\" content=\"Intermittently there is a need to immediatly turn off Query Store, and that capability now exists in the most recent versions of SQL Server.\" \/>\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\/how-to-turn-off-query-store-in-an-emergency\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Turn Off Query Store...in an emergency - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"Intermittently there is a need to immediatly turn off Query Store, and that capability now exists in the most recent versions of SQL Server.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/how-to-turn-off-query-store-in-an-emergency\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2020-10-16T13:24:42+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-10-16T13:24:44+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/10\/QS-Force-OFF.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"684\" \/>\n\t<meta property=\"og:image:height\" content=\"74\" \/>\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=\"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\/how-to-turn-off-query-store-in-an-emergency\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/how-to-turn-off-query-store-in-an-emergency\/\",\"name\":\"How to Turn Off Query Store...in an emergency - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2020-10-16T13:24:42+00:00\",\"dateModified\":\"2020-10-16T13:24:44+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"Intermittently there is a need to immediatly turn off Query Store, and that capability now exists in the most recent versions of SQL Server.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/how-to-turn-off-query-store-in-an-emergency\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/how-to-turn-off-query-store-in-an-emergency\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/how-to-turn-off-query-store-in-an-emergency\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Turn Off Query Store&#8230;in an emergency\"}]},{\"@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":"How to Turn Off Query Store...in an emergency - Erin Stellato","description":"Intermittently there is a need to immediatly turn off Query Store, and that capability now exists in the most recent versions of SQL Server.","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\/how-to-turn-off-query-store-in-an-emergency\/","og_locale":"en_US","og_type":"article","og_title":"How to Turn Off Query Store...in an emergency - Erin Stellato","og_description":"Intermittently there is a need to immediatly turn off Query Store, and that capability now exists in the most recent versions of SQL Server.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/how-to-turn-off-query-store-in-an-emergency\/","og_site_name":"Erin Stellato","article_published_time":"2020-10-16T13:24:42+00:00","article_modified_time":"2020-10-16T13:24:44+00:00","og_image":[{"width":684,"height":74,"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/10\/QS-Force-OFF.jpg","type":"image\/jpeg"}],"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\/how-to-turn-off-query-store-in-an-emergency\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/how-to-turn-off-query-store-in-an-emergency\/","name":"How to Turn Off Query Store...in an emergency - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2020-10-16T13:24:42+00:00","dateModified":"2020-10-16T13:24:44+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"Intermittently there is a need to immediatly turn off Query Store, and that capability now exists in the most recent versions of SQL Server.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/how-to-turn-off-query-store-in-an-emergency\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/how-to-turn-off-query-store-in-an-emergency\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/how-to-turn-off-query-store-in-an-emergency\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"How to Turn Off Query Store&#8230;in an emergency"}]},{"@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\/1179"}],"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=1179"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/1179\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media\/1180"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=1179"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=1179"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=1179"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}