Partilhar via

Práticas recomendadas para gerenciar o Repositório de Consultas

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azure

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

Observação

No SQL Server 2022 (16.x), o Repositório de Consultas agora 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 do processador de consultas.

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 uma operação confiável do Repositório de Consultas e dos recursos dependentes. A configuração padrão é otimizada para coleta contínua de dados, ou seja, tempo mínimo gasto em estados OFF/READ_ONLY. Para obter mais informações sobre todas as opções disponíveis do Query Store, consulte as opções de ALTER DATABASE SET em (Transact-SQL).

Configuração Descrição Padrão Comentar
Tamanho máximo de armazenamento em MB (MAX_STORAGE_SIZE_MB) Especifica o limite para o espaço de dados que o Repositório de Consultas pode ocupar dentro do banco de dados do cliente 100 antes do SQL Server 2019 (15.x)
1000 a partir do SQL Server 2019 (15.x)		 Aplicado a novas bases de dados
COMPRIMENTO_INTERVALO_MINUTOS Define o tamanho da janela de tempo durante a qual as estatísticas de tempo de execução coletadas para planos de consulta são agregadas e persistidas. Cada plano de consulta ativo tem no máximo uma linha por um período de tempo definido com essa configuração 60 Aplicado para novos bancos de dados
LIMITE_DE_DIAS_CONSULTA_OBSOLETA Política de limpeza baseada em tempo que controla o período de retenção de estatísticas de tempo de execução persistentes e consultas inativas 30 Aplicado a novas bases de dados e bases de dados com padrão anterior (367)
MODO_DE_LIMPEZA_BASEADO_NO_TAMANHO Especifica se a limpeza automática de dados ocorre quando o tamanho dos dados do Repositório de Consultas se aproxima do limite AUTOMÁTICO Aplicado para todos os bancos de dados
MODO_CAPTURA_DE_CONSULTA Especifica se todas as consultas ou apenas um subconjunto de consultas são acompanhadas AUTOMÁTICO Aplicado para todos os bancos de dados
INTERVALO_DE_DESCARGA_DE_DADOS_SEGUNDOS Especifica o período máximo durante o qual as estatísticas de tempo de execução capturadas são mantidas na memória, antes de serem liberadas para o disco 900 Aplicado para novos bancos de dados

Importante

Esses padrões são aplicados automaticamente no estágio final da 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 principal 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 SQL do Azure, no banco de dados único e no Pool Elástico. A execução ALTER DATABASE [database] SET QUERY_STORE = OFF retornará o aviso 'QUERY_STORE=OFF' is not supported in this version of SQL Server.

Para manter as tuas configurações personalizadas, usa ALTER DATABASE com as opções do Query Store para reverter a configuração para o estado anterior. Confira Melhores Práticas com o Repositório de Consultas para aprender a 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 cenários típicos para cada Query Store Capture Mode:

Modo de Captura do Repositório de Consultas Cenário
Todos os Analise sua carga de trabalho minuciosamente em termos de todas as formas de consultas e suas frequências de execução e outras estatísticas.

Identifique novas consultas em sua carga de trabalho.

Detete se consultas ad hoc são usadas para identificar oportunidades de parametrização automática ou de usuário.

Nota: Este é o modo de captura padrão no SQL Server 2016 (13.x) e no SQL Server 2017 (14.x).
Auto Concentre sua atenção em consultas relevantes e acionáveis. Um exemplo são as consultas que são executadas regularmente ou que têm um consumo de recursos significativo.

Nota: No SQL Server 2019 (15.x) e versões posteriores, este é o modo de captura padrão.
Nenhum Você já capturou o conjunto de consultas que deseja monitorar em tempo de execução e deseja eliminar as distrações que outras consultas podem introduzir.

Nenhum é adequado para ambientes de teste e benchmarking.

Nenhum também é apropriado para fornecedores de software que fornecem a configuração do Repositório de Consultas configurada para monitorar a carga de trabalho do aplicativo.

