Explorar o Repositório de Consultas
O Repositório de Consultas do SQL Server é um recurso por banco de dados que captura automaticamente um histórico de consultas, planos e estatísticas de runtime para simplificar a solução de problemas de desempenho e o ajuste de consultas. Ele também fornece insights sobre os padrões de uso e o consumo de recursos do banco de dados.
No total, o Repositório de Consultas contém três repositórios:
- Repositório de planos – usado para armazenar informações estimadas do plano de execução
- Repositório de estatísticas de runtime – usado para armazenar informações de estatísticas de execução
- Repositório de estatísticas de espera – para manter informações de estatísticas de espera
Habilitar o Repositório de Consultas
O Repositório de Consultas é habilitado por padrão em bancos de dados SQL do Azure. Se você quiser usá-lo com o SQL Server e o Azure Synapse Analytics, precisará habilitá-lo primeiro. Para habilitar o recurso do Repositório de Consultas, use a seguinte consulta válida para seu ambiente:
-- SQL Server
ALTER DATABASE <database_name> SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
-- Azure Synapse Analytics
ALTER DATABASE <database_name> SET QUERY_STORE = ON;
Como o Repositório de Consultas coleta dados
O Repositório de Consultas integra-se ao pipeline de processamento de consultas em muitos estágios. Em cada ponto de integração, os dados são coletados na memória e gravados em disco de maneira assíncrona para minimizar a sobrecarga de E/S. Os pontos de integração são os seguintes:
Quando uma consulta é executada pela primeira vez, o texto da consulta e o plano de execução estimado inicial são enviados ao Repositório de Consultas e persistidos.
O plano é atualizado no Repositório de Consultas quando uma consulta é recompilada. Se a recompilação resultar em um novo plano de execução, ele também será persistido no Repositório de Consultas para complementar os planos anteriores. Além disso, o Repositório de Consultas controla as estatísticas de execução de cada plano de consulta para fins de comparação.
Durante as fases de compilação e verificação da recompilação, o Repositório de Consultas identifica se há um plano forçado para que a consulta seja executada. A consulta será recompilada se o Repositório de Consultas fornecer um plano forçado diferente do plano no cache de procedimentos.
Quando uma consulta é executada, suas estatísticas de runtime são persistidas no Repositório de Consultas. O Repositório de Consultas agrega esses dados para garantir uma representação precisa de cada plano de consulta.
Para saber mais sobre como Repositório de Consultas coleta dados, confira Como o Repositório de Consultas coleta dados.
Cenários comuns
O Repositório de Consultas do SQL Server fornece insights valiosos sobre o desempenho das operações executadas em um banco de dados. Os cenários mais comuns incluem:
Identificar e corrigir a regressão no desempenho devido à seleção de um plano de execução de consulta inferior
Identificar e ajustar as consultas com consumo de recursos mais elevado
Teste A/B para avaliar os impactos das alterações no banco de dados e no aplicativo
Garantir a estabilidade do desempenho após atualizações do SQL Server
Determinar as consultas com uso mais frequente
Fazer auditoria do histórico dos planos de consulta de uma consulta
Identificando e melhorando as cargas de trabalho ad hoc
Entender as categorias de espera predominantes de um banco de dados e as consultas e planos que afetam os tempos de espera
Analisar os padrões de uso do banco de dados ao longo do tempo no que diz respeito ao consumo de recursos (CPU, E/S, Memória)
Descobrir as exibições do Repositório de Consultas
Após o Repositório de Consultas ser habilitado em um banco de dados, a pasta do Repositório de Consultas fica visível para o banco de dados no Pesquisador de Objetos. Para o Azure Synapse Analytics, as exibições do Repositório de Consultas são mostradas nas Exibições do Sistema. As exibições do Repositório de Consultas fornecem insights agregados e rápidos sobre os aspectos de desempenho do banco de dados do SQL Server.
Consultas Regredidas
Uma consulta regredida é uma consulta que está enfrentando degradação do desempenho ao longo do tempo devido a alterações no plano de execução. Os planos de execução estimados mudam devido a muitos fatores, incluindo alterações de esquema, alterações de estatísticas e alterações de índice. O primeiro instinto pode ser investigar o cache do procedimento, mas o problema com esse cache é que ele armazena apenas o plano de execução mais recente de uma consulta. Ainda assim, os planos são removidos com base nas demandas de memória do sistema. No entanto, o Repositório de Consultas persiste vários planos de execução armazenados para cada consulta, fornecendo assim flexibilidade para escolher um plano específico em um conceito conhecido como forçar o plano, a fim de resolver o problema de regressão de desempenho de uma consulta causada por uma alteração de plano.
A exibição Consultas Regredidas pode identificar consultas cujas métricas de execução estão regredindo devido a alterações no plano de execução em um período especificado. A exibição Consultas Regredidas permite a filtragem com base na seleção de uma métrica (como duração, tempo de CPU, contagem de linhas, entre outras) e de uma estatística (total, média, mínimo, máximo ou desvio padrão). Em seguida, a exibição lista as 25 principais consultas regredidas com base no filtro fornecido. Uma exibição de gráfico de barras das consultas é mostrada por padrão, mas você pode exibir as consultas em formato de grade.
O painel de resumo do plano exibe os planos de consulta persistidos associados à consulta ao longo do tempo depois de selecionar uma consulta no painel de consulta superior esquerdo. Você verá um plano de consulta gráfico no painel inferior selecionando um plano de consulta no painel Resumo do Plano. Além disso, botões de barra de ferramentas estão disponíveis no painel de resumo do plano e no painel do plano de consulta gráfico para forçar o plano selecionado para a consulta selecionada. A estrutura e o comportamento do painel são usados de maneira consistente em todas as exibições de consulta SQL.
Como alternativa, você pode usar o procedimento armazenado sp_query_store_force_plan para usar o recurso de forçar o plano.
EXEC sp_query_store_force_plan @query_id=73, @plan_id=79
Consumo Geral de Recursos
A exibição Consumo Geral de Recursos permite analisar o consumo total de recursos para várias métricas de execução (como contagem, duração e tempo de espera da execução, entre outros) para um período especificado. Os gráficos renderizados são interativos. Ao selecionar uma medida em um dos gráficos, uma exibição detalhada, mostrando as consultas associadas à medida escolhida, é exibida em uma nova guia.
A exibição detalhada fornece as 25 consultas com maior consumo de recursos que contribuíram para a métrica selecionada. Essa exibição detalhada usa a interface consistente que permite a inspeção das consultas associadas e seus detalhes, a avaliação dos planos de consulta estimados salvos e, opcionalmente, o uso do recurso de forçar o plano para aprimorar o desempenho. Essa exibição é valiosa quando a contenção de recursos do sistema se torna um problema, por exemplo, quando o uso da CPU atinge a capacidade máxima.
Consultas que Mais Consomem Recursos
A exibição Principais Consultas de Consumo de Recursos é semelhante ao detalhamento da exibição Consumo Geral de Recursos. Ela também permite selecionar uma métrica e uma estatística como filtro. No entanto, as consultas exibidas são as 25 consultas mais impactantes com base no filtro e no período escolhidos.
A exibição Principais Consultas de Consumo de Recursos fornece a primeira indicação da natureza ad hoc da carga de trabalho ao identificar e aprimorar cargas de trabalho ad hoc. Por exemplo, na imagem a seguir, a métrica Contagem de Execuções e a estatística Total são selecionadas para revelar que aproximadamente 90% das principais consultas que consomem recursos são executadas apenas uma vez.
Consultas com planos forçados
A exibição Consultas com Planos Forçados fornece uma visão rápida das consultas que têm planos de consulta forçados. Essa exibição se tornará relevante se um plano forçado não for mais executado conforme o esperado e precisar ser reavaliado. Essa exibição permite examinar todos os planos de execução estimados persistidos para uma consulta selecionada, determinando facilmente se outro plano agora é mais adequado para o desempenho. Se for esse o caso, botões de barra de ferramentas estarão disponíveis para deixar de forçar um plano conforme necessário.
Consultas com alta variação
O desempenho da consulta pode variar entre execuções. A exibição Consultas com Alta Variação contém uma análise das consultas que têm a maior variação ou desvio padrão para uma métrica selecionada. A interface é consistente com a maioria das exibições do Repositório de Consultas que permitem inspecionar os detalhes da consulta, avaliar o plano de execução e, opcionalmente, forçar um plano específico. Use essa exibição para ajustar consultas imprevisíveis em um padrão de desempenho mais consistente.
Estatísticas de Espera da Consulta
A exibição Estatísticas de Espera da Consulta analisa as categorias de espera mais ativas para o banco de dados e renderiza um gráfico. Este gráfico é interativo; selecionar uma categoria de espera mostra os detalhes das consultas que contribuem para a estatística de tempo de espera.
A interface da exibição de detalhes também é consistente com a maioria das exibições do Repositório de Consultas que permitem inspecionar os detalhes da consulta, avaliar o plano de execução e, opcionalmente, forçar um plano específico. Essa exibição ajuda a identificar consultas que estão afetando a experiência do usuário entre aplicativos.
Rastreamento de Consulta
A exibição Rastreamento de Consulta permite analisar uma consulta específica com base em um valor de ID de consulta inserido. Após executada, a exibição fornece o histórico de execução completo da consulta. Uma marca de seleção em uma execução indica que um plano forçado foi usado. Essa exibição pode fornecer insights sobre as consultas, como aquelas com planos forçados, para verificar se o desempenho da consulta permanece estável.
Usando o Repositório de Consultas para localizar esperas de consulta
Quando o desempenho de um sistema começa a ser degradado, faz sentido consultar as estatísticas de espera de consulta para identificar uma causa. Além de identificar consultas que precisam de ajuste, isso também pode lançar luz sobre possíveis atualizações de infraestrutura que seriam benéficas.
O Repositório de Consultas SQL fornece a exibição Estatísticas de Espera de Consulta para fornecer insights sobre as principais categorias de espera do banco de dados. Atualmente, há 23 categorias de espera.
Um gráfico de barras exibe as categorias de espera mais impactantes para o banco de dados quando você abre a exibição Estatísticas de Espera de Consulta. Além disso, um filtro localizado na barra de ferramentas do painel de categorias de espera permite que as estatísticas de espera sejam calculadas com base no tempo de espera total (padrão), no tempo de espera médio, no tempo de espera mínimo, no tempo de espera máximo ou no tempo de espera de desvio padrão.
Selecionar uma categoria de espera analisará os detalhes das consultas que contribuem para essa categoria de espera. Nessa exibição, você pode investigar consultas individuais que são as mais impactantes. Você pode acessar a exibição dos planos de execução estimados persistentes no painel Resumo do Plano selecionando uma consulta no painel de consulta. Selecionar um plano de consulta no painel Resumo do Plano exibirá o plano de consulta gráfico no painel inferior. Nessa exibição, você pode ativar ou desativar o recurso de forçar um plano de consulta para que o desempenho da consulta seja aprimorado.
