Melhores práticas para gerenciar o Repositório de Consultas

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Este artigo descreve o gerenciamento do Repositório de Consultas do SQL Server e os recursos relacionados.

• Para obter mais informações configuração e administração com o Repositório de Consultas, confira Monitoramento do desempenho usando o Repositório de Consultas.

Observação

No SQL Server 2022 (16.x), o Repositório de Consultas já está habilitado por padrão para todos os bancos de dados do SQL Server recém-criados para ajudar a controlar melhor o histórico de desempenho, solucionar problemas relacionados ao plano de consulta e habilitar novos recursos de processador de consulta.

Padrões do Repositório de Consultas no banco de dados SQL do Azure

Esta seção descreve os padrões de configuração ideais no Banco de Dados SQL do Azure projetados para garantir a operação confiável do Repositório de Consultas e recursos dependentes. A configuração padrão é otimizada para coleta de dados contínua, ou seja, tempo mínimo gasto nos estados OFF/READ_ONLY. Para obter mais informações sobre todas as opções de Repositório de Consultas disponíveis, confira Opções do ALTER DATABASE SET (Transact-SQL).

Configuração	Descrição	Padrão	Comentário
MAX_STORAGE_SIZE_MB	Especifica o limite para o espaço de dados que o Repositório de Consultas ocupará no banco de dados do cliente	100 antes do SQL Server 2019 (15.x) 1000 a partir do SQL Server 2019 (15.x)	Imposto para novos bancos de dados
INTERVAL_LENGTH_MINUTES	Define o tamanho da janela de tempo durante o qual as estatísticas de runtime coletadas para planos de consulta são agregadas e persistidas. Cada plano de consulta ativa tem no máximo uma linha por um período de tempo definido com esta configuração	60	Imposto para novos bancos de dados
STALE_QUERY_THRESHOLD_DAYS	Política de limpeza com base em tempo que controla o período de retenção de estatísticas de runtime persistidas e consultas inativas	30	Imposto para novos bancos de dados e bancos de dados padrão anteriores (367)
SIZE_BASED_CLEANUP_MODE	Especifica se a limpeza automática de dados ocorrerá quando o tamanho dos dados do Repositório de Consultas aproximar-se do limite	AUTO	Imposto para todos os bancos de dados
QUERY_CAPTURE_MODE	Especifica se todas as consultas ou apenas um subconjunto de consultas será controlado	AUTO	Imposto para todos os bancos de dados
DATA_FLUSH_INTERVAL_SECONDS	Especifica o período máximo durante o qual as estatísticas de runtime coletadas são mantidas na memória antes de liberar para disco	900	Imposto para novos bancos de dados

Importante

Esses padrões serão aplicados automaticamente na fase final de ativação do Repositório de Consultas em um banco de dados SQL do Azure. Depois de habilitado, o banco de dados SQL do Azure não alterará os valores de configuração definidos pelos clientes, a menos que eles afetem negativamente a carga de trabalho primária ou as operações confiáveis do Repositório de Consultas.

Observação

O Repositório de Consultas não pode ser desabilitado no banco de dados individual do Banco de Dados SQL do Azure e no Pool Elástico. Executar ALTER DATABASE [database] SET QUERY_STORE = OFF retornará o aviso 'QUERY_STORE=OFF' is not supported in this version of SQL Server.

Se você quiser manter suas configurações personalizadas, use ALTER DATABASE com opções de Repositório de Consultas para reverter a configuração ao estado anterior. Confira Práticas recomendadas com o Repositório de Consultas para saber como escolher os parâmetros de configuração ideais.

Definir o Modo de Captura do Repositório de Consultas ideal

Mantenha os dados mais relevantes no Repositório de Consultas. A tabela a seguir descreve os cenários típicos para cada Modo de Captura do Repositório de Consultas:

