Melhores práticas para monitorar cargas de trabalho com 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 as melhores práticas para usar o Repositório de Consultas do SQL Server com sua carga de trabalho.

• 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.
• Para obter informações sobre como descobrir informações acionáveis e ajustar o desempenho com o Repositório de Consultas, confira Ajustar o desempenho com o Repositório de Consultas.
• Para obter informações sobre operar o Repositório de consultas no Banco de dados SQL do Azure, consulte Operando o Repositório de Consultas no Banco de dados SQL do Azure.
• No Azure Synapse Analytics, o Repositório de Consultas não é habilitado por padrão para pools SQL dedicados, mas pode ser habilitado. Não há suporte para outras opções de configuração do Repositório de Consultas. Para obter mais informações, confira Armazenamento e análise de consultas históricas no Azure Synapse Analytics.

Use o SQL Server Management Studio mais recente

O SQL Server Management Studio tem um conjunto de interfaces do usuário criadas para configurar o Repositório de Consultas e para consumir os dados coletados sobre sua carga de trabalho. Baixe a versão mais recente do SQL Server Management Studio.

Para ver uma descrição rápida sobre como usar o Repositório de Consultas em cenários de solução de problemas, consulte Query Store Azure blogs.

Use a Análise de Desempenho de Consultas no Banco de Dados SQL do Azure

Se você executar o Repositório de Consultas no banco de dados SQL do Azure, poderá usar a Análise de Desempenho de Consultas para analisar o consumo do recurso ao longo do tempo. Embora seja possível usar o Management Studio e o Azure Data Studio para obter o consumo de recursos detalhado de todas as suas consultas, como CPU, memória e E/S, a Análise de Desempenho de Consultas tem uma forma rápida e eficiente de determinar seu impacto sobre o consumo de DTU geral do banco de dados. Para obter mais informações, consulte Análise de Desempenho de Consultas do Banco de Dados SQL do Azure.

Usar o Repositório de Consultas com os bancos de dados de Pool Elástico

Você pode usar o Repositório de Consultas em todos os bancos de dados sem problemas, mesmo em pools elásticos do banco de dados SQL do Azure densamente compactados. Todos os problemas anteriores relacionados ao uso excessivo de recursos, que podem ter ocorrido quando o Repositório de Consultas foi habilitado para o número grande de bancos de dados nos pools elásticos, foram resolvidos.

Começar a solução de problemas de desempenho de consulta

O fluxo de trabalho para solucionar problemas com o Repositório de Consultas é simples, como mostra o diagrama a seguir:

Habilite o Repositório de Consultas usando Management Studio conforme descrito na seção anterior ou execute a seguinte instrução Transact-SQL:

ALTER DATABASE [DatabaseOne] SET QUERY_STORE = ON;

Leva algum tempo até o Repositório de Consultas coletar o conjunto de dados que representa com precisão a sua carga de trabalho. Geralmente, um dia é suficiente até mesmo para cargas de trabalho muito complexas. No entanto, você pode começar a explorar os dados e identificar as consultas que exigem atenção imediatamente após a habilitação do recurso. Acesse a subpasta do Repositório de Consultas sob o nó do banco de dados no Pesquisador de Objetos do Management Studio para abrir modos de exibição de solução de problemas para cenários específicos.

As exibições do Repositório de Consultas do Management Studio operam com o conjunto de métricas de execução, cada uma expressa em qualquer uma destas funções de estatística:

O gráfico a seguir mostra como localizar os modos de exibição do Repositório de Consultas:

A tabela a seguir explica quando usar cada um dos modos de exibição do Repositório de Consultas:

Ao identificar uma consulta com desempenho abaixo do ideal, sua ação dependerá da natureza do problema.

• Se a consulta foi executada com vários planos e o último plano é consideravelmente pior que o anterior, é possível usar o mecanismo de imposição de plano para forçá-lo. O SQL Server tenta forçar o plano no otimizador. Se a imposição do plano falhar, um XEvent será acionado e o otimizador será instruído a otimizar normalmente.

• Você pode concluir falta um índice na consulta para a execução ideal. Essas informações são expostas no plano de execução de consulta. Crie o índice ausente e verifique o desempenho da consulta usando o Repositório de Consultas.

Se você executar sua carga de trabalho no banco de dados SQL, inscreva-se no Index Advisor do banco de dados SQL para receber automaticamente as recomendações de índice.

