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.

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

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:

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:

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.

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.

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.

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.

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.

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