Monitore o desempenho usando o Repositório de Consultas

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada do AzureAzure Synapse Analytics (somente pool SQL dedicado)Banco de dados SQL no Microsoft Fabric

O recurso Repositório de Consultas fornece informações sobre a escolha do plano de consulta e o desempenho do SQL Server, do Banco de Dados SQL do Azure, do banco de dados SQL do Fabric, da Instância Gerenciada de SQL do Azure e do Azure Synapse Analytics. O Repositório de Consultas simplifica a solução de problemas de desempenho, ajudando você a encontrar rapidamente diferenças de desempenho causadas por alterações no plano de consulta. O Repositório de Consultas captura automaticamente um histórico das consultas, dos planos e das estatísticas de runtime e os mantém para sua análise. Ele separa os dados por janelas de tempo para que você possa ver os padrões de uso do banco de dados e entender quando ocorreram alterações nos planos de consulta no servidor.

Você pode configurar o Repositório de Consultas usando a opção ALTER DATABASE SET options.

Important

Se você estiver usando o Repositório de Consultas para insights de carga de trabalho just-in-time no SQL Server 2016 (13.x), planeje instalar as correções de escalabilidade de desempenho no KB 4340759 o mais rápido possível.

Habilitar o Repositório de Consultas

  • O Repositório de Consultas está habilitado por padrão para novos bancos de dados do Banco de Dados SQL do Azure e da Instância Gerenciada de SQL do Azure.
  • O Repositório de Consultas não está habilitado por padrão para SQL Server 2016 (13.x), SQL Server 2017 (14.x), SQL Server 2019 (15.x). Ele é habilitado por padrão no READ_WRITE modo para novos bancos de dados a partir do SQL Server 2022 (16.x). Para permitir que os recursos acompanhem melhor o histórico de desempenho, solucionem problemas relacionados ao plano de consulta e habilitem novas funcionalidades no SQL Server 2022 (16.x), recomendamos habilitar o Repositório de Consultas em todos os bancos de dados.
  • O Repositório de Consultas não está habilitado por padrão para novos bancos de dados do Azure Synapse Analytics.

Use a página do Repositório de Consultas no SQL Server Management Studio

  1. No Pesquisador de Objetos, clique com o botão direito do mouse em um banco de dados e selecione Propriedades.

    Note

    Requer, no mínimo, a versão 16 do Management Studio.

  2. Na caixa de diálogo Propriedades do Banco de Dados , selecione a página Repositório de Consultas .

  3. Na caixa Modo de Operação (Solicitado), selecione Leitura Gravação.

Usar instruções Transact-SQL

Use a ALTER DATABASE instrução para habilitar o Repositório de Consultas para um determinado banco de dados. Por exemplo:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

As opções para configurar o Repositório de Consultas no banco de dados SQL do Fabric com ALTER DATABASE estão atualmente limitadas.

No Azure Synapse Analytics, habilite o Repositório de Consultas sem opções adicionais, por exemplo:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON;

Para obter mais opções de sintaxe relacionadas ao Repositório de Consultas, consulte as ALTER DATABASE SET opções.

Note

O Repositório de Consultas não pode ser habilitado para os bancos de dados master ou tempdb.

Important

Para obter informações sobre como habilitar o Repositório de Consultas e mantê-lo ajustado a sua carga de trabalho, consulte Melhor Prática do Repositório de Consultas.

Informações no Repositório de Consultas

Planos de execução para qualquer consulta específica no SQL Server normalmente evoluem com o tempo por vários motivos diferentes, como alterações de estatísticas, alterações de esquema, criação/exclusão de índices etc. O cache de procedimento (no qual os planos de consulta em cache são armazenados) armazena apenas o plano de execução mais recente. Os planos também são removidos do cache de planos devido à pressão de memória. Como resultado, as regressões do desempenho de consulta causadas por alterações no plano de execução podem não ser triviais e podem ter resolução lenta.

Como o Repositório de Consultas mantém vários planos de execução por consulta, ele pode impor políticas para instruir o Processador de Consultas a usar um plano de execução específico para uma consulta. Isso é conhecido como imposição de plano. A imposição de plano no Repositório de Consultas é fornecida usando um mecanismo semelhante à dica de consulta Query hints, mas não requer nenhuma alteração em aplicativos de usuário. A imposição de um plano pode resolver uma regressão no desempenho de uma consulta causada por uma mudança de plano em um curto espaço de tempo.