• Em alguns casos, você poderá aplicar a recompilação de estatística se perceber uma diferença significativa entre o número de linhas estimado e o real no plano de execução.
• Reescreva consultas problemáticas, por exemplo, para aproveitar a parametrização de consulta ou para implementar uma lógica melhor.

Verifique se o Repositório de Consultas coleta dados de consulta continuamente

O Repositório de Consultas pode alterar o modo de operação silenciosamente. Monitore regularmente o estado do Repositório de Consultas para garantir que o repositório de consultas esteja operando e agir para evitar falhas devido a causas previsíveis. Execute a consulta a seguir para determinar o modo de operação e exibir os parâmetros mais relevantes:

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

A diferença entre actual_state_desc e desired_state_desc indica que uma alteração do modo de operação ocorreu automaticamente. A alteração mais comum é a alternância silenciosa do Repositório de Consultas para o modo somente leitura. Em circunstâncias raras, o Repositório de Consultas pode terminar no estado de ERRO devido a erros internos.

Quando o estado real for somente leitura, use a coluna readonly_reason para determinar a causa raiz. Normalmente, você descobre que o Repositório de Consultas realizou a transição para o modo somente leitura porque a cota de tamanho foi excedida. Nesse caso, readonly_reason é definido como 65536. Para outros motivos, consulte sys.database_query_store_options (Transact-SQL).

Considere as etapas a seguir para realizar a transição do Repositório de Consultas para o modo de leitura-gravação e ativar a coleta de dados:

• Aumente o tamanho do armazenamento máximo usando a opção MAX_STORAGE_SIZE_MB de ALTER DATABASE.

• Limpe os dados do Repositório de Consultas usando a instrução a seguir:

  ALTER DATABASE [QueryStoreDB] SET QUERY_STORE CLEAR;
  

Você pode aplicar uma destas etapas ou ambas executando a seguinte instrução, que altera explicitamente o modo de operação para leitura-gravação:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

Execute as etapas a seguir para ser proativo:

• Você pode evitar alterações silenciosas do modo de operação aplicando as práticas recomendadas. O tamanho do Repositório de Consultas deve estar sempre abaixo do valor máximo permitido para reduzir drasticamente a chance de ocorrer transição para o modo somente leitura. Ative a política com base no tamanho conforme descrito na seção Configurar Repositório de Consultas para que o Repositório de Consultas limpe automaticamente os dados quando o tamanho se aproximar do limite.
• Para assegurar que os dados mais recentes sejam mantidos, configure a política com base em tempo para remover informações obsoletas regularmente.
• Por fim, considere definir o Modo de Captura do Repositório de Consultas como Auto, pois ele remove as consultas que costumam ser menos relevantes para sua carga de trabalho.

Estado de ERRO

Para recuperar o Repositório de Consultas, tente definir explicitamente o modo de leitura-gravação e verifique o estado real novamente.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Se o problema persistir, isso indicará que os dados do Repositório de Consultas mantidos no disco estão corrompidos.

A partir do SQL Server 2017 (14.x), o Repositório de Consultas pode ser recuperado executando o procedimento armazenado sys.sp_query_store_consistency_check no banco de dados afetado. O Repositório de Consultas deve ser desabilitado antes da tentativa de executar a operação de recuperação. Aqui está uma consulta de exemplo a ser usada ou modificada para realizar a verificação de consistência e a recuperação do QDS:

IF EXISTS (SELECT * FROM sys.database_query_store_options WHERE actual_state=3) 
BEGIN
  BEGIN TRY
    ALTER DATABASE [QDS] SET QUERY_STORE = OFF
    Exec [QDS].dbo.sp_query_store_consistency_check
    ALTER DATABASE [QDS] SET QUERY_STORE = ON
    ALTER DATABASE [QDS] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
  END TRY
 
  BEGIN CATCH 
    SELECT  
      ERROR_NUMBER() AS ErrorNumber  
      ,ERROR_SEVERITY() AS ErrorSeverity  
      ,ERROR_STATE() AS ErrorState  
      ,ERROR_PROCEDURE() AS ErrorProcedure  
      ,ERROR_LINE() AS ErrorLine  
      ,ERROR_MESSAGE() AS ErrorMessage; 
  END CATCH;   
END

No SQL Server 2016 (13.x), limpe os dados do Repositório de Consultas conforme mostrado.

Se a recuperação falhar, tente limpar o Repositório de Consultas antes de configurar o modo de leitura/gravação.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE CLEAR;
GO

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Evite usar consultas não parametrizadas

