Partilhar via

Monitorizar o desempenho usando o Repositório de Consultas (Query Store)

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada SQL 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 e o desempenho do plano de consulta para SQL Server, Banco de Dados SQL do Azure, Banco de Dados SQL da Malha, Instância Gerenciada do SQL do Azure e 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 de consultas, planos e estatísticas de tempo de execução e os retém para sua revisão. 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 as alterações do plano de consulta aconteceram no servidor.

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

Importante

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

Habilitar o repositório de consultas

  • O Repositório de Consultas é habilitado por padrão para novos bancos de dados do Banco de Dados SQL do Azure e da Instância Gerenciada do 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 modo READ_WRITE para novos bancos de dados a partir do SQL Server 2022 (16.x). Para habilitar recursos para controlar melhor o histórico de desempenho, solucionar problemas relacionados ao plano de consulta e habilitar novos recursos 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.

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

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

    Observação

    Requer pelo menos 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 e Gravação.

Usar instruções Transact-SQL

Use a instrução ALTER DATABASE 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 de malha com ALTER DATABASE são limitadas no momento.

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 ALTER DATABASE SET Options (Transact-SQL).

Observação

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

Importante

Para obter informações sobre como habilitar o Repositório de Consultas e mantê-lo ajustado à sua carga de trabalho, consulte Práticas recomendadas com o Repositório de Consultas.

Informações no Repositório de Consultas