Nenhum deve ser usado com cautela porque você pode perder a oportunidade de rastrear e otimizar novas consultas importantes. Evite usar None a menos que você tenha um cenário específico que o exija.
personalizado O SQL Server 2019 (15.x) introduziu um modo de captura personalizado sob o comando ALTER DATABASE ... SET QUERY_STORE. Embora Auto seja padrão e recomendado, se ainda houver alguma preocupação sobre a sobrecarga que o Repositório de Consultas pode introduzir, os administradores de banco de dados podem usar políticas de captura personalizadas para ajustar ainda mais o comportamento de captura do Repositório de Consultas. Para obter mais informações e recomendações, consulte Políticas de captura personalizadas mais adiante neste artigo. Para obter mais informações sobre essa sintaxe, consulte ALTER DATABASE SET Options.

Observação

Cursores, consultas dentro de procedimentos armazenados e consultas compiladas nativamente são sempre capturados quando o Modo de Captura do Repositório de Consultas está definido como Todos, Automáticoou 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 apenas os dados relevantes para que ele seja executado continuamente e forneça uma ótima experiência de solução de problemas com um impacto mínimo na sua carga de trabalho regular.

A tabela a seguir fornece práticas recomendadas:

Melhores práticas Configuração
Limite os dados históricos retidos. Configure a política baseada em tempo para ativar a limpeza automática.
Filtre consultas não relevantes. Configure Modo de Captura do Repositório de Consultas para Automático.
Exclua consultas menos relevantes quando o tamanho máximo for atingido. Ative a política de limpeza baseada no tamanho.

Políticas de captura personalizadas

Quando o Modo de Captura do Repositório de Consultas PERSONALIZADO está habilitado, configurações adicionais do Repositório de Consultas estão disponíveis em uma nova configuração de política de captura do Repositório de Consultas para ajustar a coleta 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. Este é um limite de tempo durante o qual as condições configuráveis são avaliadas e, se alguma for verdadeira, a consulta é qualificada para ser capturada pelo Repositório de Consultas.

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

  • Tudo: 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 pouco frequentes e consultas com duração de compilação e execução insignificante são ignoradas. Os limites para contagem de execução, compilação e duração do tempo de execução são determinados internamente. A partir do SQL Server 2019 (15.x), essa é a opção padrão.
  • Nenhum: O Query Store para de capturar novas consultas.
  • Custom: Permite controle adicional e a capacidade de ajustar finamente a política de recolha de dados. As novas configurações personalizadas definem o que acontece durante o limite de tempo da política de captura interna. Este é um limite de tempo durante o qual as condições configuráveis são avaliadas e, se alguma for verdadeira, a consulta é qualificada para ser capturada pelo Repositório de Consultas.

O ajuste de uma política de captura personalizada apropriada para seu ambiente deve ser considerado quando:

  • A base de dados é muito grande.
  • O banco de dados tem um grande número de consultas exclusivas e ad hoc.
  • O banco de dados tem limitações específicas de tamanho ou crescimento.

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 do mouse no banco de dados.
  2. Selecione Propriedades.
  3. Selecione Repositório de Consultas. Na página Repositório de Consultas, verifique se o Modo de Operação de (Solicitado) está Lerde gravação .
  4. Altere o Modo de Captura do Repositório de Consultas para Personalizado .
  5. Observe que os quatro campos de política de captura em Política de Captura do Repositório de Consultas agora estão habilitados e configuráveis.

Exemplo de políticas de captura personalizadas

