{"id":948,"date":"2018-08-30T11:22:17","date_gmt":"2018-08-30T18:22:17","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=948"},"modified":"2019-03-04T11:00:44","modified_gmt":"2019-03-04T19:00:44","slug":"alter-database-set-query_store-command-is-blocked","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-database-set-query_store-command-is-blocked\/","title":{"rendered":"ALTER DATABASE SET QUERY_STORE command is blocked"},"content":{"rendered":"<p>If you are trying to execute an ALTER DATABASE command to change a Query Store option (e.g. turn it off, change a setting) and it is blocked, take note of the blocking session_id and what that session_id is executing.\u00a0 If you are trying to execute this ALTER command right after a failover or restart, you are probably blocked by the Query Store data loading.<\/p>\n<p>As a reminder, when a database with Query Store enabled starts up, it loads data from the Query Store internal tables into memory (this is an optimization to make specific capabilities of Query Store complete quickly).\u00a0 In some cases this is a small amount of data, in other cases, it\u2019s larger (potentially a few GB), and as such, it can take seconds or minutes to load.\u00a0 I have seen this take over 30 minutes to load for a very large Query Store (over 50GB in size).<\/p>\n<p>Specifically, I was recently working with a customer with an extremely large Query Store.\u00a0 The customer had enabled Trace Flag 7752, which I have <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-trace-flags\/\">written about<\/a>, so that queries were not blocked while Query Store loaded asynchronously.\u00a0 The tricky thing about that load is that there is no way to monitor the progress.\u00a0 You can track when it starts loading and then when it finishes using Extended Events, but there is no progress bar to stare at a on a screen.\u00a0 When trying to execute an ALTER DATABASE &lt;dbname&gt; SET QUERY_STORE statement while the load was occurring, the statement was blocked by a system session that was running the command\u00a0<strong>Query Store ASYN<\/strong>.\u00a0 The ALTER DATABASE &lt;dbname&gt; SET QUERY_STORE command did complete once the Query Store data had been loaded.<\/p>\n<p>If you do not have Trace Flag 7752 enabled, then if you try to execute ALTER DATABASE &lt;dbname&gt; SET QUERY_STORE\u00a0 after a restart or failover you might see the\u00a0QDS_LOADDB wait_type for queries (again, this will depend the size of the Query Store).\u00a0 Again, there is no way to monitor the load, and you will see the same behavior if you try to run ALTER DATABASE &lt;dbname&gt; SET QUERY_STORE: the command will not complete until the Query Store load has completed.<\/p>\n<p>In summary, <em>regardless of whether the Query Store data is loading synchronously or asynchronously<\/em>, you will not be able to execute an ALTER DATABASE &lt;dbname&gt; SET QUERY_STORE statement until the load is complete.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you are trying to execute an ALTER DATABASE command to change a Query Store option (e.g. turn it off, change a setting) and it is blocked, take note of the blocking session_id and what that session_id is executing.\u00a0 If you are trying to execute this ALTER command right after a failover or restart, you [&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>ALTER DATABASE SET QUERY_STORE command is blocked - Erin Stellato<\/title>\n<meta name=\"description\" content=\"An ALTER DATABASE command for Query Store can be blocked when an instance first starts due to the loading of Query Store data.\" \/>\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\/alter-database-set-query_store-command-is-blocked\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"ALTER DATABASE SET QUERY_STORE command is blocked - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"An ALTER DATABASE command for Query Store can be blocked when an instance first starts due to the loading of Query Store data.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-database-set-query_store-command-is-blocked\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-30T18:22:17+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-03-04T19:00:44+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\/alter-database-set-query_store-command-is-blocked\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-database-set-query_store-command-is-blocked\/\",\"name\":\"ALTER DATABASE SET QUERY_STORE command is blocked - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2018-08-30T18:22:17+00:00\",\"dateModified\":\"2019-03-04T19:00:44+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"An ALTER DATABASE command for Query Store can be blocked when an instance first starts due to the loading of Query Store data.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-database-set-query_store-command-is-blocked\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-database-set-query_store-command-is-blocked\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-database-set-query_store-command-is-blocked\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"ALTER DATABASE SET QUERY_STORE command is blocked\"}]},{\"@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":"ALTER DATABASE SET QUERY_STORE command is blocked - Erin Stellato","description":"An ALTER DATABASE command for Query Store can be blocked when an instance first starts due to the loading of Query Store data.","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\/alter-database-set-query_store-command-is-blocked\/","og_locale":"en_US","og_type":"article","og_title":"ALTER DATABASE SET QUERY_STORE command is blocked - Erin Stellato","og_description":"An ALTER DATABASE command for Query Store can be blocked when an instance first starts due to the loading of Query Store data.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-database-set-query_store-command-is-blocked\/","og_site_name":"Erin Stellato","article_published_time":"2018-08-30T18:22:17+00:00","article_modified_time":"2019-03-04T19:00:44+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\/alter-database-set-query_store-command-is-blocked\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-database-set-query_store-command-is-blocked\/","name":"ALTER DATABASE SET QUERY_STORE command is blocked - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2018-08-30T18:22:17+00:00","dateModified":"2019-03-04T19:00:44+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"An ALTER DATABASE command for Query Store can be blocked when an instance first starts due to the loading of Query Store data.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-database-set-query_store-command-is-blocked\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/alter-database-set-query_store-command-is-blocked\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/alter-database-set-query_store-command-is-blocked\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"ALTER DATABASE SET QUERY_STORE command is blocked"}]},{"@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\/948"}],"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=948"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/948\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=948"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=948"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=948"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}