Como posso exportar dados do Repositório de Consultas?

Em primeiro lugar, você pode obter um desempenho aceitável com consultas diretamente nas visualizações do catálogo do repositório de consultas atualizando as estatísticas, adicionando dicas de consulta com guias de plano ou alterando o nível de compatibilidade do banco de dados / CE. Veja as respostas de Forrest e Marian aqui:

Pesquisa interminável no repositório de consultas

Se você estiver no SP1 ou superior, a abordagem mais simples seria usar DBCC CLONEDATABASE- que inclui estatísticas, dados de armazenamento de consultas e objetos de esquema - mas nenhum dos dados reais das tabelas.

Caso contrário, para exportar, uma abordagem seria simples SELECT...INTOdas visualizações do repositório de consultas para o banco de dados "sandbox". Estes são os pontos de vista relevantes .

A abordagem básica seria assim:

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;

O bom dessa abordagem é que:

• você só obterá os dados de que precisa (1000 MB)
• você pode adicionar índices para dar suporte às suas consultas de relatórios, pois são tabelas reais
• eles não terão o comportamento incomum de varredura de memória que leva a um desempenho ruim em relação às visualizações reais (novamente porque são tabelas reais)
  • Observação: as SELECT...INTOconsultas não devem aumentar a CPU como as consultas de relatórios do repositório de consultas integrado, porque elas não terão as junções problemáticas que causam acesso repetido aos TVFs na memória
• você pode manter diferentes versões dos dados (para diferentes níveis de CU, etc) alterando os nomes das tabelas ou adicionando uma coluna às tabelas que indica os dados e/ou versão do SQL Server que foi usada para essa importação

O "contra" dessa abordagem é que você não pode usar a interface do usuário do repositório de consultas. Uma solução alternativa para isso seria usar o criador de perfil ou eventos estendidos para capturar as consultas que estão sendo executadas pela interface do usuário para os relatórios específicos de que você precisa. Você pode até fazer essa captura em um ambiente sem produção, pois as consultas devem ser as mesmas.

Aviso: isso é potencialmente uma péssima ideia. Há uma razão pela qual você normalmente não pode gravar nessas tabelas. Agradecimentos especiais a Forrest por mencionar a possibilidade para mim.

Se você realmente quiser usar a interface do usuário, poderá carregar as tabelas básicas do Query Store com dados enquanto se conecta por meio do DAC . Aqui está o que funcionou para mim.

Lembrete: você precisa estar usando uma conexão DAC para fazer isso, caso contrário, você receberá erros relacionados às sys.plan_persist_*tabelas não existentes

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

Observação: se você estiver no SQL Server 2016, precisará remover a linha sobre estatísticas de espera - essa exibição de catálogo não foi adicionada até o SQL Server 2017

Depois de fazer isso, consegui usar a interface do usuário do repositório de consultas no SSMS para exibir informações sobre as consultas do banco de dados de origem. Organizado!

É importante carregar os dados no banco de dados Sandbox com o Repositório de Consultas desativado e, em seguida, ativar o Repositório de Consultas no modo somente leitura. Caso contrário, o QS acabou em um estado de erro e isso foi gravado no log de erros do SQL Server:

Erro: 12434, Gravidade: 20, Estado: 56.
O Repositório de Consultas no banco de dados Sandbox é inválido, possivelmente devido a inconsistência de esquema ou catálogo.

Também notei que isso não funciona se houver tabelas OLTP (Hekaton) na memória no banco de dados de origem. Não importa o que eu faça, o Repositório de Consultas acaba no estado "Erro" com esta mensagem no log de erros:

Erro: 5571, gravidade: 16, estado: 2.
Erro FILESTREAM interno: falha ao acessar a tabela de coleta de lixo.

Você pode contornar isso adicionando um grupo de arquivos com otimização de memória ao banco de dados Sandbox, ainda não tentei isso.

Como complemento à ótima resposta de Josh Darnell , li todas as descrições das visualizações de dados que estão sendo exportadas para tabelas. O código a seguir adiciona as chaves primárias, índices clusterizados e chaves estrangeiras conforme descrito nos documentos da Microsoft. Deve ajudar com consultas contra os dados.

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

Observe que query_store_runtime_stats e query_store_wait_stats não possuem chaves primárias descritas nos documentos da Microsoft. Como esses dados são exportados, valorizo ​​índices clusterizados em várias estatísticas no intervalo mais atual.

É exclusivo apenas para os intervalos de estatísticas de tempo de execução anteriores. Para o intervalo atualmente ativo, pode haver várias linhas

O intervalo é uma definição de configuração interval_length_minutes listada como o 'Intervalo de Coleta de Estatísticas' na GUI de Propriedades da Página Repositório de Consultas, para o banco de dados.

Usar EXEC sp_query_store_flush_db;antes SELECT * INTO Não compila as várias linhas no intervalo de estatísticas de tempo de execução atual para entradas únicas, evitando chaves primárias e índices clusterizados em query_store_runtime_stats & query_store_wait_stats em bancos de dados OLTP pesados . Nesse caso, antes de adicionar as chaves primárias, índices clusterizados e chaves estrangeiras (acima), exclua o intervalo de tempo de execução mais atual com o código abaixo.

No meu caso, tenho intervalos de 30 minutos, portanto, se quiser todos os dados até as 6h, extraio alguns minutos após as 6h e excluo as 6h + com o abaixo.

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

Gostaria de saber se existe uma maneira de usar a opção de banco de dados clone e algum poker clássico de jiggery para manter os dados para análise na GUI do QS.

1. Limpe o QS no banco de dados restaurado no ambiente de teste
2. Executar testes de linha de base
3. Clonar DB para BaselineDB
4. Restaure o banco de dados de teste principal novamente, limpando o QS
5. Executar atualizações e rotina de testes
6. Clonar DB para PostUpdatesDB
7. Em bancos de dados clonados, renomeie as tabelas do repositório de consultas
8. Crie visualizações de nomes de tabelas QS originais e una as tabelas QS renomeadas nos bancos de dados clonados. Ou use sinônimos.

Não tenho certeza se isso funcionaria, apenas comida para pensar