Os planos de execução para qualquer consulta específica no SQL Server normalmente evoluem ao longo do tempo devido a uma série de razões diferentes, como alterações de estatísticas, alterações de esquema, criação/exclusão de índices, etc. O cache de procedimentos (onde 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 da memória. Como resultado, as regressões de desempenho de consulta causadas por alterações no plano de execução podem ser não triviais e demoradas para serem resolvidas.

Como o Repositório de Consultas retém vários planos de execução por consulta, ele pode impor políticas para direcionar o Processador de Consultas a usar um plano de execução específico para uma consulta. Isso é denominado imposição de plano. O Forçamento de plano no Repositório de Consultas é realizado usando um mecanismo semelhante à sugestão de consulta USE PLAN, mas não requer nenhuma alteração nas aplicações do utilizador. A imposição de planos pode resolver uma regressão de desempenho de consulta causada por uma alteração de plano em um período de tempo muito curto.

Observação

O Repositório de Consultas recolhe planos para instruções DML, como SELECT, INSERT, UPDATE, DELETE, MERGEe BULK INSERT.

Por design, o Query Store não coleta planos para instruções DDL, como CREATE INDEX, etc. O Query Store 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 recolha de dados para procedimentos armazenados compilados nativamente.

Estatísticas de espera são outra fonte de informações que ajuda a solucionar problemas de desempenho no Mecanismo de Banco de Dados. Durante muito tempo, as estatísticas de espera estavam disponíveis apenas no nível da instância, o que dificultava o retrocesso das esperas para 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 rastreia estatísticas de espera. O exemplo a seguir permite que o Repositório de Consultas colete estatísticas de espera.

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

Os cenários comuns para usar o recurso Repositório de Consultas são:

  • Encontre e corrija rapidamente uma regressão de desempenho forçando o plano de consulta anterior. Corrija consultas cujo desempenho tenha regredido recentemente devido a mudanças no plano de execução.
  • Determine o número de vezes que uma consulta foi executada em uma determinada janela de tempo, auxiliando um DBA na solução de problemas de recursos de desempenho.
  • Identifique as principais n consultas (por tempo de execução, consumo de memória, etc.) nas últimas x horas.
  • Audite o histórico de planos de consulta para uma determinada consulta.
  • Analise os padrões de uso de recursos (CPU, E/S e memória) para um banco de dados específico.
  • Identifique as n principais consultas que estão aguardando recursos.
  • Compreender a natureza da espera para uma consulta ou plano específico.

O Repositório de Consultas contém três lojas.

  • Um repositório de planos para armazenar as informações do plano de execução.
  • Armazenamento de dados de estatísticas de execução em tempo real para armazenar permanentemente as informações de estatísticas de execução.
  • Um estatísticas de espera armazenam para informações de estatísticas de espera persistentes.

O número de planos exclusivos que podem ser armazenados 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 são agregadas em uma janela de tempo fixa. As informações nesses repositórios são visíveis ao consultar as vistas do catálogo do "Query Store".

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 (a partir do SQL Server 2022 (16.x))

O recurso Repositório de Consultas para réplicas secundárias habilita a mesma funcionalidade de Repositório de Consultas em 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 consulta 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 de seu próprio Repositório de Consultas. Essencialmente, há um Repositório de Consultas partilhado entre a réplica primária e todas as réplicas secundárias. O Repositório de Consultas existe na réplica primária e armazena dados para todas as réplicas juntas.

Para obter informações completas sobre o Query Store para réplicas secundárias, consulte Query Store para réplicas secundárias do grupo de disponibilidade Always On.

Usar o recurso Consultas Regressadas

Depois de habilitar o Repositório de Consultas, atualize a secção de banco de dados no Explorador de Objetos para adicionar a seção Repositório de Consultas.

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

Observação

Para o Azure Synapse Analytics, as vistas do Query Store estão disponíveis em Exibições do Sistema na seção do banco de dados da janela do Explorador de Objetos.

Selecione Consultas Regressadas para abrir o painel Consultas Regressadas no SQL Server Management Studio. O painel Consultas Regressadas mostra as consultas e os planos no Repositório de Consultas. Use as caixas de listagem suspensas na parte superior para filtrar consultas com base em vários critérios: Duração (ms) (Padrão), Tempo da CPU (ms), Leituras lógicas (KB), Gravações lógicas (KB), Leituras físicas (KB), Tempo CLR (ms), DOP, Consumo de memória (KB), Contagem de linhas, Memória de log usada (KB), Memória DB temp usada (KB) e Tempo de espera (ms).

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

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

Se pretende forçar um plano, selecione uma consulta e um plano e, então, selecione Forçar Plano. Você só pode forçar planos que foram salvos pela funcionalidade de plano de consulta e ainda são mantidos no cache do plano de consulta.

Encontrar 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 ao longo do tempo 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 de categorias de espera para tipos de espera está disponível em sys.query_store_wait_stats (Transact-SQL).

Selecione Estatísticas de Espera de Consulta para abrir o painel Estatísticas de Espera de Consulta no SQL Server Management Studio 18.0 ou versões superiores. O painel Estatísticas de Espera de Consulta mostra um gráfico de barras contendo as principais categorias de espera no Repositório de Consultas. Use a lista suspensa na parte superior para selecionar um critério agregado 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 na barra e uma exibição de detalhes na categoria de espera selecionada será exibida. Este novo gráfico de barras inclui as consultas que contribuíram para essa categoria de espera.

Captura de ecrã da vista detalhada das estatísticas de espera de consulta do SQL Server no Explorador de Objetos do SSMS.

Use a caixa de listagem suspensa na parte superior para filtrar consultas com base em vários critérios de tempo de espera para a categoria de espera selecionada: avg, max, min, std dev e total (padrão). Selecione um plano para ver o plano de consulta em formato gráfico. Os botões estão disponíveis para exibir a consulta de origem, forçar e desforçar um plano de consulta e atualizar a exibição.

As categorias de espera combinam diferentes tipos de espera em grupos semelhantes por natureza. Diferentes categorias de espera exigem uma análise de acompanhamento diferente para resolver o problema, mas os tipos de espera da mesma categoria levam a experiências de solução de problemas muito semelhantes, e fornecer a consulta afetada além das esperas seria a peça que faltava para concluir a maioria dessas investigações com êxito.

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

Experiência anterior Nova experiência Ação
Altas esperas de RESOURCE_SEMAPHORE por banco de dados Alta memória aguarda consultas específicas no Repositório de Consultas Encontre as principais consultas que consomem memória no Repositório de Consultas. Essas consultas provavelmente estão atrasando o progresso das consultas afetadas. Considere usar a sugestão de consulta MAX_GRANT_PERCENT para essas consultas, ou especificamente para as consultas afetadas.
Esperas elevadas de LCK_M_X por base de dados O High Lock aguarda consultas específicas no Repositório de Consultas Verifique os textos de consulta para as consultas afetadas e identifique as entidades de destino. Procure no Repositório de Consultas outras consultas modificando a mesma entidade, que são executadas com freqüê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 usar um nível de isolamento menos restritivo.
Esperas altas de PAGEIOLATCH_SH em cada base de dados Altos tempos de espera de E/S de buffer no Repositório de Consultas para consultas específicas Encontre as consultas com um elevado número de leituras físicas no Query Store. Se corresponderem às consultas com longas esperas de E/S, considere criar um índice na entidade subjacente, a fim de realizar buscas em vez de varreduras e, assim, minimizar a sobrecarga de E/S das consultas.
Esperas elevadas de SOS_SCHEDULER_YIELD por base de dados Altas esperas de CPU para consultas específicas no Repositório de Consultas Encontre as principais consultas que consomem CPU no Repositório de Consultas. Entre eles, identificar as consultas para as quais a alta tendência de CPU se correlaciona com altas esperas de CPU para as consultas afetadas. Concentre-se em otimizar essas consultas - pode 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 os parâmetros do Repositório de Consultas, consulte opções do ALTER DATABASE SET (Transact-SQL).

Consulte o modo de 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 sobre como definir opções de configuração usando instruções Transact-SQL, consulte Option Management.

Observação

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 os modos de exibição e procedimentos a seguir.

Funções do Query Store

As funções facilitam as operações com o Repositório de Consultas.

sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)

Visualizações do 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 (Transact-SQL)

sys.query_context_settings (Transact-SQL)

sys.query_store_plan (Transact-SQL)

sys.query_store_query (Transact-SQL)

sys.query_store_query_text (Transact-SQL)

sys.query_store_runtime_stats (Transact-SQL)

sys.query_store_wait_stats (Transact-SQL)

sys.query_store_runtime_stats_interval (Transact-SQL)

sys.database_query_store_internal_state (Transact-SQL)

Procedimentos armazenados do Repositório de Consultas

Os procedimentos armazenados configuram o Repositório de Consultas.

sp_query_store_flush_db (Transact-SQL)

sp_query_store_reset_exec_stats (Transact-SQL)

sp_query_store_force_plan (Transact-SQL)

sp_query_store_unforce_plan (Transact-SQL)

sp_query_store_remove_plan (Transact-SQL)

sp_query_store_remove_query (Transact-SQL)

sp_query_store_clear_message_queues (Transact-SQL)

sp_query_store_consistency_check (Transact-SQL)1

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

Manutenção do Query Store

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

Auditoria de desempenho e resolução de problemas

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

Outros tópicos de desempenho:

Conteúdo relacionado