Note

O Repositório de Consultas coleta planos para instruções DML, tais como SELECT, INSERT, UPDATE, DELETE, MERGE e BULK INSERT.

Por design, o Repositório de Consultas não coleta planos para instruções DDL, como CREATE INDEX, etc. O Repositório de Consultas captura o consumo cumulativo de recursos coletando planos para as instruções DML subjacentes. Por exemplo, o Repositório de Consultas pode exibir as instruções SELECT e INSERT executadas internamente para preencher um novo índice.

O Repositório de Consultas não coleta dados para procedimentos armazenados compilados nativamente por padrão. Use sys.sp_xtp_control_query_exec_stats para habilitar a coleta de dados para procedimentos armazenados compilados nativamente.

As estatísticas de espera são outra fonte de informações que ajudam a solucionar problemas de desempenho no mecanismo de banco de dados. Por muito tempo, as estatísticas de espera estavam disponíveis apenas no nível da instância, o que dificultava rastrear as esperas até uma consulta específica. A partir do SQL Server 2017 (14.x) e do banco de dados SQL do Azure, o Repositório de Consultas inclui uma dimensão que controla as estatísticas de espera. O exemplo a seguir possibilita que o Repositório de Consultas colete estatísticas de espera.

ALTER DATABASE <database_name>
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

Cenários comuns para o uso do recurso Repositório de Consultas são:

  • Encontre e corrija rapidamente uma regressão no desempenho do plano ao forçar o plano de consulta anterior. Corrigir consultas com regressão recente no desempenho devido a alterações no plano de execução.
  • Determinar o número de vezes que uma consulta foi executada em determinada janela de tempo, auxiliando um DBA na solução de problemas de recurso de desempenho.
  • Identificar as principais consultas n (por tempo de execução, consumo de memória, etc.) nas últimas x horas.
  • Audite o histórico dos planos de consulta de uma determinada consulta.
  • Analisar os padrões de uso dos recursos (CPU, E/S e memória) para determinado banco de dados.
  • Identifique as n principais consultas que estão aguardando recursos.
  • Entenda a natureza da espera de uma determinada consulta ou plano.

O Repositório de Consultas contém três repositórios:

  • um repositório de plano para persistir as informações do plano de execução.
  • um repositório de estatísticas de runtime para manter as informações de estatísticas de execução.
  • um repositório de estatísticas de espera para manter as informações de estatísticas de espera.

O número de planos exclusivos que pode ser armazenado para uma consulta no repositório de planos é limitado pela opção de configuração max_plans_per_query . Para melhorar o desempenho, as informações são gravadas nos repositórios de forma assíncrona. Para minimizar o uso de espaço, as estatísticas de execução no repositório de estatísticas de execução são agregadas ao longo de uma janela de tempo fixa. As informações nesses repositórios são visíveis pela consulta das exibições de catálogo do Repositório de Consultas.

A consulta a seguir retorna informações sobre consultas, seus planos, tempo de compilação e estatísticas de tempo de execução do Repositório de Consultas.

SELECT Txt.query_text_id, Txt.query_sql_text, Pln.plan_id, Qry.*, RtSt.*
FROM sys.query_store_plan AS Pln
INNER JOIN sys.query_store_query AS Qry
    ON Pln.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id
INNER JOIN sys.query_store_runtime_stats RtSt
ON Pln.plan_id = RtSt.plan_id;

Repositório de Consultas para réplicas secundárias

Aplica-se a: SQL Server 2025 (17.x), Banco de Dados SQL do Azure

O recurso Repositório de Consultas para réplicas secundárias permite a mesma funcionalidade do Repositório de Consultas nas cargas de trabalho de réplicas secundárias que está disponível para réplicas primárias. Quando o Repositório de Consultas para réplicas secundárias está habilitado, as réplicas enviam as informações de execução de consultas que normalmente seriam armazenadas no Repositório de Consultas de volta para a réplica primária. Em seguida, a réplica primária persiste os dados em disco dentro do próprio Repositório de Consultas. Em essência, há um Repositório de Consultas compartilhado entre as réplicas primárias e todas as secundárias. O Repositório de Consultas existe na réplica primária e armazena dados para todas as réplicas juntas.

