How can I export Query Store data?

Question

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.

Related

• 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)

Answer 1

First of all, you might be able to get acceptable performance with queries directly against the query store catalog views by updating stats, adding query hints with plan guides, or changing the database compatibility level / CE. See the answers from Forrest and Marian here:

Never ending Query Store search

---

If you're on SP1 or greater, the simplest approach would be to use DBCC CLONEDATABASE - which includes statistics, query store data, and schema objects - but none of the actual data from the tables.

---

Otherwise, for exporting, one approach would be a simple SELECT...INTO from the query store views to the "sandbox" database. These are the relevant views.

The basic approach would be like this:

SELECT * INTO Sandbox.dbo.query_store_runtime_stats FROM sys.query_store_runtime_stats;
SELECT * INTO Sandbox.dbo.query_store_runtime_stats_interval FROM sys.query_store_runtime_stats_interval;
SELECT * INTO Sandbox.dbo.query_store_plan FROM sys.query_store_plan;
SELECT * INTO Sandbox.dbo.query_store_query FROM sys.query_store_query;
SELECT * INTO Sandbox.dbo.query_store_query_text FROM sys.query_store_query_text;
SELECT * INTO Sandbox.dbo.query_store_wait_stats FROM sys.query_store_wait_stats;

The nice thing about this approach is that:

• you'll only get the data you need (1000 MB)
• you can add indexes to support your reporting queries, because these are actual tables
• they won't have the unusual memory scanning behavior that leads to poor performance against the actual views (again because they are actual tables)
  • Note: the SELECT...INTO queries shouldn't drive up CPU like the built-in query store reporting queries, because they won't have the problematic joins that cause repeated access to the in-memory TVFs
• you can keep different versions of the data (for different CU levels, etc) by changing the table names, or adding a column to the tables that indicates the data and / or version of SQL Server that was used for that import

The "con" of this approach is that you can't use the query store user interface. A workaround for that would be to use profiler or extended events to capture the queries being executed by the user interface for the specific reports you need. You could even do this capture in a non-prod environment, as the queries should be the same.

---

Warning: this is potentially a really bad idea. There's a reason you can't normally write to these tables. Special thanks to Forrest for mentioning the possibility to me.

If you really want to be able to use the user interface, you can actually load the base Query Store tables with data while connecting via the DAC. Here's what worked for me.

Reminder: you have to be using a DAC connection to do this, otherwise you'll get errors related to the sys.plan_persist_* tables not existing

USE [master];
GO
CREATE DATABASE [Sandbox];
GO

USE [YourSourceDatabaseWithTheQueryStoreInfo];
GO

BEGIN TRANSACTION;

INSERT INTO Sandbox.sys.plan_persist_runtime_stats SELECT * FROM sys.plan_persist_runtime_stats;
INSERT INTO Sandbox.sys.plan_persist_runtime_stats_interval SELECT * FROM sys.plan_persist_runtime_stats_interval;
INSERT INTO Sandbox.sys.plan_persist_plan SELECT * FROM sys.plan_persist_plan;
INSERT INTO Sandbox.sys.plan_persist_query SELECT * FROM sys.plan_persist_query;
INSERT INTO Sandbox.sys.plan_persist_query_text SELECT * FROM sys.plan_persist_query_text;
INSERT INTO Sandbox.sys.plan_persist_wait_stats SELECT * FROM sys.plan_persist_wait_stats;
INSERT INTO Sandbox.sys.plan_persist_context_settings SELECT * FROM sys.plan_persist_context_settings

COMMIT TRANSACTION;
GO

USE [master];
GO
ALTER DATABASE [Sandbox] SET QUERY_STORE = ON (OPERATION_MODE = READ_ONLY);

Note: if you're on SQL Server 2016, you'll need to remove the line about wait stats - that catalog view wasn't added until SQL Server 2017

After doing that, I was able to use the Query Store UI in SSMS to view info on the queries from the source database. Neat!

It's important to load the data into the Sandbox database with Query Store off, and then turn Query Store on in read only mode. Otherwise QS ended up in an error state, and this was written to the SQL Server error log:

Error: 12434, Severity: 20, State: 56.
The Query Store in database Sandbox is invalid, possibly due to schema or catalog inconsistency.

I also noticed that this doesn't work if there are in-memory OLTP (Hekaton) tables in the source database. No matter what I do, Query Store ends up in the "Error" state with this message in the error log:

Error: 5571, Severity: 16, State: 2.
Internal FILESTREAM error: failed to access the garbage collection table.

You may be able to work around that by adding a memory-optimized filegroup to the Sandbox database, I haven't tried that yet.

Answer 2

As a supplement to the great answer by Josh Darnell I read through all the descriptions of the data views that are being exported into tables. The following code adds the primary keys, clustered indexes and foreign keys as described in the Microsoft documents. It should help with queries against the data.

----------------------------------------------------------------
--Add primary key,  clustered indexes and foreign keys
-----------------------------------------------------------

Use Admin
ALTER TABLE query_context_settings ADD CONSTRAINT PK_context_settings_id PRIMARY KEY CLUSTERED (context_settings_id);
ALTER TABLE query_store_plan ADD CONSTRAINT PK_plan_id PRIMARY KEY CLUSTERED (plan_id);
ALTER TABLE query_store_query ADD CONSTRAINT PK_query_id PRIMARY KEY CLUSTERED (query_id);
ALTER TABLE query_store_query_text ADD CONSTRAINT PK_query_text_id PRIMARY KEY CLUSTERED (query_text_id);
-- query_store_runtime_stats -- Has foreign keys but the "primary key - 'runtime_stats_id'" is not unique in run time. Only add for historical data
ALTER TABLE query_store_runtime_stats ADD CONSTRAINT PK_runtime_stats_id PRIMARY KEY CLUSTERED (runtime_stats_id);
ALTER TABLE query_store_runtime_stats_interval ADD CONSTRAINT PK_runtime_stats_interval_id PRIMARY KEY CLUSTERED (runtime_stats_interval_id);
-- query_store_wait_stats the "primary key - 'wait_stats_id'" is not unique in run time. Only add for historical data
ALTER TABLE query_store_wait_stats ADD CONSTRAINT PK_wait_stats_id PRIMARY KEY CLUSTERED (wait_stats_id);

--Create Foreign Keys

ALTER TABLE query_store_plan ADD CONSTRAINT FK_query_id FOREIGN KEY (query_id)     
    REFERENCES query_store_query (query_id)     
    ON DELETE CASCADE    
    ON UPDATE CASCADE    
;    
GO   

ALTER TABLE query_store_query ADD CONSTRAINT FK_query_text_id FOREIGN KEY (query_text_id)     
    REFERENCES query_store_query_text (query_text_id)     
    ON DELETE CASCADE    
    ON UPDATE CASCADE    
;    
GO  

 ALTER TABLE query_store_query ADD CONSTRAINT FK_context_settings_id FOREIGN KEY (context_settings_id)     
    REFERENCES query_context_settings (context_settings_id)     
    ON DELETE CASCADE    
    ON UPDATE CASCADE    
;    
GO  

 ALTER TABLE query_store_runtime_stats ADD CONSTRAINT FK_plan_id FOREIGN KEY (plan_id)     
    REFERENCES query_store_plan (plan_id)     
    ON DELETE CASCADE    
    ON UPDATE CASCADE    
;    
GO  

 ALTER TABLE query_store_runtime_stats ADD CONSTRAINT FK_runtime_stats_interval_id FOREIGN KEY (runtime_stats_interval_id)     
    REFERENCES query_store_runtime_stats_interval (runtime_stats_interval_id)     
    ON DELETE CASCADE    
    ON UPDATE CASCADE    
;    
GO  

 ALTER TABLE query_store_wait_stats ADD CONSTRAINT FK_2_plan_id FOREIGN KEY (plan_id)     
    REFERENCES query_store_plan (plan_id)     
    ON DELETE CASCADE    
    ON UPDATE CASCADE    
;    
GO  

 ALTER TABLE query_store_wait_stats ADD CONSTRAINT FK_2_runtime_stats_interval_id FOREIGN KEY (runtime_stats_interval_id)     
    REFERENCES query_store_runtime_stats_interval (runtime_stats_interval_id)     
    ON DELETE CASCADE    
    ON UPDATE CASCADE    
;    
GO  

--Additional Indexes 
--Improve linking plans to queries  
CREATE NONCLUSTERED INDEX NC_QueryID_with_PlanID ON query_store_plan (query_id ASC) INCLUDE (plan_id) 
GO

--To get summary info easier, add query_id column to tables with only the plan_id
--Add the column
ALTER TABLE query_store_runtime_stats
ADD query_id bigint
Go

