{"id":760,"date":"2016-05-11T07:38:12","date_gmt":"2016-05-11T14:38:12","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=760"},"modified":"2018-12-14T09:11:08","modified_gmt":"2018-12-14T17:11:08","slug":"sql-server-query-store-data-storage-options-and-a-request","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-data-storage-options-and-a-request\/","title":{"rendered":"SQL Server Query Store: Data Storage Options (and a Request!)"},"content":{"rendered":"<p>Microsoft <a href=\"https:\/\/blogs.technet.microsoft.com\/dataplatforminsider\/2016\/05\/02\/get-ready-sql-server-2016-coming-on-june-1st\/\">recently announced<\/a> that SQL Server 2016 RTM will be available June 1, 2016, and it was also confirmed that <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/performance\/monitoring-performance-by-using-the-query-store\">Query Store<\/a> will be available in <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/sql-server\/editions-and-supported-features-for-sql-server-2016\">ALL editions of SQL Server<\/a>.\u00a0 This is pretty exciting, as there was some concern that it would be Enterprise-only.\u00a0 I\u2019ve done a couple sessions on Query Store this year, and the response to the feature has been fantastic.\u00a0 If you haven\u2019t checked it out yet, I highly recommend it (<a href=\"https:\/\/www.microsoft.com\/en-us\/evalcenter\/evaluate-sql-server-2016\">download 2016 RC3 here<\/a>), and in this post I want to talk about the Query Store data \u2013 where it\u2019s located and how it\u2019s retained.<\/p>\n<p><strong>Query Store Data Storage<\/strong><\/p>\n<p>Enabling Query Store is pretty simple.\u00a0 It\u2019s done a per-database basis (note that master and tempdb cannot be enabled for Query Store) either through the UI or via T-SQL.\u00a0 I first restored a copy of the AdventureWorks2016 database (<a href=\"https:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=49502\" class=\"broken_link\">download from here<\/a>).\u00a0\u00a0 Interestingly enough, Query Store is already enabled for the database.\u00a0 But if it were not, to enable it you right-click on the database and select Properties, go to the Query Store page, and then select Read Write for the Operation Mode (Requested).\u00a0 This is the code that runs if you script it out from the UI:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nUSE &#x5B;master]\r\nGO\r\nALTER DATABASE &#x5B;AdventureWorks2016] SET QUERY_STORE = ON\r\nGO\r\nALTER DATABASE &#x5B;AdventureWorks2016] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)\r\nGO\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>Multiple settings are then configured to the default values for Query Store \u2013 it\u2019s analogous to running this code:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nUSE &#x5B;master]\r\nGO\r\nALTER DATABASE &#x5B;AdventureWorks2016] SET QUERY_STORE = ON\r\nGO\r\nALTER DATABASE &#x5B;AdventureWorks2016] SET QUERY_STORE (\r\nOPERATION_MODE = READ_WRITE,\r\nCLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),\r\nDATA_FLUSH_INTERVAL_SECONDS = 900,\r\nINTERVAL_LENGTH_MINUTES = 60,\r\nMAX_STORAGE_SIZE_MB = 100,\r\nQUERY_CAPTURE_MODE = ALL,\r\nSIZE_BASED_CLEANUP_MODE = AUTO,\r\nMAX_PLANS_PER_QUERY = 200);\r\nGO\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>The setting I want to point out today is MAX_STORAGE_SIZE.\u00a0 Understand that with Query Store, the data is persisted *IN* the user database.\u00a0 Which means that if you backup and restore the database to another instance (or detach and attach), the Query Store data goes <em>with<\/em> the database.\u00a0 This is pretty handy.\u00a0 You have the ability to set how much space can be used by Query Store <em>within<\/em> the user database with the MAX_STORAGE_SIZE setting.\u00a0 The default value, 1024MB, is a good start.\u00a0 Because workloads vary, and what data *you* might want to keep is different than what someone else want to keep, and there aren\u2019t any recommendations as to what this size \u201cshould\u201d be at this point.\u00a0 It\u2019s something you\u2019re going to have test out in your environment \u2013 just like figuring out the \u201cbest\u201d size for the transaction log for a database, or the size of tempdb for an instance.\u00a0 Note that the more varied the workload (more unique plans), the more space you might need.\u00a0 <a href=\"https:\/\/medium.com\/@rusanu\/why-i-have-high-hopes-for-the-quality-of-sql-server-2016-release-6173bc1fbc82#.evcj15poy\" class=\"broken_link\">Remus Rusanu points out in a recent post<\/a> that the SQL Server team vastly under-estimated the space needed for a client workload \u2013 it consumed the Query Store allocated space in <em>minutes<\/em> rather than months.\u00a0 Disk space is pretty cheap \u2013 I would probably start with a few GB of space for Query Store and monitor how much is used with this query:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT\r\nactual_state_desc,\r\ncurrent_storage_size_mb,\r\nmax_storage_size_mb,\r\nflush_interval_seconds,\r\ninterval_length_minutes,\r\nsize_based_cleanup_mode_desc\r\nFROM sys.database_query_store_options;\r\nGO\r\n<\/pre>\n<p>SQL Server can automatically start to purge data from the store based on how much of that allocated space is used.\u00a0 This is determined by size_based_cleanup_mode_desc setting, which defaults to AUTO.\u00a0 With AUTO set, SQL Server will start purging data when the space used gets close the max_storage_size_mb setting.\u00a0 If you have this set to OFF, then when current_storage_size_mb is reached, Query Store will <em>stop<\/em> collecting data and actual_state_desc will switch to READ_ONLY.<\/p>\n<p><strong>\u00a0Using Query Store to Find Query Regressions or Improvements<\/strong><\/p>\n<p>Ok, so now we\u2019re clear on those storage-related settings.\u00a0 There\u2019s more of a conversation to have about <em>how<\/em> you\u2019re going to use this data to find regressed queries, etc\u2026but that\u2019s an entirely separate post.\u00a0 I want you now to think about the value of this data and how can you use it to measure the impact of code changes, SQL Server patches and upgrades, hardware changes and so on.<\/p>\n<p>When you restore a copy of your production database to your Test\/Dev\/QA\/UAT environment (because you have at least one of those, right?), the Query Store data from production will come with it.\u00a0 This is great because you then run your workload in that environment and do comparisons <em>using the Query Store data<\/em>.\u00a0 Awesome.\u00a0 But what about this scenario, proposed by an attendee of last week\u2019s IEPTO2 class\u2026<\/p>\n<p style=\"padding-left: 30px;\">You have an automated process developed in-house to backup and restore production databases down to lower environments (like Test\/Dev\/QA\/UAT) on a regular basis (daily, weekly).\u00a0 Because the Query Store data lives in the user database, you actually want to clear the Query Store data once the database is restored in the lower environment.\u00a0 Then you test new code, etc. against the database.<\/p>\n<p style=\"padding-left: 30px;\">When the production data is restored again, all data collected during testing in Test\/Dev\/QA\/UAT will be lost\u2026<em>unless there is a way to export it<\/em>.\u00a0 Meaning, let\u2019s save off that Query Store generated from testing, <em>before the refresh<\/em> so I can do comparisons between THOSE tests.<\/p>\n<p>Now, there are <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-catalog-views\/query-store-catalog-views-transact-sql\">catalog views<\/a> that allow you to view the Query Store data.\u00a0 You can copy that data into another database using SELECT INTO, and then do comparisons, but wouldn\u2019t it be nice to have some kind of export option?\u00a0 There\u2019s a Connect item for that:<\/p>\n<p>Export Query Store tables separately from the database tables: https:\/\/connect.microsoft.com\/SQLServer\/feedback\/details\/2620017\/export-query-store-tables-separately-from-the-database-tables<\/p>\n<p>If you think this is something that would be useful for Query Store, please up vote it!\u00a0 Again, Query Store is available in <strong>ALL<\/strong> editions of SQL Server 2016, this is definitely a feature you <strong>CAN<\/strong> use and will want to use!\u00a0 This potential option won\u2019t make it into RTM, but with the change in how SQL Server is releasing CUs, the more important the SQL Server team sees this option (as a result of votes), the faster it might make it in a release.<\/p>\n<p>If you haven\u2019t looked into Query Store, I definitely recommend doing so!\u00a0 When I demo\u2019d the feature last week, one of our attendees said that he would consider skipping 2014 and going straight to 2016, just for this feature alone.\u00a0 <em>A feature that\u2019s available in EVERY Edition.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Microsoft recently announced that SQL Server 2016 RTM will be available June 1, 2016, and it was also confirmed that Query Store will be available in ALL editions of SQL Server.\u00a0 This is pretty exciting, as there was some concern that it would be Enterprise-only.\u00a0 I\u2019ve done a couple sessions on Query Store this year, [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","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>SQL Server Query Store: Data Storage Options (and a Request!) - Erin Stellato<\/title>\n<meta name=\"description\" content=\"SQL Server&#039;s Query Store data stores query information in the user database. In this post we&#039;ll look at the storage options for that data.\" \/>\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\/sql-server-query-store-data-storage-options-and-a-request\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Query Store: Data Storage Options (and a Request!) - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"SQL Server&#039;s Query Store data stores query information in the user database. In this post we&#039;ll look at the storage options for that data.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-data-storage-options-and-a-request\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2016-05-11T14:38:12+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-12-14T17:11:08+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=\"6 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\/sql-server-query-store-data-storage-options-and-a-request\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-data-storage-options-and-a-request\/\",\"name\":\"SQL Server Query Store: Data Storage Options (and a Request!) - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2016-05-11T14:38:12+00:00\",\"dateModified\":\"2018-12-14T17:11:08+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"SQL Server's Query Store data stores query information in the user database. In this post we'll look at the storage options for that data.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-data-storage-options-and-a-request\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-data-storage-options-and-a-request\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-data-storage-options-and-a-request\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Query Store: Data Storage Options (and a Request!)\"}]},{\"@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":"SQL Server Query Store: Data Storage Options (and a Request!) - Erin Stellato","description":"SQL Server's Query Store data stores query information in the user database. In this post we'll look at the storage options for that data.","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\/sql-server-query-store-data-storage-options-and-a-request\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Query Store: Data Storage Options (and a Request!) - Erin Stellato","og_description":"SQL Server's Query Store data stores query information in the user database. In this post we'll look at the storage options for that data.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-data-storage-options-and-a-request\/","og_site_name":"Erin Stellato","article_published_time":"2016-05-11T14:38:12+00:00","article_modified_time":"2018-12-14T17:11:08+00:00","author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-data-storage-options-and-a-request\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-data-storage-options-and-a-request\/","name":"SQL Server Query Store: Data Storage Options (and a Request!) - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2016-05-11T14:38:12+00:00","dateModified":"2018-12-14T17:11:08+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"SQL Server's Query Store data stores query information in the user database. In this post we'll look at the storage options for that data.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-data-storage-options-and-a-request\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-data-storage-options-and-a-request\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-query-store-data-storage-options-and-a-request\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"SQL Server Query Store: Data Storage Options (and a Request!)"}]},{"@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\/760"}],"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=760"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/760\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=760"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=760"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=760"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}