Modo de captura do Repositório de Consultas	Cenário
Todos	Analise sua carga de trabalho plenamente quanto a todas as formas das consultas, suas frequências de execução e outras estatísticas. Identifique novas consultas na carga de trabalho. Detecte se consultas ad hoc são usadas para identificar oportunidades de parametrização automática ou pelo usuário. Observação: esse é o modo de captura padrão do SQL Server 2016 (13.x) e do SQL Server 2017 (14.x).
Auto	Concentre a atenção em consultas relevantes e acionáveis. Um exemplo são as consultas executadas regularmente ou que consomem muitos recursos. Observação: esse é o modo de captura padrão no SQL Server 2019 (15.x) e versões posteriores.
Nenhuma	Você já capturou o conjunto de consultas que deseja monitorar no runtime e deseja eliminar as distrações que outras consultas podem causar. Nenhuma é adequada para ambientes de teste e parâmetros de avaliação. Nenhuma também é adequado para fornecedores de software que entregam o Repositório de Consultas configurado para monitorar a sua carga de trabalho do aplicativo. Nenhuma deve ser usada com cuidado, pois você poderá perder a oportunidade de acompanhar e otimizar novas consultas importantes. Evite usar Nenhuma, a menos que tenha um cenário específico que precise desse modo.
Personalizado	O SQL Server 2019 (15.x) introduziu um modo de captura personalizado pelo comando ALTER DATABASE ... SET QUERY_STORE. Embora seja recomendado usar o padrão Auto, se ainda houver preocupações com a sobrecarga que o Repositório de Consultas pode introduzir, os administradores de banco de dados poderão usar políticas de captura personalizadas para ajustar melhor o comportamento de captura do Repositório de Consultas. Veja mais informações e recomendações em Políticas de captura personalizadas, mais adiante neste artigo. Para obter mais informações sobre essa sintaxe, confira Opções ALTER DATABASE SET.

Observação

Cursores, consultas dentro de procedimentos armazenados e consultas compiladas nativamente sempre são capturados quando o Modo de Captura do Repositório de Consultas está definido como Tudo, Automático ou Personalizado. Para capturar consultas compiladas nativamente, habilite a coleta de estatísticas por consulta usando sys.sp_xtp_control_query_exec_stats.

Manter os dados mais relevantes no Repositório de Consultas

Configure o Repositório de Consultas para conter somente os dados relevantes para que ele seja executado continuamente e proporcione uma ótima experiência de solução de problemas com impacto mínimo sobre sua carga de trabalho normal.

A tabela a seguir fornece as práticas recomendadas:

Melhor prática	Configuração
Limite dados históricos retidos.	Configure a política com base em tempo para ativar a limpeza automática.
Por filtragem, desconsidere consultas não relevantes.	Configure o Modo de Captura do Repositório de Consultas como Auto.
Exclua consultas menos relevantes ao atingir o tamanho máximo.	Ative a política de limpeza com base em tamanho.

Políticas de captura personalizadas

Quando o modo de captura do Repositório de Consultas CUSTOM está habilitado, as configurações adicionais do Repositório de Consultas ficam disponíveis em uma nova configuração de política de captura do Repositório de Consultas para ajustar a coleção de dados em um servidor específico.

As novas configurações personalizadas definem o que acontece durante o limite de tempo da política de captura interna. Esse é um limite de tempo durante o qual as condições configuráveis são avaliadas e, se alguma for verdadeira, a consulta será qualificada para captura pelo Repositório de Consultas.

O Modo de captura do Repositório de Consultas especifica a política de captura de consultas para o Repositório de Consultas.

• All: captura todas as consultas. Essa opção é o padrão no SQL Server 2016 (13.x) e no SQL Server 2017 (14.x).
• Auto: consultas incomuns e consultas com duração de compilação e execução insignificantes são ignoradas. Os limites para a duração da execução de contagem, da compilação e do runtime são determinados internamente. A partir do SQL Server 2019 (15.x), essa é a opção padrão.
• None: o Repositório de Consultas para de capturar novas consultas.
• Pesonalizada: permite controle adicional e o ajuste fino da política de coleção de dados. As novas configurações personalizadas definem o que acontece durante o limite de tempo da política de captura interna. Esse é um limite de tempo durante o qual as condições configuráveis são avaliadas e, se alguma for verdadeira, a consulta será qualificada para captura pelo Repositório de Consultas.

