I have Query Store (QS) running on a SQL 2017 instance. Currently at RTM, with RTM CU13 currently in test, to be applied on prod in next month's patch window.
While most queries and reports return results quickly with little to no impact, anything thing I try to look at around waits is problematic. CPU usage climbs from 20 to 80 percent and stays there for minutes until I kill it. This is 24/7 production system so if I really want to look at QS waits I am going to need to do it someplace else.
The database is 150GB with 1000MB of space for QS. I have a sandbox with 10GB of space so if I could get the QS data out, I could play with it there.
I looked around and I am not finding how to do this. The best I found was is this sql.sasquatch 2016 post with a 2016 answer by Erin Stellato
At present, there is no option to export and/or import Query Store data, but there is a Connect item to vote for: https://connect.microsoft.com/SQLServer/feedback/details/2620017/export-query-store-tables-separately-from-the-database-tables
Note: The link goes to a redirect "Microsoft Connect Has Been Retired" Looks like the actual link should be to https://feedback.azure.com/forums/908035-sql-server/suggestions/32901670-export-query-store-tables-separately-from-the-data
Looking at Microsoft, I find that most everything you might use to access the data is a View, Stored Procedure or Report. I am not seeing a way to just extract all the QS stuff out of database.
Examples of direct queries, use the views Example by Kendra Little I have toyed with the idea of just doing a
Select * from the views and exporting the results to my sandbox. But as I have not found anyone talking about it, I am not sure it is good idea.
- Best Practice with the Query Store
- How Query Store Collects Data
- Query Store Catalog Views
- Query Store Stored Procedures
- Monitoring performance by using the Query Store
Additionally I would like to be able to keep the Query Store results from pre-CU13 to use as a baseline to compare post CU13.
Edit after first answer and edits to same Recent edits to the answer by jadarnel27 adds good info, but I don't care about the user interface, I want to be able to query the data without altering the database or impacting performance. As a secondary goal I would like to be able to archive the QS data, so I can review previous performance (i.e. before and upgrade, but after the old QS data would have purged off)