Para obter mais informações, consulte Repositório de Consultas para réplicas secundárias.

Usar o recurso de consultas regredidas

Depois de habilitar o Repositório de Consultas, atualize a parte do banco de dados do painel Pesquisador de Objetos para adicionar a seção Repositório de Consultas.

Captura de tela da árvore de relatórios do Repositório de Consultas no Pesquisador de Objetos do SSMS.

Note

No Azure Synapse Analytics, as exibições do Repositório de Consultas estão disponíveis em Exibições do Sistema na parte do banco de dados do painel do Pesquisador de Objetos.

Escolha Consultas regredidas para abrir o painel Consultas regredidas no SQL Server Management Studio. O painel Consultas Regredidas mostra as consultas e os planos no Repositório de Consultas. Use as caixas de listagem suspensa na parte superior para filtrar consultas com base em diversos critérios: Duração (ms) (Padrão), Tempo de CPU (ms), Leituras Lógicas (KB), Gravações Lógicas (KB), Leituras Físicas (KB), Tempo de CLR (ms), DOP, Consumo de Memória (KB), Contagem de Linhas, Memória de Log Usada (KB), Memória Usada pelo tempdb (KB) e Tempo de Espera (ms).

Selecione um plano para ver o plano de consulta gráfico. Há botões disponíveis para exibir a consulta de origem, para forçar e não forçar um plano de consulta, para alternar entre os formatos de grade e gráfico, para comparar os planos selecionados (se houver mais de um selecionado) e para atualizar a exibição.

Captura de tela do relatório Consultas Retornadas do SQL Server no Pesquisador de Objetos do SSMS.

Para forçar um plano, selecione uma consulta e um plano e, em seguida, selecione Forçar Plano. Você pode impor apenas planos que foram salvos pelo recurso de plano de consulta e ainda são mantidos no cache do plano de consulta.

Localizar consultas em espera

A partir do SQL Server 2017 (14.x) e do banco de dados SQL do Azure, as estatísticas de espera por consulta estão disponíveis no Repositório de Consultas.

No Repositório de Consultas, os tipos de espera são combinados em categorias de espera. O mapeamento das categorias de espera para tipos de espera está disponível em sys.query_store_wait_stats (Transact-SQL).

Selecione Estatísticas de espera da consulta para abrir o painel Estatísticas de espera da consulta no SQL Server Management Studio 18.0 ou versões posteriores. O painel Estatísticas de Espera de Consulta mostra um gráfico de barras que contém as principais categorias de espera no Repositório de Consultas. Use a lista suspensa na parte superior para selecionar um critério de agregação para o tempo de espera: avg, max, min, std dev e total (padrão).

Captura de tela do relatório Estatísticas de espera de consulta do SQL Server no Pesquisador de Objetos do SSMS.

Selecione uma categoria de espera selecionando a barra, e uma exibição detalhada da categoria de espera selecionada será exibida. Esse novo gráfico de barras inclui as consultas que contribuíram para essa categoria de espera.

Captura de tela da exibição detalhada de Estatísticas de Espera de Consulta do SQL Server no Pesquisador de Objetos do SSMS.

Use a caixa de lista suspensa na parte superior para filtrar consultas com base em diferentes critérios de tempo de espera para a categoria de espera selecionada: média, máximo, mínimo, desvio padrão e total (padrão). Selecione um plano para ver o plano de consulta gráfico. Botões estão disponíveis para exibir a consulta de origem, impor e cancelar a imposição de um plano de consulta e atualizar a exibição.

As categorias de espera combinam diferentes tipos de espera em grupos de natureza semelhante. Diferentes categorias de espera exigem análises complementares distintas para resolver o problema, mas tipos de espera da mesma categoria levam a processos de solução de problemas muito semelhantes, e fornecer a consulta afetada, além das esperas, seria a peça que faltava para completar com êxito a maioria dessas investigações.

Aqui estão alguns exemplos de como você pode obter mais informações sobre sua carga de trabalho antes e depois de introduzir as categorias de espera no Repositório de Consultas:

