如何导出查询存储数据？

首先，您可以通过更新统计信息、使用计划指南添加查询提示或更改数据库兼容性级别/CE，直接针对查询存储目录视图进行查询，从而获得可接受的性能。在此处查看 Forrest 和 Marian 的答案：

永无止境的查询商店搜索

如果您使用的是 SP1 或更高版本，最简单的方法是使用DBCC CLONEDATABASE- 其中包括统计信息、查询存储数据和架构对象 - 但没有来自表的实际数据。

否则，对于导出，一种方法是SELECT...INTO从查询存储视图到“沙盒”数据库的简单方法。 以上是相关意见。

基本方法是这样的：

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;

这种方法的好处在于：

• 你只会得到你需要的数据（1000 MB）
• 您可以添加索引来支持您的报告查询，因为这些是实际的表
• 他们不会有不寻常的内存扫描行为导致对实际视图的性能不佳（再次因为它们是实际的表）
  • 注意：SELECT...INTO查询不应像内置查询存储报告查询那样占用 CPU，因为它们不会有导致重复访问内存中 TVF 的有问题的连接
• 您可以通过更改表名来保留不同版本的数据（对于不同的 CU 级别等），或者在表中添加一个列来指示用于该导入的数据和/或 SQL Server 版本

这种方法的“缺点”是您不能使用查询存储用户界面。一种解决方法是使用分析器或扩展事件来捕获用户界面正在为您需要的特定报告执行的查询。您甚至可以在非生产环境中进行此捕获，因为查询应该是相同的。

警告：这可能是一个非常糟糕的主意。 您通常无法写入这些表是有原因的。特别感谢Forrest向我提到了这种可能性。

如果您真的希望能够使用用户界面，您实际上可以在通过 DAC 连接时加载带有数据的基本 Query Store 表。这对我有用。

提醒：您必须使用 DAC 连接来执行此操作，否则您将收到与sys.plan_persist_*不存在的表相关的错误

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

注意：如果您使用的是 SQL Server 2016，则需要删除有关等待统计信息的行 - 直到 SQL Server 2017 才添加目录视图

之后，我可以使用 SSMS 中的查询存储 UI 来查看来自源数据库的查询信息。整洁的！

在关闭Query Store的情况下将数据加载到 Sandbox 数据库中，然后以只读模式打开 Query Store 非常重要。 否则 QS 最终会处于错误状态，并将其写入 SQL Server 错误日志：

错误：12434，严重性：20，状态：56
。数据库沙箱中的查询存储无效，可能是由于架构或目录不一致。

我还注意到，如果源数据库中有内存中的 OLTP (Hekaton) 表，这将不起作用。无论我做什么，查询存储最终都处于“错误”状态，并在错误日志中显示以下消息：

错误：5571，严重性：16，状态：2。
内部 FILESTREAM 错误：无法访问垃圾收集表。

您可以通过向沙盒数据库添加内存优化文件组来解决这个问题，我还没有尝试过。

作为对Josh Darnell 出色答案的补充，我通读了导出到表中的数据视图的所有描述。以下代码添加了 Microsoft 文档中描述的主键、聚集索引和外键。它应该有助于查询数据。

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

请注意，query_store_runtime_stats和query_store_wait_stats都没有 microsoft 文档中描述的主键。由于这是导出的数据，因此我在大多数当前时间间隔内对多个统计数据进行了聚集索引的评估。

它仅对过去的运行时统计间隔是唯一的。对于当前活动的区间，可能有多行

间隔是数据库的配置设置interval_length_minutes在查询存储页面的属性 GUI 中列为“统计信息收集间隔”。

使用EXEC sp_query_store_flush_db;beforeSELECT * INTO 不会将当前运行时统计间隔中的多行编译为单个条目，从而防止在重型 OLTP数据库中 query_store_runtime_stats 和 query_store_wait_stats 上的主键和聚集索引。在这种情况下，在添加主键、聚集索引和外键（上）之前，使用下面的代码删除最新的运行时间间隔。

在我的情况下，我有 30 分钟的间隔，所以如果我想要所有数据到早上 6 点，我会在早上 6 点之后提取几分钟，然后使用下面的内容删除 6AM+。

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

我想知道是否有办法使用克隆数据库选项和一些经典的 jiggery pokery 将数据保留在 QS GUI 中进行分析。

1. 在测试环境中清除恢复的数据库中的 QS
2. 运行基线测试
3. 将 DB 克隆到 BaselineDB
4. 再次恢复主测试数据库，清除 QS
5. 运行更新和测试例程
6. 将数据库克隆到 PostUpdatesDB
7. 在克隆的数据库中，重命名查询存储表
8. 创建原始 QS 表名称的视图，并在克隆的数据库中合并重命名的 QS 表。或者使用同义词。

不确定这是否有效，只是思考的食物