O exemplo a seguir define QUERY_CAPTURE_MODE como AUTO e define um modo de captura personalizado. Cada uma das opções a seguir define as políticas de captura personalizadas como seu valor padrão no SQL Server 2022 (16.x). Considere ajustar esses valores para reduzir o número de consultas capturadas e, portanto, reduzir o espaço ocupado no disco do Repositório de Consultas. Recomenda-se alterar gradualmente estes valores através de 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 para EXECUTION_COUNT e 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 de 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 de tamanho máximo do Repositório de Consultas é apropriado em um banco de dados ocupado com muitos planos de consulta exclusivos. Ajustar a política de captura (consulte a seção anterior) é uma consideração mais importante para limitar o espaço em disco ocupado pelo Repositório de Consultas e impedir que o Repositório de Consultas entre em modo SOMENTE_LEITURA. Enquanto o Repositório de Consultas coleta consultas, planos de execução e estatísticas, seu tamanho no banco de dados cresce até que esse limite seja atingido. Quando isso acontece, o Repositório de Consultas altera automaticamente o modo de operação para READ_ONLY e para de coletar novos dados, o que significa que sua análise de desempenho não é mais precisa.

  • No SQL Server e na Instância Gerenciada SQL do Azure, o limite MAX_STORAGE_SIZE_MB não é rigorosamente imposto.
  • No Banco de Dados SQL do Azure, o valor máximo de MAX_STORAGE_SIZE_MB permitido é 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 de diálogo Repositório de Consultas do Management Studio Intervalo de Liberação de Dados.

  • O valor padrão do intervalo é 900 segundos (ou 15 minutos).
  • Se o Repositório de Consultas tiver violado o limite de MAX_STORAGE_SIZE_MB entre as verificações de tamanho de armazenamento, ele passará para o modo somente leitura.
  • Se SIZE_BASED_CLEANUP_MODE está ativado, o mecanismo de limpeza para impor o limite de MAX_STORAGE_SIZE_MB também será ativado.
    • Assim que o espaço suficiente tiver sido limpo, o modo de Armazenamento de Consultas voltará automaticamente para o modo READ_WRITE.

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

Intervalo de descarregamento de dados (minutos)

O Intervalo de Liberação de Dados define a frequência antes que as estatísticas de tempo de execução coletadas sejam mantidas no disco. No SQL Server Management Studio, o valor é em minutos, mas em Transact-SQL é expresso em segundos. 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 Query Store, mas fazer com que a carga de trabalho de E/S de armazenamento seja mais espinhosa, com menos impactos, mas mais pesados, na utilização do disco. Considere o uso de um valor mais alto se sua carga de trabalho não gerar um grande número de consultas e planos diferentes, ou se você puder suportar mais tempo para persistir os dados antes de um desligamento do banco de dados.
  • Diminuir o intervalo de transferência de dados diminui a quantidade de dados do Query Store que seria perdida em caso de um desligamento, perda de energia ou falha de sistema. Isso também pode suavizar o impacto de E/S de armazenamento do Repositório de Consultas ao gravar no disco com mais frequência, mas com menos dados.

Observação

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

Modificar padrões do Repositório de Consultas

Configure o Repositório de Consultas com base em sua carga de trabalho e requisitos de solução de problemas de desempenho. Os parâmetros padrão são bons o suficiente para iniciar, mas você deve monitorar como o Repositório de Consultas se comporta ao longo do tempo e ajustar sua configuração de acordo.

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 no T-SQL.

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 do mouse no banco de dados.
  2. Selecione Propriedades.
  3. Selecione Repositório de Consultas.

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 o SQL Server Management Studio ou o Transact-SQL para definir um valor diferente para Intervalo de Liberação de Dados:

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

Statistics Collection Interval: Define o nível de granularidade para a estatística de tempo de execução coletada, expressa em minutos. O padrão é 60 minutos. Considere o uso de um valor mais baixo se precisar de uma granularidade mais fina ou menos tempo para detetar e mitigar problemas. Lembre-se de que o valor afeta diretamente o tamanho dos dados do Repositório de Consultas. Use o SQL Server Management Studio ou o Transact-SQL para definir um valor diferente para Intervalo de Coleta de Estatísticas:

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

Limite para Consultas Obsoletas (Dias): Política de limpeza baseada no tempo que controla o período de retenção das estatísticas de tempo de execução guardadas e das consultas inativas, expressos em dias. Por padrão, o Repositório de Consultas é configurado para manter os dados por 30 dias, o que pode ser desnecessariamente longo para o seu cenário.

Evite manter dados históricos que você não planeja usar. Essa prática reduz a frequência das alterações para o estado de "somente leitura". O tamanho dos dados do Repositório de Consultas e o tempo para detetar e mitigar o problema serão mais previsíveis. Use o Management Studio ou o seguinte script para configurar a política de limpeza baseada em tempo:

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

