{"id":1045,"date":"2019-09-10T10:03:08","date_gmt":"2019-09-10T17:03:08","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=1045"},"modified":"2019-09-10T10:03:08","modified_gmt":"2019-09-10T17:03:08","slug":"why-you-need-query-store-part-i-historical-performance-data","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-i-historical-performance-data\/","title":{"rendered":"Why You Need Query Store, Part I: Historical Performance Data"},"content":{"rendered":"

The Query Store feature previewed in Azure SQL Database in summer 2015<\/a>, was made generally available that fall, and was part of the SQL Server 2016 release the following summer.\u00a0 Over the past four years (has it really been that long?!) I have devoted significant time to learning Query Store \u2013 not just understanding how it works from the bottom up, but also why<\/em> it works the way it does.\u00a0 I\u2019ve also spent a lot of time sharing that information<\/a> and helping customers understand and implement the feature, and then working with them to use the query store data to troubleshoot issues and stabilize performance.\u00a0 During this time I have developed a deep appreciation for the Query Store feature, as its capabilities go far beyond its original marketing.\u00a0 Is it perfect?\u00a0 No.\u00a0 But it\u2019s a feature that Microsoft continues to invest in, and in this series of blog posts my aim is to help you understand why<\/em> Query Store is a tool you need to leverage in your environment.<\/p>\n

It\u2019s all about the data<\/h2>\n

The most talked-about benefit of Query Store is its ability to help you dig into query performance and regressions, which is possible because of the data it captures.\u00a0 The bulk of the Query Store data resides seven system tables, exposed through views, with a total of over 150 distinct data points.\u00a0 Notable within that collection of information are:<\/p>\n

    \n
  • Statement text\n
      \n
    • individual queries are captured, whether they are ad hoc or part of an object (stored procedure)<\/li>\n<\/ul>\n<\/li>\n
    • Query Plan(s)\n
        \n
      • the plan that was used during execution is stored in Query Store, it\u2019s the equivalent of what you get from the plan cache<\/li>\n<\/ul>\n<\/li>\n
      • Execution Statistics\n
          \n
        • resource use, execution count, duration data and more are stored<\/li>\n<\/ul>\n<\/li>\n
        • Wait Statistics\n
            \n
          • Azure SQL Database and SQL Server 2017 and higher can capture query-level wait statistics<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n

            Query Store is a database-scoped feature – it\u2019s not something that\u2019s enabled for the entire instance – therefore the data is stored within the user database.\u00a0 To view that data you can use the built-in reports in Management Studio (the 18.x release has seven reports, earlier releases only have four), or you can write your own queries against the system views.<\/p>\n

            Viewing data<\/h2>\n

            Depending on how you\u2019ve configured Query Store, you can look at historical query information going back days or weeks.\u00a0 Not only can you see trends in query performance over time, but you can also compare query performance between specific windows of time.\u00a0 The image below shows the top 25 queries for the past two weeks based on total duration.\u00a0 The grid view on the left (which I prefer for analysis but is not the default) lets us see the statement text, the object to which it belongs (if it\u2019s blank then it\u2019s an ad hoc query), and the number of plans for the query.<\/p>\n

             <\/p>\n

            \"Query
            Top 25 Queries (Total Duration) for the Last Two Weeks<\/figcaption><\/figure>\n

             <\/p>\n

            The ability to view performance of the plans over time, as displayed on the right side of the image, is a huge benefit for any data professional that has to troubleshoot performance, or figure out what happened AFTER<\/em> the fact.\u00a0 For this query, query_id 45, there are two plans, plan_id 45 and plan_id 1882.\u00a0 We can see that one plan, 1882, was used on September 9, 2019, and the other, 45, was used the next day, on September 10, 2019.\u00a0 Had someone complained because performance was abysmal on the 9th<\/sup>, but then just fine on the 10th<\/sup>, we could look at this data and see immediately it was due to a change in plan.\u00a0 If you didn\u2019t have Query Store, how would you know that?<\/p>\n

            You wouldn\u2019t, unless you had set up a method to capture query plan and execution information, or you had a third-party monitoring tool.\u00a0 On that topic, does Query Store replace the need for third-party monitoring tools?\u00a0 It doesn\u2019t.\u00a0 I don\u2019t believe you need one or the other, I think having access to both only makes your life easier.\u00a0 If you don\u2019t have the budget for a third-party tool, then Query Store is a fantastic option as it\u2019s included in SQL Server, the data is collected within that user database, you control how long the data is kept, and it\u2019s extremely easy to look at query performance using the built-in reports.<\/p>\n","protected":false},"excerpt":{"rendered":"

            The Query Store feature previewed in Azure SQL Database in summer 2015, was made generally available that fall, and was part of the SQL Server 2016 release the following summer.\u00a0 Over the past four years (has it really been that long?!) I have devoted significant time to learning Query Store \u2013 not just understanding how […]<\/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":"\nWhy You Need Query Store, Part I: Historical Performance Data - Erin Stellato<\/title>\n<meta name=\"description\" content=\"The Query Store data is central to the functionality of the feature, and provides a wealth of information about a database worklaod\" \/>\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\/why-you-need-query-store-part-i-historical-performance-data\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Why You Need Query Store, Part I: Historical Performance Data - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"The Query Store data is central to the functionality of the feature, and provides a wealth of information about a database worklaod\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-i-historical-performance-data\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2019-09-10T17:03:08+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2019\/09\/QS_basicview-1024x357.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=\"4 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\/why-you-need-query-store-part-i-historical-performance-data\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-i-historical-performance-data\/\",\"name\":\"Why You Need Query Store, Part I: Historical Performance Data - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2019-09-10T17:03:08+00:00\",\"dateModified\":\"2019-09-10T17:03:08+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"The Query Store data is central to the functionality of the feature, and provides a wealth of information about a database worklaod\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-i-historical-performance-data\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-i-historical-performance-data\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-i-historical-performance-data\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Why You Need Query Store, Part I: Historical Performance Data\"}]},{\"@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":"Why You Need Query Store, Part I: Historical Performance Data - Erin Stellato","description":"The Query Store data is central to the functionality of the feature, and provides a wealth of information about a database worklaod","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\/why-you-need-query-store-part-i-historical-performance-data\/","og_locale":"en_US","og_type":"article","og_title":"Why You Need Query Store, Part I: Historical Performance Data - Erin Stellato","og_description":"The Query Store data is central to the functionality of the feature, and provides a wealth of information about a database worklaod","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-i-historical-performance-data\/","og_site_name":"Erin Stellato","article_published_time":"2019-09-10T17:03:08+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2019\/09\/QS_basicview-1024x357.jpg"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-i-historical-performance-data\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-i-historical-performance-data\/","name":"Why You Need Query Store, Part I: Historical Performance Data - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2019-09-10T17:03:08+00:00","dateModified":"2019-09-10T17:03:08+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"The Query Store data is central to the functionality of the feature, and provides a wealth of information about a database worklaod","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-i-historical-performance-data\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-i-historical-performance-data\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/why-you-need-query-store-part-i-historical-performance-data\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Why You Need Query Store, Part I: Historical Performance Data"}]},{"@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\/1045"}],"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=1045"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/1045\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=1045"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=1045"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=1045"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}