Considere ajustar uma política de captura personalizada apropriada para seu ambiente quando:

• O banco de dados for muito grande.
• O banco de dados tiver um número grande de consultas ad hoc exclusivas.
• O banco de dados tiver limitações específicas de tamanho ou crescimento.

SSMS

Baixe a versão mais recente do SQL Server Management Studio (SSMS)

Para exibir as configurações atuais no Management Studio:

1. No Pesquisador de Objetos do SQL Server Management Studio, clique com o botão direito no banco de dados.
2. Selecione Propriedades.
3. Escolha Repositório de Consultas. Na página Repositório de Consultas, verifique se o Modo de operação (solicitado) é de leitura/gravação.
4. Altere o Modo de captura do Repositório de Consultas para Personalizado.
5. Os quatro campos de política de captura em Política de captura do Repositório de Consultas já estão habilitados e configuráveis.

T-SQL

Usar ALTER DATABASE ... Sintaxe SET para configurar QUERY_CAPTURE_MODE = CUSTOM e especificar opções para QUERY_CAPTURE_POLICY. Confira os seguintes exemplos de T-SQL e as opções ALTER DATABASE SET (Transact-SQL) para ver detalhes sobre as opções de política de captura personalizada.

Exemplo de políticas de captura personalizada

O exemplo a seguir configura QUERY_CAPTURE_MODE como AUTO e configura um modo de captura personalizado. Cada item a seguir configura as políticas de captura personalizadas com o respectivo valor padrão no SQL Server 2022 (16.x). Considere ajustar esses valores para reduzir o número de consultas capturadas e, assim, reduzir o volume em disco do Repositório de Consultas. Recomenda-se alterar esses valores aos poucos em pequenos incrementos.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

A consulta de exemplo a seguir altera um Repositório de Consultas existente para usar uma política de captura personalizada que substitui as configurações padrão de EXECUTION_COUNT e de TOTAL_COMPILE_CPU_TIME_MS.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        EXECUTION_COUNT = 100,
        TOTAL_COMPILE_CPU_TIME_MS = 10000
      )
    );

Tamanho máximo do Repositório de Consultas

O valor do tamanho máximo padrão do Repositório de Consultas é 1000 MB, a partir do SQL Server 2019 (15.x). Nas versões anteriores, o padrão era 100 MB. Aumentar o limite do tamanho máximo do Repositório de Consultas é adequado em um banco de dados ocupado com muitos planos de consulta exclusivos. Ajustar a política de captura (confira a seção anterior) é uma consideração mais importante para limitar o tamanho em disco do Repositório de Consultas e impedir que o Repositório de Consultas entre no modo READ_ONLY. Conforme o Repositório de Consultas coleta consultas, planos de execução e estatísticas, seu tamanho no banco de dados cresce até esse limite ser atingido. Quando isso acontece, o Repositório de Consultas automaticamente altera o modo de operação para READ_ONLY e para de coletar novos dados, o que significa que a análise de desempenho não é mais precisa.

• No SQL Server e na Instância Gerenciada de SQL do Azure, o limite MAX_STORAGE_SIZE_MB não é aplicado estritamente.
• No Banco de Dados SQL do Azure, o valor MAX_STORAGE_SIZE_MB máximo permitido é de 10.240 MB.

O tamanho do armazenamento é verificado somente quando o Repositório de Consultas grava dados no disco. Esse intervalo é definido pela opção DATA_FLUSH_INTERVAL_SECONDS ou pela opção da caixa de diálogo do Repositório de Consultas Management Studio, Intervalo de Liberação de Dados.

• O valor padrão do intervalo é de 900 segundos (ou 15 minutos).
• Se o Repositório de Consultas tiver violado o limite MAX_STORAGE_SIZE_MB entre as verificações de tamanho do armazenamento, ele fará a transição para o modo somente leitura.
• Se o SIZE_BASED_CLEANUP_MODE estiver habilitado, o mecanismo de limpeza que impõe o limite MAX_STORAGE_SIZE_MB também será disparado.
  • Depois que o espaço suficiente for limpo, o modo de Repositório de Consultas alternará automaticamente para o modo READ_WRITE.