--Update it
update query_store_runtime_stats
Set query_store_runtime_stats.query_id = query_store_plan.query_id
from   query_store_plan
Inner Join query_store_runtime_stats ON  query_store_runtime_stats.Plan_id = query_store_plan.Plan_id 

--Add an index
CREATE NONCLUSTERED INDEX NC_QueryID_with_PlanID ON query_store_runtime_stats (query_id ASC) INCLUDE (plan_id) 
GO

--Do the Same to query_store_wait_stats
--Add the column
ALTER TABLE query_store_wait_stats
ADD query_id bigint
Go

--Update it
update query_store_wait_stats
Set query_store_wait_stats.query_id = query_store_plan.query_id
from   query_store_plan
Inner Join query_store_wait_stats ON  query_store_wait_stats.Plan_id = query_store_plan.Plan_id 

--Add an index
CREATE NONCLUSTERED INDEX NC_QueryID_with_PlanID ON query_store_wait_stats (query_id ASC) INCLUDE (plan_id) 
GO

Note that both query_store_runtime_stats & query_store_wait_stats do not have primary keys described in the microsoft documents. As this is exported data, I value clustered indexes over multiple statistics in most current interval.

It is unique only for the past runtime statistics intervals. For the currently active interval, there may be multiple rows

The interval is a configuration setting interval_length_minutes listed as the 'Statistics Collection Interval' in the Properties GUI of the Query Store Page, for the database.

Using EXEC sp_query_store_flush_db; before SELECT * INTO Does Not compile the multiple rows in the current runtime statistics interval, to single entries, thus preventing primary keys and clustered indexes on query_store_runtime_stats & query_store_wait_stats in Heavily OLTP databases. In this case before adding the primary keys, clustered indexes and foreign keys (above) delete the most current run time interval with the code below.

In my case I have 30 minute intervals, so if I want all the data up to 6AM, I extract a couple of minutes after 6AM then delete the 6AM+ with the below.

------------------
-- Not in the current run time interval
------------------------
-- Because runtime_stats_id is only unique in past time, I think I want to exclude current run time from the data in admin
-- Current solution delete after import to keep the import as simple as possible. 

Use Admin
Declare @Max_runtime_stats_interval_id bigint
Declare @Max_start_time datetimeoffset(7)
--Declare @Max_end_time datetimeoffset(7) -- No added value at this time.

Select @Max_runtime_stats_interval_id =  MAX (query_store_runtime_stats_interval.runtime_stats_interval_id) from dbo.query_store_runtime_stats_interval
Select @Max_start_time = query_store_runtime_stats_interval.start_time from dbo.query_store_runtime_stats_interval where query_store_runtime_stats_interval.runtime_stats_interval_id = @Max_runtime_stats_interval_id
--Select @Max_end_time = query_store_runtime_stats_interval.end_time from dbo.query_store_runtime_stats_interval where query_store_runtime_stats_interval.runtime_stats_interval_id = @Max_runtime_stats_interval_id

Print @Max_runtime_stats_interval_id
Print @Max_start_time
--Print @Max_end_time

Delete from dbo.query_store_runtime_stats where runtime_stats_interval_id = @Max_runtime_stats_interval_id

Delete from dbo.query_store_runtime_stats_interval where runtime_stats_interval_id = @Max_runtime_stats_interval_id

Delete from dbo.query_store_plan  where initial_compile_start_time > @Max_start_time

--This should be ok, but there was not a query that met the exclude criteria in inital test data. 
Delete from dbo.query_store_query where initial_compile_start_time > @Max_start_time

Delete from dbo.query_store_wait_stats where runtime_stats_interval_id = @Max_runtime_stats_interval_id

--dbo.query_store_query_text -- No time fields, we are excluding new quiries not sure if we need to also exclude their text, more work for little added value
--dbo.query_context_settings -- Does not need to be filtered

Answer 3

I wonder if there is a way to use the clone database option and some classic jiggery pokery to keep the data for analysis in the QS GUI.

1. Clear down QS in restored DB in test environment
2. Run baseline testing
3. Clone DB to BaselineDB
4. Restore main test DB again, clearing down QS
5. Run updates and test routine
6. Clone DB to PostUpdatesDB
7. In cloned DBs, rename query store tables
8. Create views of original QS table names and union the renamed QS tables across the cloned DBs. Or use synonyms.

Not sure if that would work, just food for thought