{"id":882,"date":"2017-11-07T10:57:10","date_gmt":"2017-11-07T18:57:10","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=882"},"modified":"2018-03-08T10:53:41","modified_gmt":"2018-03-08T18:53:41","slug":"query-store-and-availability-groups","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-availability-groups\/","title":{"rendered":"Query Store and Availability Groups"},"content":{"rendered":"<p>Last week at the PASS Summit I presented a pre-con and general session on Query Store. I had several questions related to Query Store and Availability Groups, so I thought I&#8217;d pull them all together and answer them here.<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Q: What happens to the Query Store data when a database is in an Availability Group?<\/strong><\/p>\n<p><strong>A:<\/strong> You can enable Query Store for any user database, including one in an AG, and since that data is stored in internal tables in the database, it exists in the replica copies as well (just like other system tables and user tables).<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Q: Can you enable Query Store for a read-only replica?<\/strong><\/p>\n<p><strong>A:<\/strong> No.\u00a0 Because the replica is read-only, and Query Store inherently writes data TO the database, you cannot enable it to capture queries that are executed against that read-only copy.\u00a0 I did create a <del>Connect<\/del> UserVoice item for this request.\u00a0 If this is of interest to you, please up-vote it: <a href=\"https:\/\/feedback.azure.com\/forums\/908035-sql-server\/suggestions\/32899126-enable-query-store-for-collection-on-a-read-only-r\">Enable Query Store for collection on a read-only replica in an Availability Group<\/a>.\u00a0 The more votes this has, the better the possibility that Microsoft will implement it, so feel free to share with your friends and have them vote too!<\/p>\n<p>*Update March 8, 2018: Connect has ended and all requests were ported to UserVoice&#8230;and your votes are still needed.\u00a0 Please vote when you have a chance!<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Q: Can you force a plan for a query on a read-only replica if you force it first for that query on the primary?<\/strong><\/p>\n<p><strong>A:<\/strong> No.\u00a0 With a read-only database this is not possible.<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Q: Can you create a separate user database on the secondary, enable Query Store for it, and then issue queries against the read-only replica through that user database and have them captured in Query Store?<\/strong><\/p>\n<p><strong>A:<\/strong> No.\u00a0 (But it&#8217;s a really creative idea.)<\/p>\n<p>&nbsp;<\/p>\n<p>Have other questions related to Query Store and AGs?\u00a0 Let me know!\u00a0 Happy to answer them.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Last week at the PASS Summit I presented a pre-con and general session on Query Store. I had several questions related to Query Store and Availability Groups, so I thought I&#8217;d pull them all together and answer them here. &nbsp; Q: What happens to the Query Store data when a database is in an Availability [&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 and Availability Groups - Erin Stellato<\/title>\n<meta name=\"description\" content=\"If you&#039;re interesting in using Query Store with an Availability Group database, understand what to expect in terms of data and what you can do.\" \/>\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-and-availability-groups\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Query Store and Availability Groups - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"If you&#039;re interesting in using Query Store with an Availability Group database, understand what to expect in terms of data and what you can do.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-availability-groups\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2017-11-07T18:57:10+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-03-08T18:53:41+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\/query-store-and-availability-groups\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-availability-groups\/\",\"name\":\"Query Store and Availability Groups - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2017-11-07T18:57:10+00:00\",\"dateModified\":\"2018-03-08T18:53:41+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"If you're interesting in using Query Store with an Availability Group database, understand what to expect in terms of data and what you can do.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-availability-groups\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-availability-groups\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-availability-groups\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Query Store and Availability Groups\"}]},{\"@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 and Availability Groups - Erin Stellato","description":"If you're interesting in using Query Store with an Availability Group database, understand what to expect in terms of data and what you can do.","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-and-availability-groups\/","og_locale":"en_US","og_type":"article","og_title":"Query Store and Availability Groups - Erin Stellato","og_description":"If you're interesting in using Query Store with an Availability Group database, understand what to expect in terms of data and what you can do.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-availability-groups\/","og_site_name":"Erin Stellato","article_published_time":"2017-11-07T18:57:10+00:00","article_modified_time":"2018-03-08T18:53:41+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\/query-store-and-availability-groups\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-availability-groups\/","name":"Query Store and Availability Groups - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2017-11-07T18:57:10+00:00","dateModified":"2018-03-08T18:53:41+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"If you're interesting in using Query Store with an Availability Group database, understand what to expect in terms of data and what you can do.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-availability-groups\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-availability-groups\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-and-availability-groups\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Query Store and Availability Groups"}]},{"@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\/882"}],"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=882"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/882\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=882"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=882"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=882"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}