Não é recomendável usar consultas não parametrizadas quando isso não é necessário. Um exemplo é o caso da análise ad hoc. Planos em cache não podem ser reutilizados, o que força o Otimizador de Consulta a compilar consultas para cada texto da consulta exclusivo. Para saber mais, confira Diretrizes para uso da parametrização forçada.

Além disso, o Repositório de Consultas pode rapidamente exceder a cota de tamanho devido a um número potencialmente grande de textos da consulta diferentes e, assim, um grande número de planos de execução diferentes com forma semelhante. Como resultado, o desempenho da carga de trabalho fica abaixo do ideal e o Repositório de Consultas pode alternar para o modo somente leitura ou excluir dados constantemente na tentativa de acompanhar as consultas que chegam.

Considere as seguintes opções:

• Parametrizar consultas quando aplicável. Por exemplo, encapsule as consultas dentro de um procedimento armazenado ou em sp_executesql. Para saber mais, confira Reutilização de parâmetros e do plano de execução.
• Use a opção otimizar para cargas de trabalho ad hoc se a carga de trabalho contiver muitos lotes ad hoc de uso único com planos de consulta diferentes.
  • Compare o número de valores query_hash distintos ao número total de entradas em sys.query_store_query. Se o índice estiver próximo de 1, a carga de trabalho ad hoc gerará consultas diferentes.
• Aplique parametrização forçada ao banco de dados ou a um subconjunto de consultas se o número de planos de consulta diferentes não for grande.
  • Use um guia de plano para forçar a parametrização somente para a consulta selecionada.
  • Configure a parametrização forçada usando o comando parameterization database option se houver um pequeno número de planos de consulta diferentes em sua carga de trabalho. Um exemplo é quando a taxa entre a contagem de query_hash distintos e o número total de entradas em sys.query_store_query é muito inferior a 1.
• Defina QUERY_CAPTURE_MODE como AUTO para filtrar automaticamente, desconsiderando consultas ad hoc com baixo consumo de recursos.

Localizar consultas não parametrizadas no Repositório de Consultas

Você pode encontrar o número de planos armazenados no Repositório de Consultas usando a consulta abaixo, usando DMVs do repositório de consultas, no SQL Server, Instância Gerenciada de SQL do Azure ou Banco de Dados SQL do Azure:

SELECT count(Pl.plan_id) AS plan_count, Qry.query_hash, Txt.query_text_id, Txt.query_sql_text
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
    ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id
GROUP BY Qry.query_hash, Txt.query_text_id, Txt.query_sql_text
ORDER BY plan_count desc;

O exemplo a seguir cria uma sessão de Eventos Estendidos para capturar o evento query_store_db_diagnostics, o que pode ser útil para diagnosticar o consumo de recursos de consulta. No SQL Server, essa sessão de evento estendida cria um arquivo de evento na pasta de log do SQL Server por padrão. Por exemplo, em uma instalação padrão do SQL Server 2019 (15.x) em Windows, o arquivo de evento (arquivo.xel) deve ser criado na pasta C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log. Para Instância Gerenciada de SQL do Azure, especifique um local de Armazenamento de Blobs do Azure. Para obter mais informações, confira XEvent event_file para Instância Gerenciada de SQL do Azure. O event 'qds.query_store_db_diagnostics' não está disponível para o Banco de Dados SQL do Azure.

CREATE EVENT SESSION [QueryStore_Troubleshoot] ON SERVER 
ADD EVENT qds.query_store_db_diagnostics(
      ACTION(sqlos.system_thread_id,sqlos.task_address,sqlos.task_time,sqlserver.database_id,sqlserver.database_name))
ADD TARGET package0.event_file(SET filename=N'QueryStore',max_file_size=(100))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);

Com esses dados, você pode encontrar a contagem de planos no Repositório de Consultas e também muitas outras estatísticas. Procure as colunas plan_count, query_count, max_stmt_hash_map_size_kb e max_size_mb nos dados do evento para entender a quantidade de memória usada e o número de planos que são rastreados por Repositório de Consultas. A contagem de planos maior que o normal pode indicar um aumento nas consultas não parametrizadas. Use a consulta de exibição de gerenciamento dinâmico do Repositório de Consultas abaixo para examinar as consultas parametrizadas e consultas não parametrizadas no Repositório de Consultas.

Para consultas parametrizadas:

SELECT qsq.query_id, qsqt.query_sql_text
FROM sys.query_store_query AS qsq 
INNER JOIN sys.query_store_query_text AS qsqt
ON qsq.query_text_id= qsqt.query_text_id 
WHERE qsq.query_parameterization_type<>0 or qsqt.query_sql_text like '%@%';

