Explorar o Repositório de Consultas
O SQL Server Query Store é um recurso por banco de dados que captura automaticamente um histórico de consultas, planos e estatísticas de runtime, simplificando a solução de problemas de desempenho e o ajuste de consulta. Ele também fornece insights sobre os padrões de uso do banco de dados e o consumo de recursos.
O Repositório de Consultas consiste em três repositórios:
- Repositório de planos: armazena informações estimadas do plano de execução.
- Repositório de estatísticas de runtime: armazena informações de estatísticas de execução.
- Armazenamento de estatísticas de espera: persiste 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 consulta em vários 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 estimado inicial são enviados ao Repositório de Consultas e persistidos.
O plano é atualizado no Repositório de Consultas quando uma consulta é recompilar. 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 mantém o controle das estatísticas de execução para 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 informações valiosas sobre o desempenho das operações de banco de dados. Cenários comuns incluem:
- Identificar e corrigir regressões de desempenho devido à seleção inferior do plano de execução de consulta.
- Identificando e ajustando as consultas de consumo de recursos mais altas.
- Teste A/B para avaliar os impactos das alterações de banco de dados e aplicativo.
- Garantindo a estabilidade do desempenho após as atualizações do SQL Server.
- Determinando as consultas mais usadas.
- Auditando o histórico de planos de consulta para uma consulta.
- Identificar e melhorar cargas de trabalho não planejadas.
- Noções básicas sobre as categorias de espera predominantes de um banco de dados e as consultas e planos que contribuem para o tempo de espera.
- Analisando padrões de uso de banco de dados ao longo do tempo em termos de 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 experimenta degradação de desempenho ao longo do tempo devido a alterações no plano de execução. Planos de execução estimados podem ser alterados devido a vários fatores, incluindo alterações de esquema, alterações de estatísticas e alterações de índice. Investigar o cache de procedimentos pode ser o primeiro instinto, mas ele armazena apenas o plano de execução mais recente para uma consulta e os planos podem ser removidos com base nas demandas de memória do sistema. O Repositório de Consultas, no entanto, persiste vários planos de execução para cada consulta, permitindo a flexibilidade de escolher um plano específico por meio de uma força de plano para lidar com a regressão de desempenho da consulta causada por alterações 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. Essa exibição permite filtragem com base em uma métrica selecionada (como duração, tempo de CPU, contagem de linhas e muito mais) e uma estatística (total, média, mínimo, máximo ou desvio padrão). Em seguida, ele lista as 25 principais consultas regredidas com base no filtro fornecido. Por padrão, uma exibição de gráfico de barras gráficas das consultas é exibida, mas você pode, opcionalmente, exibir as consultas em um formato de grade.
Depois de selecionar uma consulta no painel de consulta superior esquerdo, o painel resumo do plano exibirá os planos de consulta persistentes associados à consulta ao longo do tempo. Selecionar um plano de consulta no painel Resumo do Plano mostra um plano de consulta gráfica no painel inferior. Os botões da barra de ferramentas no painel resumo do plano e no painel de plano de consulta gráfica permitem forçar o plano selecionado para a consulta selecionada. Essa estrutura e o comportamento do painel são usados consistentemente 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 não planejada da carga de trabalho ao identificar e melhorar cargas de trabalho não planejadas. 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 ser ajustadas, ela 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 detalha 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 exibe o plano de consulta gráfica 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.
Correção automática de plano
O SQL Server 2017 e o Banco de Dados SQL do Azure introduziram o conceito de correção automática de plano analisando os dados no Repositório de Consultas. Quando você habilita o Repositório de Consultas com um banco de dados no SQL Server 2017 (ou posterior) e no Banco de Dados SQL do Azure, o mecanismo do SQL Server procura regressões do plano de consulta e fornece recomendações. Você pode ver essas recomendações na DMV (exibição de gerenciamento dinâmico) sys.dm_db_tuning_recommendations. Essas recomendações incluem instruções T-SQL para forçar manualmente um plano de consulta quando o desempenho está em um bom estado.
Se tiver confiança nessas recomendações, você poderá habilitar o SQL Server a forçar os planos automaticamente quando regressões forem encontradas. Habilite a correção automática do plano usando ALTER DATABASE e o argumento AUTOMATIC_TUNING.
Para o Banco de Dados SQL do Azure, você também pode habilitar a correção automática de plano por meio das opções de ajuste automático nas APIs REST ou no portal do Azure. As recomendações da correção automática de plano sempre estão habilitadas para qualquer banco de dados em que o Repositório de Consultas está habilitado (que é o padrão para o Banco de Dados SQL do Azure e a Instância Gerenciada de SQL do Azure). Para novos bancos de dados, a correção automática de plano (FORCE_PLAN) fica habilitada por padrão para o Banco de Dados SQL do Azure.