{"id":796,"date":"2017-01-10T07:32:28","date_gmt":"2017-01-10T15:32:28","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=796"},"modified":"2018-12-14T09:11:08","modified_gmt":"2018-12-14T17:11:08","slug":"t-sql-tuesday-86-storing-and-accessing-the-query-store-data","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-86-storing-and-accessing-the-query-store-data\/","title":{"rendered":"T-SQL Tuesday #86: Storing and Accessing the Query Store Data"},"content":{"rendered":"
\"T-SQL<\/a>
T-SQL Tuesday<\/figcaption><\/figure>\n

The crew over at Brent Ozar Unlimited is hosting this month’s T-SQL Tuesday<\/a>, and the request was to find an interesting Connect item (bug fix or enhancement request) and write about it.\u00a0 I’ve been knee-deep in Query Store, and there are two Connect items that immediately came to mind.<\/p>\n

One of the primary uses of Query Store is finding query regressions – that’s where I start with every presentation and that’s how it’s been pitched by Microsoft<\/a> from the beginning.\u00a0 And typically we talk about troubleshooting production issues that are happening right now<\/em>, but Query Store is also great for finding regressions and problems before<\/em> you upgrade or make changes.\u00a0 If you run Query Store in production, and have some code changes you want to test, you can restore a backup of production, make changes, then run your queries and use Query Store to do comparisons.\u00a0 But when I start talking about this, I can see people start to think about their own testing process, and they start asking questions….<\/p>\n

Where does the Query Store data live?<\/strong>\u00a0 In the user database.<\/p>\n

Can I change the location of the Query Store data?<\/strong> No, it resides in the PRIMARY filegroup.<\/p>\n

Can I export the Query Store data?<\/strong> Technically yes, but it’s not a supported method.<\/p>\n

What if I have sensitive data in Query Store that can’t exist in a non-Production environment?<\/strong> Just like any restore of a database with sensitive data, you’re going to have to obfuscate it.<\/p>\n

Two of these requests have Connect items, which I’m listing below.\u00a0 The first allows you to change the location of Query Store data to reside somewhere in the user database besides the PRIMARY filegroup:<\/p>\n

Option to store query store data in a filegroup other than PRIMARY<\/p>\n

The other request is related to exporting that data, which is technically possible now, but it’s not a supported method so it’s not something I really want to implement in a client environment.\u00a0 I’ve had many people describe their process for testing which includes restoring database nightly.\u00a0 If they’re using Query Store as part of testing, that data is lost every night by the restore.<\/p>\n

Export Query Store tables separately from the database tables<\/p>\n

Find time to up-vote the Connect items you think are most valuable!\u00a0 Haven’t used Connect before?\u00a0 You just need to create a Microsoft account<\/a> (it’s free) and then you can vote.\u00a0 If you use SQL Server on a daily basis, I expect you’d like provide input into the development of the product you’re supporting.\u00a0 Have fun!<\/p>\n","protected":false},"excerpt":{"rendered":"

The crew over at Brent Ozar Unlimited is hosting this month’s T-SQL Tuesday, and the request was to find an interesting Connect item (bug fix or enhancement request) and write about it.\u00a0 I’ve been knee-deep in Query Store, and there are two Connect items that immediately came to mind. One of the primary uses of […]<\/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":"\nT-SQL Tuesday #86: Storing and Accessing the Query Store Data - Erin Stellato<\/title>\n<meta name=\"description\" content=\"There is currently no way to modify where Query Store data is stored, and no way to export it. Check out these two Connect items that could change that!\" \/>\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\/t-sql-tuesday-86-storing-and-accessing-the-query-store-data\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"T-SQL Tuesday #86: Storing and Accessing the Query Store Data - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"There is currently no way to modify where Query Store data is stored, and no way to export it. Check out these two Connect items that could change that!\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-86-storing-and-accessing-the-query-store-data\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2017-01-10T15:32:28+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-12-14T17:11:08+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/01\/tsql2sday150x150.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\/t-sql-tuesday-86-storing-and-accessing-the-query-store-data\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-86-storing-and-accessing-the-query-store-data\/\",\"name\":\"T-SQL Tuesday #86: Storing and Accessing the Query Store Data - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2017-01-10T15:32:28+00:00\",\"dateModified\":\"2018-12-14T17:11:08+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"There is currently no way to modify where Query Store data is stored, and no way to export it. Check out these two Connect items that could change that!\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-86-storing-and-accessing-the-query-store-data\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-86-storing-and-accessing-the-query-store-data\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-86-storing-and-accessing-the-query-store-data\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"T-SQL Tuesday #86: Storing and Accessing the Query Store 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":"T-SQL Tuesday #86: Storing and Accessing the Query Store Data - Erin Stellato","description":"There is currently no way to modify where Query Store data is stored, and no way to export it. Check out these two Connect items that could change that!","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\/t-sql-tuesday-86-storing-and-accessing-the-query-store-data\/","og_locale":"en_US","og_type":"article","og_title":"T-SQL Tuesday #86: Storing and Accessing the Query Store Data - Erin Stellato","og_description":"There is currently no way to modify where Query Store data is stored, and no way to export it. Check out these two Connect items that could change that!","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-86-storing-and-accessing-the-query-store-data\/","og_site_name":"Erin Stellato","article_published_time":"2017-01-10T15:32:28+00:00","article_modified_time":"2018-12-14T17:11:08+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/01\/tsql2sday150x150.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\/t-sql-tuesday-86-storing-and-accessing-the-query-store-data\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-86-storing-and-accessing-the-query-store-data\/","name":"T-SQL Tuesday #86: Storing and Accessing the Query Store Data - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2017-01-10T15:32:28+00:00","dateModified":"2018-12-14T17:11:08+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"There is currently no way to modify where Query Store data is stored, and no way to export it. Check out these two Connect items that could change that!","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-86-storing-and-accessing-the-query-store-data\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-86-storing-and-accessing-the-query-store-data\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/t-sql-tuesday-86-storing-and-accessing-the-query-store-data\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"T-SQL Tuesday #86: Storing and Accessing the Query Store 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\/796"}],"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=796"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/796\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=796"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=796"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=796"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}