Explore 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 tempo de execução para simplificar a solução de problemas de desempenho e o ajuste de consultas. Ele também fornece informações sobre padrões de uso de banco de dados e consumo de recursos.
No total, o Repositório de Consultas contém três armazenamentos:
- Armazenamento de planos - usado para armazenar informações estimadas do plano de execução
- Armazenamento de estatísticas de tempo de execução - usado para armazenar informações de estatísticas de execução
- Armazenamento de estatísticas de espera - para informações de estatísticas de espera persistentes
Habilitar o repositório de consultas
O Repositório de Consultas é habilitado por padrão nos 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 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 forma 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 inicial estimado são enviados para o Repositório de Consultas e persistem.
O plano é atualizado no Repositório de Consultas quando uma consulta é recompilada. Se a recompilação resultar em um plano de execução recém-gerado, ela também persistirá no Repositório de Consultas para aumentar 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 de 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 tempo de execução persistem 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 o Repositório de Consultas coleta dados, consulte Como o Repositório de Consultas coleta dados.
Cenários comuns
O Repositório de Consultas do SQL Server fornece informações valiosas sobre o desempenho das operações executadas em um banco de dados. Os cenários mais comuns incluem:
Identificando e corrigindo a regressão de desempenho devido à seleção inferior do plano de execução da consulta
Identificando e ajustando as consultas de maior consumo de recursos
Testes A/B para avaliar os impactos das alterações no banco de dados e no aplicativo
Garantindo a estabilidade de desempenho após atualizações do SQL Server
Determinando as consultas usadas com mais freqüência
Auditar o histórico de planos de consulta para uma consulta
Identificação e melhoria de cargas de trabalho ad hoc
Compreender as categorias de espera predominantes de um banco de dados e as consultas e planos que contribuem para afetar os tempos de espera
Analise os padrões de uso do banco de dados ao longo do tempo à medida que ele se aplica ao consumo de recursos (CPU, I/O, Memory)
Descubra os modos de exibição do Repositório de Consultas
Depois que o Repositório de Consultas estiver habilitado em um banco de dados, a pasta Repositório de Consultas ficará 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 exibidas em Exibições do Sistema. As exibições do Repositório de Consultas fornecem informações agregadas e rápidas sobre os aspetos de desempenho do banco de dados do SQL Server.
Consultas em Regressão
Uma consulta regressada é uma consulta que está sofrendo degradação de 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 de procedimentos, mas o problema com o cache de procedimentos é que ele armazena apenas o plano de execução mais recente para uma consulta; Mesmo assim, os planos são despejados 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, proporcionando assim a flexibilidade de escolher um plano específico em um conceito conhecido como forçar plano para resolver o problema de uma regressão de desempenho de consulta causada por uma alteração de plano.
O modo de exibição Consultas Regressadas 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 de tempo especificado. A visualização Consultas Regressadas permite a filtragem com base na seleção de uma métrica (como duração, tempo da CPU, contagem de linhas e muito mais) e de uma estatística (total, média, min, max ou desvio padrão). Em seguida, o modo de exibição lista as 25 principais consultas regredidas com base no filtro fornecido. Uma exibição gráfica de gráfico de barras das consultas é exibida por padrão, mas você pode, opcionalmente, exibir as consultas em um formato de grade.
O painel de resumo do plano exibe os planos de consulta persistentes 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, os botões da barra de ferramentas estão disponíveis no painel de resumo do plano e no painel de plano de consulta gráfica para forçar o plano selecionado para a consulta selecionada. Essa estrutura e comportamento do painel são usados consistentemente em todas as exibições de Consulta SQL.
Como alternativa, você pode usar o procedimento armazenado para usar a sp_query_store_force_plan força de plano.
EXEC sp_query_store_force_plan @query_id=73, @plan_id=79
Consumo Global do Recurso
A visualização Consumo Geral de Recursos permite analisar o consumo total de recursos para várias métricas de execução (como contagem de execução, duração, tempo de espera e muito mais) para um período de tempo especificado. Os gráficos renderizados são interativos; Ao selecionar uma medida de um dos gráficos, uma exibição de detalhamento exibindo as consultas associadas à medida escolhida é exibida em uma nova guia.
A visualização de detalhes fornece as 25 principais consultas de consumidor de recursos que contribuíram para a métrica selecionada. Essa exibição de detalhes usa a interface consistente que permite a inspeção das consultas associadas e seus detalhes, avaliar planos de consulta estimados salvos e, opcionalmente, usar a força de plano para melhorar o desempenho. Essa exibição é valiosa quando a contenção de recursos do sistema se torna um problema, como quando o uso da CPU atinge a capacidade.
Consultas Principais de Consumo de Recursos
O modo de exibição Principais Consultas que Consomem Recursos é semelhante ao detalhamento do modo de exibição Consumo Geral de Recursos. Ele também permite selecionar uma métrica e uma estatística como um filtro. No entanto, as consultas que ele exibe são as 25 consultas mais impactantes com base no filtro e no período de tempo escolhidos.
O modo de exibição Principais Consultas que Consomem Recursos fornece a primeira indicação da natureza ad hoc da carga de trabalho ao identificar e melhorar cargas de trabalho ad hoc. Por exemplo, na imagem a seguir, a métrica Contagem de Execução 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
O modo de exibição Consultas com planos forçados fornece uma visão rápida das consultas que forçaram planos de consulta. Esta visão torna-se relevante se um plano forçado já não tiver o desempenho esperado e precisar de ser reavaliado. Essa exibição fornece a capacidade de revisar todos os planos de execução estimados persistentes para uma consulta selecionada, determinando facilmente se outro plano agora é mais adequado para desempenho. Em caso afirmativo, os botões da barra de ferramentas estão disponíveis para desforçar um plano, conforme necessário.
Consultas com alta variação
O desempenho da consulta pode variar entre as execuções. O modo de 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 dos modos de exibição do Repositório de Consultas, permitindo a inspeção de detalhes da consulta, a avaliação do plano de execução e, opcionalmente, forçando 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 de consulta
O modo de exibição Estatísticas de Espera de Consulta analisa as categorias de espera mais ativas para o banco de dados e renderiza um gráfico. Este gráfico é interativo; A seleção de uma categoria de espera detalha os detalhes das consultas que contribuem para a estatística de tempo de espera.
A interface de exibição de detalhes também é consistente com a maioria das exibições de armazenamento de consulta, permitindo a inspeção de detalhes da consulta, a avaliação do plano de execução e, opcionalmente, a imposição de um plano específico. Essa exibição ajuda a identificar consultas que estão afetando a experiência do usuário em todos os aplicativos.
Consulta de acompanhamento
O modo de exibição Consulta de Acompanhamento permite analisar uma consulta específica com base em um valor de ID de consulta inserido. Uma vez executada, a exibição fornece o histórico de execução completo da consulta. Uma marca de verificação em uma execução indica que um plano forçado foi usado. Essa exibição pode fornecer informações sobre 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 degradar-se, faz sentido consultar estatísticas de espera de consulta para potencialmente identificar uma causa. Além de identificar consultas que precisam de ajustes, ele também pode lançar luz sobre possíveis atualizações de infraestrutura que seriam benéficas.
O Repositório de Consultas SQL fornece o modo de exibição Estatísticas de Espera de Consulta para fornecer informações sobre as principais categorias de espera para o 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 o modo de 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 total de espera (padrão), tempo médio de espera, tempo mínimo de espera, tempo máximo de espera ou tempo de espera de desvio padrão.
A seleção de uma categoria de espera analisará detalhadamente os detalhes das consultas que contribuem para essa categoria de espera. A partir dessa visão, você tem a capacidade de investigar consultas individuais que são as mais impactantes. Você pode acessar a exibição de planos de execução estimados persistentes no painel Resumo do plano selecionando uma consulta no painel de consulta. A seleção de um plano de consulta no painel Resumo do plano exibirá o plano de consulta gráfico no painel inferior. A partir dessa exibição, você tem a capacidade de forçar ou desforçar um plano de consulta para melhorar o desempenho.