Para mais informações, veja ALTER DATABASE SET OPTION MAX_STORAGE_SIZE_MB.

Intervalo de limpeza de dados (minutos)

O intervalo de liberação de dados define a frequência antes que as estatísticas de runtime coletadas fiquem persistentes no disco. O valor está em minutos SQL Server Management Studio, mas é expresso em segundos no Transact-SQL. O padrão é 15 minutos (900 segundos).

• Aumentar o intervalo de liberação de dados pode reduzir o impacto geral de E/S de armazenamento do Repositório de Consultas, mas pode fazer com que a carga de trabalho de E/S de armazenamento tenha mais picos, com impacto menor, porém mais pesado, no uso do disco. Considere usar um valor mais alto se a carga de trabalho não gerar um grande número de planos e consultas diferentes ou se você puder aguardar mais tempo para manter os dados antes do desligamento de um banco de dados.
• Diminuir o intervalo de liberação de dados diminui a quantidade de dados do Repositório de Consultas perdidos em caso de desligamento, perda de energia ou failover. Isso também pode suavizar o impacto de E/S de armazenamento do Repositório de Consultas gravando no disco com mais frequência, porém com menos dados.

Observação

O uso do sinalizador de rastreamento 7745 impede que os dados do Repositório de Consultas sejam gravados em disco em caso de um failover ou comando de desligamento. Para obter mais informações, consulte Usar o Repositório de Consultas em servidores críticos.

Modificar padrões do Repositório de Consultas

Configure o Repositório de Consultas com base na sua carga de trabalho e nos requisitos para solução de problemas de desempenho. Os parâmetros padrão são bons o bastante para iniciar, mas você deve monitorar o comportamento do Repositório de Consultas ao longo do tempo e ajustar sua configuração adequadamente.

Exibir as configurações atuais do Repositório de Consultas

Exiba as configurações atuais do Repositório de Consultas no SQL Server Management Studio (SSMS) ou em T-SQL.

SSMS

Baixe a versão mais recente do SQL Server Management Studio (SSMS)

Para exibir as configurações atuais no Management Studio:

1. No Pesquisador de Objetos do SQL Server Management Studio, clique com o botão direito no banco de dados.
2. Selecione Propriedades.
3. Escolha Repositório de Consultas.

T-SQL

Execute este script T-SQL para obter as informações mais recentes sobre o tamanho do Repositório de Consultas:

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
 max_storage_size_mb, readonly_reason
FROM sys.database_query_store_options;

O script a seguir define um novo valor para Tamanho Máximo (MB):

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);

Use SQL Server Management Studio ou Transact-SQL para definir um valor diferente para o Intervalo de Liberação de Dados:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);

Intervalo de coleção de estatísticas: define o nível de granularidade da estatística de runtime coletada, expressa em minutos. O padrão é de 60 minutos. Considere usar um valor menor se você precisar de granularidade mais fina ou menos tempo para detectar e atenuar problemas. Lembre-se de que o valor afeta diretamente o tamanho dos dados do Repositório de Consultas. Use SQL Server Management Studio ou Transact-SQL para definir um valor diferente para o Intervalo de Coleta de Estatísticas:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);

Limite de consulta obsoleto (dias): política de limpeza com base em tempo que controla o período de retenção de estatísticas de runtime persistentes e consultas inativas. Por padrão, o Repositório de Consultas está configurado para manter os dados por 30 dias, o que pode ser longo demais para seu cenário.

Evite manter dados históricos que você não planeja usar. Essa prática reduz as alterações ao status somente leitura. O tamanho dos dados do Repositório de Consultas e o tempo para detectar e reduzir o problema serão mais previsíveis. Use Management Studio ou o script a seguir para configurar a política de limpeza com base em tempo:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90));

