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, simplificando 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.
O Repositório de Consultas consiste em três lojas:
- Repositório de planos: armazena informações estimadas do plano de execução.
- Armazenamento de estatísticas de tempo de execução: armazena informações de estatísticas de execução.
- Armazenamento de estatísticas de espera: armazena as informações de estatísticas de espera.
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 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 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 de banco de dados. Os cenários comuns incluem:
- Identificação e correção de regressões de desempenho devido à seleção inferior do plano de execução da consulta.
- Identificar e ajustar as consultas de maior consumo de recursos.
- Testes A/B para avaliar os impactos das alterações no banco de dados e no aplicativo.
- Garantir a estabilidade de desempenho após atualizações do SQL Server.
- Determinar as consultas usadas com mais frequência.
- Auditar o histórico de planos de consulta para uma consulta.
- Identificação e melhoria de cargas de trabalho não planeadas.
- Compreender as categorias de espera predominantes de um banco de dados e as consultas e planos que contribuem para afetar os tempos de espera.
- Analisar padrões de uso do banco de dados ao longo do tempo em termos de 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 regredida sofre degradação de desempenho ao longo do tempo devido a alterações no plano de execução. Os planos de execução estimados podem mudar 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 da imposição de plano para abordar a regressão de desempenho da consulta causada por alterações 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. Essa exibição permite a filtragem com base em uma métrica selecionada (como duração, tempo da CPU, contagem de linhas e muito mais) e uma estatística (total, média, min, max ou desvio padrão). Em seguida, ele lista as 25 principais consultas regredidas com base no filtro fornecido. Por padrão, uma exibição gráfica de gráfico de barras 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 de resumo do plano exibe os planos de consulta persistentes associados à consulta ao longo do tempo. A seleção de um plano de consulta no painel Resumo do Plano mostra um plano de consulta gráfico no painel inferior. Os botões da barra de ferramentas no painel de 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 do SQL Query.
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 não planeada da carga de trabalho, isto de modo a identificar e melhorar cargas de trabalho não planeadas. 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 consumidoras de recursos são executadas apenas uma vez.
Consultas com planos forçados
A vista Consultas com planos forçados fornece uma visão rápida das consultas que têm planos de consulta forçados. 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 ser ajustadas, 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 detalha 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 exibe 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.
Correção automática do plano
O SQL Server 2017 e a Base de Dados SQL do Azure introduziram o conceito de correção automática do plano ao analisarem dados no Arquivo de Consulta. Ao ativar o Arquivo de Consulta com uma base de dados no SQL Server 2017 (ou posterior) e na Base de Dados SQL do Azure, o motor do SQL Server procura regressões do plano de consulta e fornece recomendações. Você pode ver essas recomendações na sys.dm_db_tuning_recommendations visualização de gerenciamento dinâmico (Detran). Estas recomendações incluem instruções T-SQL para forçar manualmente um plano de consulta quando o desempenho está em bom estado.
Se ganhar confiança nestas recomendações, pode ativar o SQL Server para forçar planos automaticamente ao encontrar regressões. Habilite a correção automática do plano usando ALTER DATABASE e o AUTOMATIC_TUNING argumento.
No caso da Base de Dados SQL do Azure, também pode ativar a correção automática do plano através de opções de otimização automática no portal do Azure ou ao utilizar APIs REST. As recomendações da correção automática do plano são sempre ativadas para qualquer base de dados na qual o Arquivo de Consulta esteja ativado (trata-se da predefinição da Base de Dados SQL do Azure e do Azure SQL Managed Instance). Para novos bancos de dados, a correção automática de plano (FORCE_PLAN) é habilitada por padrão para o Banco de Dados SQL do Azure.