Para consultas não parametrizadas:

SELECT qsq.query_id, qsqt.query_sql_text
FROM sys.query_store_query AS qsq 
INNER JOIN sys.query_store_query_text AS qsqt
ON qsq.query_text_id= qsqt.query_text_id 
WHERE query_parameterization_type=0;

Evite um padrão DROP e CREATE para objetos recipientes

O Repositório de Consultas associa a entrada da consulta a um objeto recipiente, como um procedimento armazenado, função e gatilho. Ao recriar um objeto recipiente, uma nova entrada da consulta é gerada para o mesmo texto da consulta. Isso o impede de acompanhar estatísticas de desempenho para essa consulta ao longo do tempo e de usar o mecanismo de imposição de plano. Para evitar essa situação, use o processo ALTER <object> para alterar uma definição de objeto recipiente sempre que possível.

Verifique o status de planos forçados regularmente

A imposição de plano é um mecanismo prático para corrigir o desempenho das consultas críticas e torná-las mais previsíveis. Assim como ocorre com as dicas de plano e guias de plano, impor um plano não é uma garantia de que ele será usado em execuções futuras. Normalmente, quando o esquema de banco de dados é alterado de forma que os objetos referenciados pelo plano de execução são alterados ou removidos, a imposição de plano começa a falhar. Nesse caso, o SQL Server fará fallback para a recompilação da consultas, enquanto o motivo real da falha da imposição é exposto em sys.query_store_plan. A consulta a seguir retorna informações sobre planos impostos:

USE [QueryStoreDB];
GO

SELECT p.plan_id, p.query_id, q.object_id as containing_object_id,
    force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q on p.query_id = q.query_id
WHERE is_forced_plan = 1;

Para obter uma lista completa dos motivos, confira sys.query_store_plan. Você também pode usar o XEvent query_store_plan_forcing_failed para acompanhar e solucionar problemas de falhas de imposição de plano.

Evite renomear bancos de dados para consultas com planos forçados

Planos de execução fazem referência a objetos usando nomes de três partes, como database.schema.object.

Se você renomear um banco de dados, a imposição do plano falhará, causando recompilação em todas as execuções de consulta subsequentes.

Usar o Repositório de Consultas em servidores críticos

Os sinalizadores de rastreamento globais 7745 e 7752 podem ser usados para melhorar a disponibilidade dos bancos de dados usando o Repositório de Consultas. Para obter mais informações, confira Sinalizadores de rastreamento.

• O sinalizador de rastreamento 7745 impede o comportamento padrão em que o Repositório de Consultas grava dados em disco antes que o SQL Server possa ser desligado. Isso significa que os dados de Repositório de Consultas que foram coletados, mas ainda não foram mantidos no disco, serão perdidos até a janela de tempo definida com DATA_FLUSH_INTERVAL_SECONDS.
• O sinalizador de rastreamento 7752 permite o carregamento assíncrono do Repositório de Consultas. Isso permite colocar um banco de dados online e executar as consultas antes da recuperação total do Repositório de Consultas. O comportamento padrão é fazer um carregamento assíncrono do Repositório de Consultas. O comportamento padrão impede a execução de consultas antes da recuperação do Repositório de Consultas, mas também impede a perda de consultas na coleta de dados.

Usar o Repositório de Consultas na replicação geográfica ativa do banco de dados SQL do Azure

O Repositório de Consultas em uma réplica geográfica ativa secundária do Banco de Dados SQL do Azure será uma cópia somente leitura da atividade na réplica primária.

Evite camadas incompatíveis com replicação geográfica do banco de dados SQL do Azure. Um banco de dados secundário deve estar no mesmo tamanho da computação do banco de dados primário ou próximo dele e na mesma camada de serviço do banco de dados primário. Procure o tipo de espera HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO em sys.dm_db_wait_stats, o que indica a limitação da taxa do log de transações na réplica primária devido ao retardo secundário.

Para saber mais sobre como estimar e configurar o tamanho do Banco de Dados SQL do Azure secundário da replicação geográfica ativa, confira Como configurar o banco de dados secundário.

Mantenha o Repositório de Consultas ajustado à sua carga de trabalho

As melhores práticas e as recomendações para configurar e gerenciar o Repositório de Consultas foram expandidas neste artigo: Melhores práticas para gerenciar o Repositório de Consultas.

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
• Ajuste do desempenho com o Repositório de Consultas
• Armazenamento e análise de consultas históricas no Azure Synapse Analytics