{"id":1053,"date":"2019-09-23T14:10:51","date_gmt":"2019-09-23T21:10:51","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=1053"},"modified":"2019-09-23T14:40:04","modified_gmt":"2019-09-23T21:40:04","slug":"query-store-questions-24hop-follow-up","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-questions-24hop-follow-up\/","title":{"rendered":"Query Store Questions: 24HOP follow up"},"content":{"rendered":"

 <\/p>\n

A couple weeks ago I presented for the 24HOP Summit Preview and I had a lot of great general questions about how Query Store works.\u00a0 My session title was \u201cWhy You Need Query Store\u201d (you can watch it here<\/a>) and I only had about 45 minutes.\u00a0 As you can probably guess – since I have a full day pre-con on the topic – I can talk about Query Store for a loooong time \ud83d\ude42\u00a0 The 24HOP session was really focused on getting folks to understand THE<\/strong> most important things that Query Store provides to show why it\u2019s needed.\u00a0 I left out a lot of details about HOW Query Store works because talking through it is the fun stuff that I\u2019ll dive into during the pre-con.\u00a0 I did have a good number of questions from attendees related to specific functionality, and I promised to write a post answering them.\u00a0 Questions* and answers are below\u2026if you need clarification on anything, please leave a comment and I\u2019ll follow up!<\/p>\n

*Questions copied exactly as they were shared with me, I did not try to re-word or make any inferences about what was being asked.<\/em><\/p>\n

Questions<\/h2>\n

1. Can I get Query Store data for a production database deployed on a client site, that I don\u2019t actually have access to myself? Can the DBA send me something I can use in my own development environment?<\/p>\n

A:<\/strong> You provide instructions to the client that explain how to enable Query Store, either through the UI or with T-SQL.\u00a0 If you want to view that Query Store data, the client can either send you a backup of the database or create a clone with DBCC CLONEDATABASE<\/a> and share that.<\/p>\n

 <\/p>\n

2. If a user executes a stored procedure from \u2018master\u2019 it is not captured?<\/p>\n

A:<\/strong> If you have Query Store enabled for a user database, and you execute a query against that user database from the context of the master database, it is not supposed to be captured in Query Store for that user database.\u00a0 But in my testing, it is.\u00a0 But it is not supposed to be, so there is no guarantee it will always work that way.<\/p>\n

 <\/p>\n

3. If your database is part of an AG the data you look at can be different based on the server it is running on at that time, correct?<\/p>\n

A:<\/strong> I\u2019m not quite clear what\u2019s being asked, but I wrote a post about Query Store and Availability Groups<\/a>, which will hopefully answer the question.<\/p>\n

 <\/p>\n

4. Is it easy to remove the forcing of a given plan?<\/p>\n

A:<\/strong> Yes, just use the \u201cUnforce Plan\u201d button in the UI, or use the stored procedure sp_query_store_unforce_plan<\/a> (you supply the query_id and plan_id).<\/p>\n

 <\/p>\n

5. If you have 3+ plans how does SQL Server decide which plan to use?<\/p>\n

A:<\/strong> I assume this is specific to the Automatic Plan Correction feature, and if so, it will force the last good plan (most recent plan that performed better than the current plan).\u00a0 More details in my Automatic Plan Correction in SQL Server<\/a> post.<\/p>\n

 <\/p>\n

6. What equivalent options we have for lower versions?<\/p>\n

A:<\/strong> \u00a0There is an open-source tool called Open Query Store<\/a> for versions prior to SQL Server 2016.<\/p>\n

 <\/p>\n

7. Why are the trace flags not on by default? given the issues with AlwaysOn and QS<\/p>\n

A:<\/strong> Great question.\u00a0 Trace flag 7752<\/a> will be<\/strong> default functionality in SQL Server 2019.\u00a0 TF 7745 is not default functionality because, I suspect, of the potential for losing Query Store data…and SQL Server wants you to make a conscious choice about that.\u00a0 More details in Query Store Trace Flags<\/a>.<\/p>\n

 <\/p>\n

8. How would you use Query Store to troubleshoot Views?<\/p>\n

A:<\/strong> Query Store does not<\/strong> differentiate between a query that references a view and a query that references a table.\u00a0 It does not capture the object_id of the view and store that in Query Store (as happens for a stored procedure), so you have to look specifically for the view name in the query_sql_text column (within sys.query_store_query_text) to look for queries that reference the view.<\/p>\n

 <\/p>\n

9. Is there any way to make use of Query Store in readonly secondary AG replicas?<\/p>\n

A:<\/strong> You can read data from the Query Store views on a read-only replica, but you cannot capture data in Query Store about queries executing against<\/em> the read-only replica.\u00a0 See my post referenced in #3, and then please up-vote this request: Enable Query Store for collection on a read-only replica in an Availability Group<\/a>.<\/p>\n

 <\/p>\n

10. Is it possible that a query store run from one instance to another instance for example I want check the queries of production from dev instance?<\/p>\n

A:<\/strong> If you can connect to the production instance from the dev instance, and have appropriate permissions, then you can query the Query Store data on the production instance (but the data exist in the production database).<\/p>\n

 <\/p>\n

11. If I execute a parameterized query with OPTION (RECOMPILE), will Query Store have the parameter values of every execution?<\/p>\n

A: <\/strong>No.\u00a0 The plan will have the values used for the initial execution that generated said plan, but values for every individual execution are not captured (it would generate excessive overhead to capture every execution).<\/p>\n

 <\/p>\n

12. Can Query Store supply the T-SQL to force plan?<\/p>\n

A: <\/strong>The UI does not provide an option to script forcing a plan for a query, but if you are using Automatic Plan Correction, the T-SQL to force it can be found in sys.dm_db_tuning_recommendations<\/a>.<\/p>\n

 <\/p>\n

13. Will there be any significant performance overhead by using query store?<\/p>\n

A:<\/strong> See Query Store Performance Overhead: What you need to know<\/a><\/p>\n

 <\/p>\n

14. How does it function when queries span multiple databases?<\/p>\n

A:<\/strong> As alluded to in question #2, cross-database queries are tricky.\u00a0 You should work under the assumption that if you execute a query from Database_A, where Database_A has Query Store enabled, it will be captured.\u00a0 If you execute a query from Database_A that queries both Database_A and Database_B, and both databases have Query Store enabled, it will ONLY be captured in Database_A.<\/p>\n

 <\/p>\n

15. It seems to be working for me, but sometimes not<\/p>\n

A:<\/strong> I would love to help you out, just not sure of the behavior you\u2019re seeing and what your question is.<\/p>\n

 <\/p>\n

16. How do you get the full query text from inside the \u2018Top Resource Consuming Queries\u2019 windows?<\/p>\n

A:<\/strong> Click on the button with the grid and magnifying glass, which says \u201cView the query text of the selected query in a query editor window\u201d when you hover over it.<\/p>\n

\"Button
Button to “View the query text of the selected query in a query editor window”<\/figcaption><\/figure>\n

 <\/p>\n

17. Is the data will be stored in Query Store After the adhoc\/SP completed or it will do while is running?<\/p>\n

A:<\/strong> Once the plan has been compiled for a query, the query text and plan are sent to Query Store.\u00a0 When execution completes, the runtime statistics are sent to Query Store.<\/p>\n

 <\/p>\n

18. If we change the compatibility to SQL 2012 or lower, will that affect Query store?<\/p>\n

A:<\/strong> No, Query Store functions in SQL Server 2016 and higher, and Azure SQL Database, regardless of your compatibility mode.<\/p>\n

 <\/p>\n

19. If we drop a SP, will that clear the history of that SP plans in the query store?<\/p>\n

A:<\/strong> No, but\u2026If you use DROP PROCEDURE syntax, then the object_id column in sys.query_store_query will no longer reference an existing object (in sys.objects).\u00a0 The query and plans will stay in Query Store until they are aged out based on the retention policy.<\/p>\n

 <\/p>\n

Follow Up<\/h2>\n

Again, if any answers are unclear, leave a comment and I can clarify.\u00a0 If you are interested in learning more about Query Store I would love to see you in my full day session at the PASS Summit!\u00a0 It’s on Monday, November 5th, and you get more details here: Performance Tuning with Query Store in SQL Server<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

  A couple weeks ago I presented for the 24HOP Summit Preview and I had a lot of great general questions about how Query Store works.\u00a0 My session title was \u201cWhy You Need Query Store\u201d (you can watch it here) and I only had about 45 minutes.\u00a0 As you can probably guess – since I […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,46],"tags":[],"yoast_head":"\nQuery Store Questions: 24HOP follow up - Erin Stellato<\/title>\n<meta name=\"description\" content=\"During my 24HOP session there were several questions about how Query Store works - even though we focused on the WHY, some details are good too\" \/>\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-questions-24hop-follow-up\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Query Store Questions: 24HOP follow up - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"During my 24HOP session there were several questions about how Query Store works - even though we focused on the WHY, some details are good too\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-questions-24hop-follow-up\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2019-09-23T21:10:51+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-09-23T21:40:04+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2019\/09\/Display-Text-1024x626.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=\"7 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-questions-24hop-follow-up\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-questions-24hop-follow-up\/\",\"name\":\"Query Store Questions: 24HOP follow up - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2019-09-23T21:10:51+00:00\",\"dateModified\":\"2019-09-23T21:40:04+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"During my 24HOP session there were several questions about how Query Store works - even though we focused on the WHY, some details are good too\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-questions-24hop-follow-up\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-questions-24hop-follow-up\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-questions-24hop-follow-up\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Query Store Questions: 24HOP follow up\"}]},{\"@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 Questions: 24HOP follow up - Erin Stellato","description":"During my 24HOP session there were several questions about how Query Store works - even though we focused on the WHY, some details are good too","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-questions-24hop-follow-up\/","og_locale":"en_US","og_type":"article","og_title":"Query Store Questions: 24HOP follow up - Erin Stellato","og_description":"During my 24HOP session there were several questions about how Query Store works - even though we focused on the WHY, some details are good too","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-questions-24hop-follow-up\/","og_site_name":"Erin Stellato","article_published_time":"2019-09-23T21:10:51+00:00","article_modified_time":"2019-09-23T21:40:04+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2019\/09\/Display-Text-1024x626.jpg"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-questions-24hop-follow-up\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-questions-24hop-follow-up\/","name":"Query Store Questions: 24HOP follow up - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2019-09-23T21:10:51+00:00","dateModified":"2019-09-23T21:40:04+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"During my 24HOP session there were several questions about how Query Store works - even though we focused on the WHY, some details are good too","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-questions-24hop-follow-up\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-questions-24hop-follow-up\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-questions-24hop-follow-up\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Query Store Questions: 24HOP follow up"}]},{"@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\/1053"}],"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=1053"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/1053\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=1053"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=1053"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=1053"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}