Modo de limpeza com base no tamanho: especifica se a limpeza automática de dados ocorrerá quando o tamanho dos dados no Repositório de Consultas se aproximar do limite. Ative a limpeza com base no tamanho para que o repositório de consultas seja sempre executado no modo de leitura-gravação e colete sempre os dados mais recentes. Sob cargas de trabalho pesadas, não há nenhuma garantia de que a limpeza do Repositório de Consultas manterá de maneira consistente o tamanho dos dados abaixo do limite. É possível que a limpeza de dados automática fique para trás e alterne (temporariamente) para o modo somente leitura.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

Modo de captura do Repositório de Consultas: especifica a política de captura de consultas para o repositório de consultas.

• All: captura todas as consultas. Essa opção é o padrão no SQL Server 2016 (13.x) e no SQL Server 2017 (14.x).
• Auto: consultas incomuns e consultas com duração de compilação e execução insignificantes são ignoradas. Os limites para a duração da execução de contagem, da compilação e do runtime são determinados internamente. A partir do SQL Server 2019 (15.x), essa é a opção padrão.
• None: o Repositório de Consultas para de capturar novas consultas.
• Pesonalizada: permite controle adicional e o ajuste fino da política de coleção de dados. As novas configurações personalizadas definem o que acontece durante o limite de tempo da política de captura interna. Esse é um limite de tempo durante o qual as condições configuráveis são avaliadas e, se alguma for verdadeira, a consulta será qualificada para captura pelo Repositório de Consultas.

Importante

Cursores, consultas dentro de procedimentos armazenados e consultas compiladas nativamente sempre são capturados quando o Modo de Captura do Repositório de Consultas está definido como Tudo, Automático ou Personalizado. Para capturar consultas compiladas nativamente, habilite a coleta de estatísticas por consulta usando sys.sp_xtp_control_query_exec_stats.

O script a seguir define QUERY_CAPTURE_MODE como AUTO:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

Exemplos

O exemplo a seguir configura QUERY_CAPTURE_MODE como AUTO e configura as outras opções recomendadas no SQL Server 2016 (13.x):

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60
    );

O exemplo a seguir define QUERY_CAPTURE_MODE como AUTO e define as outras opções recomendadas no SQL Server 2017 (14.x) para incluir estatísticas de espera:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON
    );

O exemplo a seguir configura a política de captura CUSTOM para os padrões do SQL Server 2019 (15.x), em vez do novo modo de captura AUTO padrão. Veja mais informações sobre opções e padrões de política de captura personalizada em <query_capture_policy_option_list>.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Manutenção do Repositório de Consultas

Esta seção fornece algumas diretrizes sobre como gerenciar recursos do próprio repositório de consultas.

Estado do Repositório de Consultas

O Repositório de Consultas armazena seus dados dentro do banco de dados do usuário e é por isso que ele tem limite de tamanho (configurado com MAX_STORAGE_SIZE_MB). Se os dados no repositório de consultas atingirem esse limite, o repositório de consultas alterará automaticamente o status de somente gravação para somente leitura e interromperá a coleta de novos dados.

Consulte sys.database_query_store_options para determinar se o Repositório de Consultas está ativo no momento e se está coletando estatísticas de runtime ou não.

SELECT actual_state, actual_state_desc, readonly_reason,
    current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

O status do Repositório de Consultas é determinado pela coluna actual_state. Caso não seja o status desejado, a coluna readonly_reason pode fornecer mais informações. Quando o tamanho do Repositório de Consultas exceder a cota, o recurso mudará para o modo read_only e fornecerá um motivo. Veja informações sobre os motivos em sys.database_query_store_options.

Opções Obter Repositório de Consultas

Para obter informações detalhadas sobre o status do repositório de consultas, execute o seguinte em um banco de dados do usuário.

SELECT * FROM sys.database_query_store_options;

Definir o intervalo do Repositório de Consultas

Você pode substituir o intervalo para agregar estatísticas de runtime de consulta (o padrão é 60 minutos). O novo valor do intervalo é exposto na exibição sys.database_query_store_options.

