Monitorar 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 de SQL do AzureAzure Synapse Analytics (somente pool de SQL dedicado)
O recurso Repositório de Consultas fornece insights sobre a escolha e o desempenho do plano de consulta para o SQL Server, o Banco de Dados SQL do Azure, a Instância Gerenciada de SQL do Azure e o Azure Synapse Analytics. Ele simplifica a solução de problemas, ajudando você a identificar rapidamente diferenças de desempenho causadas por alterações nos planos 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 por hora, permitindo que você veja os padrões de uso do banco de dados e entenda quando as alterações aos planos de consulta ocorreram no servidor. O repositório de consultas pode ser configurado usando a opção ALTER DATABASE SET .
- 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.
- 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 como moldar planos de consulta sem alterar o código do aplicativo, confira Dicas do Repositório de Consultas.
Importante
Se você estiver usando o Repositório de Consultas para obter insights de carga de trabalho em tempo real no SQL Server 2016 (13.x), planeje instalar as correções de escalabilidade de desempenho na KB 4340759 o quanto antes.
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 é habilitado por padrão no SQL Server 2016 (13.x), SQL Server 2017 (14.x), SQL Server 2019 (15.x). Ele é habilitado por padrão no modo
READ_WRITEpara 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.
Usar a página do Repositório de Consultas no SQL Server Management Studio
No Pesquisador de Objetos, clique com o botão direito do mouse em um banco de dados e selecione Propriedades.
Observação
Requer, no mínimo, a versão 16 do Management Studio.
Na caixa de diálogo Propriedades do Banco de Dados , selecione a página Repositório de Consultas .
Na caixa Modo de Operação (Solicitado), selecione Leitura 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);
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, confira Opções ALTER DATABASE SET (Transact-SQL).
Observação
O Repositório de Consultas não pode ser habilitado para os bancos de dados de master ou tempdb.
Importante
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 do plano devido à pressão da 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 USE PLAN , mas não requer nenhuma alteração nos aplicativos do usuário. A imposição de plano pode resolver uma regressão de desempenho de consulta causada por uma alteração do plano em um período muito curto.
Observação
O Repositório de Consultas coleta planos para Instruções DML, como SELECT, INSERT, UPDATE, DELETE, MERGE e BULK INSERT.
Por opção, 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 popular 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 somente no nível da instância, o que dificultava o retorno das esperas de 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:
- Localizar e corrigir rapidamente uma regressão de desempenho do plano, forçando 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.
- Fazer auditoria de histórico dos planos de consulta para determinada consulta.
- Analisar os padrões de uso dos recursos (CPU, E/S e memória) para determinado banco de dados.
- Identifique as principais n consultas que estão esperando em recursos.
- Entenda a natureza de espera de um plano ou de uma consulta específica.
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 otimizar o uso do espaço, as estatísticas de runtime no repositório de estatísticas de runtime são agregadas em 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 e planos no Repositório de Consultas.
SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
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;
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 do Repositório de Consultas em cargas de trabalho de réplicas secundárias disponíveis 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. Há basicamente 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.
Veja informações completas sobre o Repositório de Consultas para réplicas secundárias em Repositório de Consultas para réplicas secundárias de grupos de disponibilidade Always On.
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.
Observação
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 consultas e planos no repositório de consultas. Use as caixas de lista suspensas 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 do CLR (ms), DOP, Consumo de Memória (KB), Contagem de Linhas, Registro de Memória Usada (KB) Memória Usada pelo BD Temporário (KB) e o 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.
Para impor um plano, selecione uma consulta, um plano e selecione Impor 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 v18 ou posterior. O painel Estatísticas de Espera da Consulta mostra a você um gráfico de barras que contém os principais categorias de espera no Repositório de Consultas. Use 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).
Escolha uma categoria de espera selecionando a barra, e o modo de exibição Detalhe é exibido na categoria de espera selecionada. Esse novo gráfico de barras contém as consultas que contribuíram para essa categoria de espera.
Use a lista 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 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 tipos diferentes de espera em buckets semelhantes por natureza. Categorias de espera diferentes exigem um acompanhamento de análise diferente para resolver o problema, mas tipos de espera de mesma categoria levam a experiências de solução de problemas muito semelhantes, e fornecer a consulta afetada com base nessas 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 as categorias de espera no Repositório de Consultas:
| Experiência anterior | Nova experiência | Ação |
|---|---|---|
| RESOURCE_SEMAPHORE alto de esperas 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 estão provavelmente atrasando o andamento das consultas afetadas. Considere usar a dica de consulta MAX_GRANT_PERCENT para essas consultas ou para as consultas afetadas. |
| Espera de LCK_M_X alta 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. |
| Esperas de PAGEIOLATCH_SH altas por banco de dados | Esperas de buffer de E/S altas no Repositório de Consultas para consultas específicas | Localize as consultas com um grande número de leituras físicas no Repositório de Consultas. Se elas corresponderem às consultas com esperas de E/S, considere introduzir um índice na entidade subjacente, para fazer buscas em vez de verificações e, portanto, minimizar a sobrecarga de E/S das consultas. |
| Esperas de SOS_SCHEDULER_YIELD altas 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 CPU alta se correlaciona às esperas de CPU altas para as 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 a fim de configurar os parâmetros do Repositório de Consultas, confira Opções ALTER DATABASE SET (Transact-SQL).
Consulte a exibição de 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.
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.
Exibições, funções e procedimentos relacionados
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 ajudam as operações com o Repositório de Consultas.
Exibições do catálogo do Repositório de Consultas
As exibições do catálogo apresentam informações sobre o Repositório de Consultas.
Procedimentos armazenados do Repositório de Consultas
Os procedimentos armazenados configuram o Repositório de Consultas.
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:
Confira também
- Procedimentos armazenados do Repositório de Consultas (Transact-SQL)
- Exibições de Catálogo do Repositório de Consultas (Transact-SQL)
- sys.database_query_store_options (Transact-SQL)
- Estatísticas de consulta dinâmica
- Monitor de Atividade
- Como o Repositório de Consultas coleta dados
- Monitorar e ajustar o desempenho
- Ferramentas para monitoramento e ajuste de desempenho
- Usar o Repositório de Consultas com OLTP na memória
Próximas etapas
Comentários
Brevemente: Ao longo de 2024, vamos descontinuar progressivamente o GitHub Issues como mecanismo de feedback para conteúdos e substituí-lo por um novo sistema de feedback. Para obter mais informações, veja: https://aka.ms/ContentUserFeedback.
Submeter e ver comentários