{"id":986,"date":"2019-01-09T07:00:01","date_gmt":"2019-01-09T15:00:01","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=986"},"modified":"2019-12-05T08:55:45","modified_gmt":"2019-12-05T16:55:45","slug":"different-query-store-settings-for-a-database-in-an-availability-group","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/different-query-store-settings-for-a-database-in-an-availability-group\/","title":{"rendered":"Different Query Store Settings for a Database in an Availability Group"},"content":{"rendered":"

Last week there was a question on #sqlhelp on Twitter about the status of Query Store for a database in an Availability Group. I’ve written about Query Store and Availability Groups<\/a> before so if you’re not familiar with QS behavior in an AG, check out that post first. But this question was, I think, specific to the values <\/em>that shows on a read-only replica and how there were different query store settings between a primary and secondary. Let’s set it up and take a look.<\/p>\n

Environment<\/strong>
\nI have a multi-node cluster set up, and an AG (Avengers) running for the WideWorldImporters database across two nodes (CAP\\ROGERS and BUCKY\\BARNES). Right now, CAP is primary and BUCKY is secondary:<\/p>\n

\"Avengers<\/a>
Avengers AG Status<\/figcaption><\/figure>\n

 <\/p>\n

Query Store<\/strong>
\nOn the primary, if you check the status of Query Store in the UI, we see the following:<\/p>\n

\"Query<\/a>
Query Store configuration for WideWorldImporters on CAP<\/figcaption><\/figure>\n

 <\/p>\n

If you check the status of Query Store from the secondary, the settings are the same:<\/p>\n

\"Query<\/a>
Query Store configuration for WideWorldImporters on BUCKY<\/figcaption><\/figure>\n

 <\/p>\n

Now we’ll make a change to the Query Store on the primary using TSQL:<\/p>\n

USE [master];
\nGO
\nALTER DATABASE [WideWorldImporters]
\nSET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);
\nGO<\/p>\n

And if we check the settings again, we see they’re updated on the primary:<\/p>\n

\"Query<\/a>
Query Store configuration for WideWorldImporters after change on CAP<\/figcaption><\/figure>\n

 <\/p>\n

But on the secondary, you’ll see that settings show the same values as before:<\/p>\n

\"Query<\/a>
Query Store configuration for WideWorldImporters after change on BUCKY<\/figcaption><\/figure>\n

 <\/p>\n

This is expected.\u00a0 When the instance starts up and the database loads on a replica, the Query Store settings are pulled from sys.database_query_store_options, and these are cached in memory. Any change that is subsequently made to the configuration of Query Store on the primary is persisted to disk on the primary and then propagated over to the secondary and written to disk, just like anything else. However, on-disk changes to Query Store settings are only propagated to the memory cache when the secondary replica is restarted, or if there is a failover and the secondary becomes primary.<\/p>\n

Conclusion<\/strong><\/p>\n

If you’re seeing a disparity between values for Query Store settings on a primary and any or all of your replicas, it is expected and you can be assured that the changes have been written to the secondary copies and committed.<\/p>\n","protected":false},"excerpt":{"rendered":"

Last week there was a question on #sqlhelp on Twitter about the status of Query Store for a database in an Availability Group. I’ve written about Query Store and Availability Groups before so if you’re not familiar with QS behavior in an AG, check out that post first. But this question was, I think, specific […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[51,46],"tags":[],"yoast_head":"\nDifferent Query Store Settings for a Database in an Availability Group - Erin Stellato<\/title>\n<meta name=\"description\" content=\"When making changes to Query Store on a primary database in AG, different query store settings will appear on replicas.\" \/>\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\/different-query-store-settings-for-a-database-in-an-availability-group\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Different Query Store Settings for a Database in an Availability Group - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"When making changes to Query Store on a primary database in AG, different query store settings will appear on replicas.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/different-query-store-settings-for-a-database-in-an-availability-group\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2019-01-09T15:00:01+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-12-05T16:55:45+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2019\/01\/AG.jpg\" \/>\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\/different-query-store-settings-for-a-database-in-an-availability-group\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/different-query-store-settings-for-a-database-in-an-availability-group\/\",\"name\":\"Different Query Store Settings for a Database in an Availability Group - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2019-01-09T15:00:01+00:00\",\"dateModified\":\"2019-12-05T16:55:45+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"When making changes to Query Store on a primary database in AG, different query store settings will appear on replicas.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/different-query-store-settings-for-a-database-in-an-availability-group\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/different-query-store-settings-for-a-database-in-an-availability-group\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/different-query-store-settings-for-a-database-in-an-availability-group\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Different Query Store Settings for a Database in an Availability Group\"}]},{\"@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":"Different Query Store Settings for a Database in an Availability Group - Erin Stellato","description":"When making changes to Query Store on a primary database in AG, different query store settings will appear on replicas.","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\/different-query-store-settings-for-a-database-in-an-availability-group\/","og_locale":"en_US","og_type":"article","og_title":"Different Query Store Settings for a Database in an Availability Group - Erin Stellato","og_description":"When making changes to Query Store on a primary database in AG, different query store settings will appear on replicas.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/different-query-store-settings-for-a-database-in-an-availability-group\/","og_site_name":"Erin Stellato","article_published_time":"2019-01-09T15:00:01+00:00","article_modified_time":"2019-12-05T16:55:45+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2019\/01\/AG.jpg"}],"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\/different-query-store-settings-for-a-database-in-an-availability-group\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/different-query-store-settings-for-a-database-in-an-availability-group\/","name":"Different Query Store Settings for a Database in an Availability Group - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2019-01-09T15:00:01+00:00","dateModified":"2019-12-05T16:55:45+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"When making changes to Query Store on a primary database in AG, different query store settings will appear on replicas.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/different-query-store-settings-for-a-database-in-an-availability-group\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/different-query-store-settings-for-a-database-in-an-availability-group\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/different-query-store-settings-for-a-database-in-an-availability-group\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Different Query Store Settings for a Database in an Availability Group"}]},{"@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\/986"}],"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=986"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/986\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=986"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=986"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=986"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}