ALTER DATABASE <database_name>
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

Não são permitidos valores arbitrários para INTERVAL_LENGTH_MINUTES. Use um dos seguintes intervalos: 1, 5, 10, 15, 30, 60 ou 1.440 minutos.

Observação

Para o Azure Synapse Analytics, não há suporte para a personalização das opções de configuração do Repositório de Consultas, conforme demonstrado nesta seção.

Uso de espaço do Repositório de Consultas

Para verificar o tamanho atual e o limite do Repositório de Consultas, execute a instrução a seguir no banco de dados do usuário.

SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Se o armazenamento do repositório de consultas estiver completo, use a seguinte instrução para ampliar o armazenamento.

ALTER DATABASE <database_name>
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);

Definir as opções do Repositório de Consultas

Você pode definir várias opções de repositório de consultas de uma só vez com uma única instrução ALTER DATABASE.

ALTER DATABASE <database name>
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 3000,
    MAX_STORAGE_SIZE_MB = 500,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 1000,
    WAIT_STATS_CAPTURE_MODE = ON
);

Para obter a lista completa de opções de configuração, confira Opções ALTER DATABASE SET (Transact-SQL).

Limpar o espaço

Tabelas internas do repositório de consultas são criadas no grupo de arquivos PRIMARY durante a criação do banco de dados e essa configuração não pode ser alterada posteriormente. Se você estiver executando sem espaço, limpe os dados antigos do repositório de consultas usando a instrução a seguir.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

Você pode também limpar apenas dados de consulta ad hoc, pois são menos relevantes para otimizações de consulta e análise do plano, mas ocupam a mesma quantidade de espaço.

No Azure Synapse Analytics, a opção de limpar o Repositório de Consultas não está disponível. Os dados dos últimos sete dias são retidos automaticamente.

Excluir consultas ad hoc

Isso limpa as consultas ad hoc e internas do Repositório de Consultas para que ele não fique sem espaço e remova as consultas que realmente precisamos acompanhar.

SET NOCOUNT ON
-- This purges adhoc and internal queries from
-- the Query Store in the current database
-- so that the Query Store does not run out of space
-- and remove queries we really need to track

DECLARE @id int;
DECLARE adhoc_queries_cursor CURSOR
FOR
    SELECT q.query_id
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
    JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
    JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
    WHERE q.is_internal_query = 1  -- is it an internal query then we dont care to keep track of it
       OR q.object_id = 0 -- if it does not have a valid object_id then it is an adhoc query and we don't care about keeping track of it
    GROUP BY q.query_id
    HAVING MAX(rs.last_execution_time) < DATEADD (minute, -5, GETUTCDATE())  -- if it has been more than 5 minutes since the adhoc query ran
    ORDER BY q.query_id;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN
    PRINT 'EXEC sp_query_store_remove_query ' + str(@id);
    EXEC sp_query_store_remove_query @id;
    FETCH NEXT FROM adhoc_queries_cursor INTO @id;
END
CLOSE adhoc_queries_cursor;
DEALLOCATE adhoc_queries_cursor;

Você pode definir seu próprio procedimento com uma lógica diferente para limpar os dados que não são mais necessários.

O exemplo anterior usa o procedimento armazenado estendido sp_query_store_remove_query para remover dados desnecessários. Também é possível:

• Use sp_query_store_reset_exec_stats para limpar as estatísticas de runtime de um determinado plano.
• Use sp_query_store_remove_plan para remover um único plano.

Conteúdo relacionado

• Opções de ALTER DATABASE SET (Transact-SQL)
• Exibições de catálogo do Repositório de Consultas (Transact-SQL)
• Procedimentos armazenados do Repositório de Consultas (Transact-SQL)
• Usar o Repositório de Consultas com OLTP in-memory
• Guia de arquitetura de processamento de consultas
• Dicas do Repositório de Consultas
• Monitorar o desempenho usando o Repositório de Consultas
• Ajustar o desempenho com o Repositório de Consultas
• Armazenamento e análise de consultas históricas no Azure Synapse Analytics