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

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstâ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.

Configurações padrão 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, consulte ALTER DATABASE SET opções (Transact-SQL).

Se você quiser manter suas configurações personalizadas, use ALTER DATABASE com as opções do 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 de 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 contagem de execuções, a compilação e o tempo de execução 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 é muito grande.
• O banco de dados tem um grande número de consultas ad hoc únicas.
• O banco de dados tem 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 espaço suficiente for liberado, o modo Repositório 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 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. No SQL Server Management Studio, o valor está em minutos, mas, no Transact-SQL, ele é expresso em segundos. O padrão é 15 minutos (900 segundos).

• Aumentar o intervalo de gravação dos 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 se torne mais irregular, com impactos menos frequentes, porém mais intensos, na utilização 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 atenuar o impacto de E/S no armazenamento do Repositório de Consultas ao gravar em disco com mais frequência, mas 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 consultas obsoletas (dias): política de limpeza baseada em tempo que controla o período de retenção de estatísticas persistentes de tempo de execução e de consultas inativas, expresso em dias. 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 mitigar o problema serão mais previsíveis. Use o Management Studio ou o script a seguir 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 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 garantir que o Repositório de Consultas permaneça sempre no modo de leitura e gravação e colete os dados mais recentes. Com cargas de trabalho intensas, não há garantia de que a limpeza do Repositório de Consultas consiga manter, de forma consistente, o tamanho dos dados dentro do limite. É possível que a limpeza automática de dados atrase e entre temporariamente em 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 contagem de execuções, a compilação e o tempo de execução 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 o próprio recurso 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 somente leitura e informará o motivo. Veja informações sobre os motivos em 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 do usuário.

SELECT * FROM sys.database_query_store_options;

Definir o intervalo do Repositório de Consultas

Você pode sobrescrever o intervalo de agregação das estatísticas de tempo de execução da consulta (o padrão é de 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>);

Configurar opções do Repositório de Consultas

Você pode definir várias opções de Repositório de Consultas ao mesmo tempo com uma única ALTER DATABASE instrução.

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 Opções (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 ficando sem espaço, talvez seja interessante limpar os dados antigos do Repositório de Consultas usando a instrução a seguir.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

Como alternativa, talvez você queira limpar apenas os dados de consultas ad hoc, já que eles são menos relevantes para otimizações de consultas e análise de planos, mas ocupam o mesmo espaço.

No Azure Synapse Analytics, a limpeza do 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

• ALTER DATABASE SET opções (Transact-SQL)
• Modos de exibição do 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
• Otimize o desempenho com o Repositório de Consultas
• Armazenamento e análise de consultas históricas no Azure Synapse Analytics