Modo de Limpeza Baseado em Tamanho: Especifica se a limpeza automática de dados ocorre quando o tamanho dos dados do Repositório de Consultas se aproxima do limite. Ative a limpeza baseada em tamanho para garantir que o Repositório de Consultas sempre seja executado no modo de leitura-gravação e colete os dados mais recentes. Não há garantia de que, sob elevadas cargas de trabalho, a limpeza do Repositório de Consultas consiga manter consistentemente o tamanho dos dados abaixo do limite. É possível que a limpeza automática de dados fique para trás e mude (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 pouco frequentes e consultas com duração de compilação e execução insignificante são ignoradas. Os limites para contagem de execução, compilação e duração do tempo de execução são determinados internamente. A partir do SQL Server 2019 (15.x), essa é a opção padrão.
  • Nenhum: O Repositório de Consultas para de capturar novas consultas.
  • Custom: Permite maior controle e a capacidade de refinar a política de recolha de dados. As novas configurações personalizadas definem o que acontece durante o limite de tempo da política de captura interna. Este é um limite de tempo durante o qual as condições configuráveis são avaliadas e, se alguma for verdadeira, a consulta é qualificada para ser capturada pelo Repositório de Consultas.

Importante

Cursores, consultas dentro de procedimentos armazenados e consultas compiladas nativamente são sempre capturadas quando o Modo de Captura do Repositório de Consultas é definido como Todos, Automáticoou 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 define QUERY_CAPTURE_MODE como AUTO e define 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 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 define a política de captura CUSTOM como os padrões do SQL Server 2019 (15.x), em vez do novo modo de captura AUTO padrão. Para obter mais informações sobre opções e padrões de política de captura personalizada, consulte <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 Query Store

Esta seção fornece algumas diretrizes sobre como gerenciar o próprio recurso do Repositório de Consultas.

Estado do Query Store

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 mudará automaticamente de estado de leitura-gravação para somente leitura e interromperá a coleta de novos dados.

O sys.database_query_store_options deverá ser consultado para determinar se o Repositório de Consultas está atualmente ativo e se está a recolher estatísticas de tempo de execuçã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. Se for diferente do status desejado, a coluna readonly_reason pode fornecer mais informações. Quando o tamanho do Repositório de Consultas exceder a cota, o recurso alternará para o modo de somente leitura e fornecerá um motivo. Para obter informações sobre os motivos, consulte sys.database_query_store_options.

Obter opções do 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 de usuários.

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 tempo de execução de consulta (o padrão é 60 minutos). O novo valor para o intervalo é exposto através da visualização sys.database_query_store_options.

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

Valores arbitrários não são permitidos para INTERVAL_LENGTH_MINUTES. Use um dos seguintes intervalos: 1, 5, 10, 15, 30, 60 ou 1440 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.

Utilização do espaço do Repositório de Consultas

Para verificar o tamanho e o limite atuais 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 instrução a seguir para estender o armazenamento.

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

Definir opções do Repositório de Consultas

Você pode configurar várias opções do Repositório de Consultas de uma só vez com um único comando 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, consulte ALTER DATABASE SET Options (Transact-SQL).

Limpe o espaço

As 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 ficando sem espaço, convém limpar dados mais antigos do Repositório de Consultas usando a instrução a seguir.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

Como alternativa, convém limpar apenas dados de consulta ad hoc, pois eles são menos relevantes para otimizações de consulta e análise de planos, mas ocupam tanto espaço.

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

Excluir consultas ad hoc

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

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 lógica diferente para limpar os dados que não deseja mais.

O exemplo anterior usa o sp_query_store_remove_query procedimento armazenado estendido para remover dados desnecessários. Você também pode:

  • Use sp_query_store_reset_exec_stats para limpar estatísticas de tempo de execução para um plano específico.
  • Use sp_query_store_remove_plan para remover um único plano.

Conteúdo relacionado

  • Last updated on