Experiência anterior Nova experiência Action
Alto número de esperas RESOURCE_SEMAPHORE por banco de dados Esperas de memória alta no Repositório de Consultas para consultas específicas Localize as consultas que consomem mais memória no Repositório de Consultas. Essas consultas provavelmente estão atrasando o progresso adicional das consultas afetadas. Considere usar a dica de consulta MAX_GRANT_PERCENT para essas consultas ou para as consultas afetadas.
Altas esperas de LCK_M_X por banco de dados Esperas de bloqueio altas no Repositório de Consultas para consultas específicas Verifique os textos de consulta para as consultas afetadas e identifique as entidades de destino. Pesquise outras consultas no Repositório de Consultas que modificam a mesma entidade, que são executadas com frequência e/ou têm alta duração. Depois de identificar essas consultas, considere alterar a lógica do aplicativo para melhorar a simultaneidade ou use um nível de isolamento menos restritivo.
Alto número de esperas de PAGEIOLATCH_SH por banco de dados Esperas de buffer IO alto no Repositório de Consultas para consultas específicas Encontre as consultas com um alto número de leituras físicas no Repositório de Consultas. Se corresponderem às consultas com altas IO esperas, considere a introdução de um índice na entidade subjacente, a fim de fazer buscas em vez de varreduras e, assim, minimizar a IO sobrecarga das consultas.
Altas esperas de SOS_SCHEDULER_YIELD por banco de dados Esperas de CPU altas no Repositório de Consultas para consultas específicas Localize as consultas com maior consumo de CPU no Repositório de Consultas. Entre elas, identifique as consultas para as quais a tendência de alto uso de CPU se correlaciona com altas esperas de CPU nas consultas afetadas. Concentre-se em otimizar essas consultas – poderia haver uma regressão de plano ou talvez um índice ausente.

Opções de configuração

Para obter as opções disponíveis para configurar parâmetros de Repositório de Consultas, consulte ALTER DATABASE SET opções (Transact-SQL).

Consulte a exibição sys.database_query_store_options para determinar as opções atuais do Repositório de Consultas. Para obter mais informações sobre os valores, consulte sys.database_query_store_options.

Para obter exemplos de como definir as opções de configuração usando as instruções Transact-SQL, confira o Gerenciamento de opções.

Note

Para o Azure Synapse Analytics, o Repositório de Consultas pode ser habilitado como em outras plataformas, mas não há suporte para opções de configuração adicionais.

Exiba e gerencie o Repositório de Consultas por meio do Management Studio ou usando as exibições e os procedimentos a seguir.

Funções do Repositório de Consultas

As funções auxiliam nas operações com o Repositório de Consultas.

sys.fn_stmt_sql_handle_from_sql_stmt

Exibições de catálogo do Repositório de Consultas

As exibições de catálogo apresentam informações sobre o Repositório de Consultas.

sys.database_query_store_options

sys.query_context_settings

sys.query_store_plan

sys.query_store_query

sys.query_store_query_text

sys.query_store_runtime_stats

sys.query_store_wait_stats

sys.query_store_runtime_stats_interval

sys.database_query_store_internal_state

Procedimentos armazenados do Repositório de Consultas

Os procedimentos armazenados configuram o Repositório de Consultas.

sp_query_store_flush_db

sp_query_store_reset_exec_stats

sp_query_store_force_plan

sp_query_store_unforce_plan

sp_query_store_remove_plan

sp_query_store_remove_query

sp_query_store_clear_message_queues

sp_query_store_consistency_check (Transact-SQL)1

1 Em cenários extremos, o Repositório de Consultas pode inserir um estado de ERRO devido a erros internos. Começando com o SQL Server 2017 (14.x), se isso acontecer, o Repositório de Consultas pode ser recuperado executando o procedimento armazenado sp_query_store_consistency_check no banco de dados afetado. Confira sys.database_query_store_options para obter mais detalhes descritos na descrição da coluna actual_state_desc.

Manutenção do Repositório de Consultas

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

Auditoria e solução de problemas de desempenho

Para obter mais informações sobre como se aprofundar no ajuste de desempenho com o Repositório de Consultas, confira Ajustar o desempenho com o Repositório de Consultas.

Outros tópicos de desempenho:

Conteúdo relacionado

  • Last updated on