{"id":912,"date":"2018-05-10T08:36:04","date_gmt":"2018-05-10T15:36:04","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=912"},"modified":"2018-12-03T08:30:44","modified_gmt":"2018-12-03T16:30:44","slug":"query-store-examples-stories-from-customers","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-examples-stories-from-customers\/","title":{"rendered":"Query Store Examples: Stories from customers"},"content":{"rendered":"

In the past week I’ve used Query Store to troubleshoot performance issues for two different customers running SQL Server 2016 and higher.\u00a0 I’ve presented a lot of sessions about what Query Store is, how to configure it, and basic use cases, but\u00a0we really take it to the next level\u00a0when we can provide\u00a0Query Store examples from the real world.\u00a0 If you are running SQL Server 2016 or SQL Server 2017, I hope you’re using this feature!<\/p>\n

Scenario 1<\/span><\/p>\n

A long time customer reached out for some help after adding 50% more memory to their VM and seeing no improvement in performance.\u00a0 Sometimes you can’t throw hardware at an issue, and they wanted to look at using In-Memory OLTP to help improve performance.\u00a0 After a 10 minute discussion on In-Memory OLTP, where I explained why it wouldn’t magically solve their performance issues (and also explained the amount of testing that would need to be done prior to implementation), I took a look at the system.<\/p>\n

I started with a health audit and a review of wait statistics.\u00a0 There were a couple settings they could change, but nothing was horribly misconfigured.\u00a0 Wait stats showed nothing remarkable.\u00a0 The majority of waits were due to CXPACKET, but the average duration was extremely low.\u00a0 They had some WRITELOG and PAGEIOLATCH waits, but these were also low in average duration.\u00a0 I asked if they had Query Store enabled…they did not.\u00a0 I requested that they enable it to capture query information while I starting to look at plan cache data.\u00a0 With Query Store collecting information, I used Glenn’s DMV queries<\/a> to dig into the plan cache to look at queries that were executing most frequently, taking the longest, and consuming the most resources.<\/p>\n

Now, while the plan cache has some fantastic information, it’s transitory.\u00a0 Plans can fall out of cache because they aren’t used, or because they get recompiled, so truly tracking information for execution frequency\u00a0over time<\/em> can be a bit tricky.\u00a0 Further, there are some plans that never make it into the plan cache (e.g. trivial plans or those with the RECOMPILE hint).\u00a0 This is where Query Store shines.\u00a0 EVERY query that executes can be captured in Query Store, even if it’s trivial or has the RECOMPILE hint.\u00a0 Further, execution frequency and resource use is aggregated over defined intervals (e.g. 15 minutes, 30 minutes), as determined by the INTERVAL_LENGTH_MINUTES setting<\/a>.\u00a0 You can look back over a small or large period of time to see more than just what query performance looked like, but also how many times queries executed.<\/p>\n

Several hours later, after a typical workload where users said performance was “slow”, we looked at the Query Store data.\u00a0 In terms of high resource queries, there were a few (e.g. lots of reads, or lots of CPU), but the execution frequency was low for a lot of those heavy hitters.\u00a0 Then we looked at queries executing most frequently – the death by a thousand cuts scenario – and here we could see that there were thousands of queries executing in an hour, and while individually a query might not take long to execute, or use a lot of resources, but cumulatively it added up.<\/p>\n

\"Queries<\/a>
Queries with high execution count in Query Store<\/figcaption><\/figure>\n

 <\/p>\n

 <\/p>\n

In digging into some of those queries I quickly noticed that almost every plan had a missing index recommendation.\u00a0 I queried sys.indexes and found that almost 95% of the tables in the database had 2 indexes or less.\u00a0 It’s a rare case where a database is under-indexed.\u00a0 Their problem?\u00a0 Their workload volume and their data are slowly, but steadily increasing.\u00a0 They have some big tables that are missing indexes and causing scans, and even though all that data is sitting in memory (because they have enough server memory to hold the entire database), they are using a lot of CPU and time to roll through that data.\u00a0 I could have determined this with the information in the plan cache, but I would have had to set up a job to capture it on a regular basis and then write some queries to do analysis against it.\u00a0 With Query Store, I just had to enable it, let it capture the data, then use the UI to look at performance.<\/p>\n

Scenario 2<\/span><\/p>\n

A new customer\u00a0engaged with us after upgrading to SQL Server 2017.\u00a0 They had some extreme cases of variability in query performance – the system would be running fine and then all of the sudden performance would tank.\u00a0 They would knee-jerk and free the plan cache, then suddenly things would be great again.\u00a0 A couple hours later, the same problem.\u00a0 Rinse and repeat, rinse and repeat.\u00a0 We had them enable Query Store and within a couple hours we took a look at the data (letting them continue with the practice of freeing procedure cache when there was a problem).\u00a0 Within the Query Store UI I looked at CPU use for that window of time, and used the data grid to sort the output to view queries with multiple plans first.\u00a0 There it was…they had a select number of queries that had multiple plans with huge variations due to different input parameters – queries that were parameter sensitive.<\/p>\n

\"Viewing<\/a>
Viewing multiple plans for the same query in Query Store<\/figcaption><\/figure>\n

 <\/p>\n

We used Query Store to force the most optimal plan and thus stabilize performance, then we looked at the query itself and the different plans being generated.\u00a0 Within an hour we determined one code change and an index that would generate a consistent plan.\u00a0 After testing these changes and implementing them, we unforced the plan, confirmed that the new plan we wanted was consistently used, and moved on to the next challenge.<\/p>\n

Summary<\/span><\/p>\n

If you’re running SQL Server 2016 or higher, I highly recommend enabling Query Store…even if you have a third party monitoring tool.\u00a0 Don’t get me wrong, those tools are great and track a ton of information that Query Store doesn’t.\u00a0 But those tools don’t capture EVERY query, nor do they capture query metrics\u00a0all the time.<\/em>\u00a0 Lastly, they don’t provide the ability to force plans.\u00a0 You get all that with Query Store, and more \ud83d\ude42<\/p>\n

If you’re interested in learning more about Query Store from the ground up, you’re in luck!\u00a0 In two weeks I’m hosting a live, online\u00a0Immersion Event for Query Store that you can attend from the comfort of your home (or your desk at work \ud83d\ude42\u00a0 You can find a course description and registration details\u00a0here<\/a>.\u00a0\u00a0I hope you’re able to attend!<\/p>\n","protected":false},"excerpt":{"rendered":"

In the past week I’ve used Query Store to troubleshoot performance issues for two different customers running SQL Server 2016 and higher.\u00a0 I’ve presented a lot of sessions about what Query Store is, how to configure it, and basic use cases, but\u00a0we really take it to the next level\u00a0when we can provide\u00a0Query Store examples from […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[15,46],"tags":[],"yoast_head":"\nQuery Store Examples: Stories from customers - Erin Stellato<\/title>\n<meta name=\"description\" content=\"There's a lot of documentation about setting up Query Store, but in this post we'll step through some Query Store examples from recent real world situations\" \/>\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-examples-stories-from-customers\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Query Store Examples: Stories from customers - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"There's a lot of documentation about setting up Query Store, but in this post we'll step through some Query Store examples from recent real world situations\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-examples-stories-from-customers\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2018-05-10T15:36:04+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-12-03T16:30:44+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/05\/execution-count2.png\" \/>\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=\"5 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-examples-stories-from-customers\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-examples-stories-from-customers\/\",\"name\":\"Query Store Examples: Stories from customers - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2018-05-10T15:36:04+00:00\",\"dateModified\":\"2018-12-03T16:30:44+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"There's a lot of documentation about setting up Query Store, but in this post we'll step through some Query Store examples from recent real world situations\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-examples-stories-from-customers\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-examples-stories-from-customers\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-examples-stories-from-customers\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Query Store Examples: Stories from customers\"}]},{\"@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 Examples: Stories from customers - Erin Stellato","description":"There's a lot of documentation about setting up Query Store, but in this post we'll step through some Query Store examples from recent real world situations","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-examples-stories-from-customers\/","og_locale":"en_US","og_type":"article","og_title":"Query Store Examples: Stories from customers - Erin Stellato","og_description":"There's a lot of documentation about setting up Query Store, but in this post we'll step through some Query Store examples from recent real world situations","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-examples-stories-from-customers\/","og_site_name":"Erin Stellato","article_published_time":"2018-05-10T15:36:04+00:00","article_modified_time":"2018-12-03T16:30:44+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2018\/05\/execution-count2.png"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-examples-stories-from-customers\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-examples-stories-from-customers\/","name":"Query Store Examples: Stories from customers - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2018-05-10T15:36:04+00:00","dateModified":"2018-12-03T16:30:44+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"There's a lot of documentation about setting up Query Store, but in this post we'll step through some Query Store examples from recent real world situations","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-examples-stories-from-customers\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-examples-stories-from-customers\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/query-store-examples-stories-from-customers\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Query Store Examples: Stories from customers"}]},{"@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\/912"}],"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=912"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/912\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=912"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=912"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=912"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}