Introdução ao Assistente de Ajuste de Consulta (QTA)
Você planeja migrar dois bancos de dados de produção do SQL Server 2012 para uma nova instância do SQL Server 2022. Como os bancos de dados estão em uso, inicialmente você deseja deixar os níveis de compatibilidade nas configurações originais e reposicionar os aplicativos. O avaliador de cardinalidade foi alterado desde o SQL Server 2014 e você deseja medir o desempenho da consulta antes de alterar o nível de compatibilidade dos bancos de dados para o SQL Server 2022 (160).
Você precisa encontrar e corrigir as consultas que regridem quando você muda o nível de compatibilidade e aplica o novo algoritmo de avaliador de cardinalidade. Usar essa abordagem mede uma linha de base para que o desempenho seja comparado depois de atualizar o nível de compatibilidade.
Habilitar o Repositório de Consultas reúne as métricas necessárias para encontrar consultas de regressão e usar o QTA (Assistente de Ajuste de Consulta) orienta você pela correção das consultas de regressão. Esta unidade fornece uma visão geral do Repositório de Consultas e do QTA.
Visão geral do Repositório de Consultas
O recurso Repositório de Consultas foi introduzido no SQL Server 2016 para coletar continuamente informações sobre a execução e o desempenho de consultas em um banco de dados. O Repositório de Consultas opera como um gravador de dados de pré-lançamento para coletar informações de runtime sobre consultas e planos. Se você salvar esses dados de tempo de execução, poderá acompanhar o desempenho ao longo do tempo. Se algo der errado, haverá um histórico de informações para descobrir a causa do problema.
A partir do SQL Server 2022 e do Banco de Dados SQL do Azure e da Instância Gerenciada de SQL, o Repositório de Consultas está habilitado para novos bancos de dados por padrão. No SQL Server 2016, SQL Server 2017 e SQL Server 2019, o Repositório de Consultas não está habilitado por padrão, mas qualquer banco de dados em uma instância do SQL Server 2016 ou superior pode habilitar, desabilitar e configurar o Repositório de Consultas. Consulte a próxima unidade para obter instruções sobre como habilitar o Repositório de Consultas ou confirmar sua configuração em um banco de dados.
O Repositório de Consultas pode funcionar em bancos de dados com níveis de compatibilidade anteriores à instância do SQL Server. Por exemplo, se você migrar um banco de dados do SQL Server 2012 para o SQL Server 2022 e mantiver o nível de compatibilidade em 110, o Repositório de Consultas ainda poderá operar no banco de dados.
No entanto, muitos recursos do Processamento Inteligente de Consultas e outros aprimoramentos automáticos de desempenho serão habilitados apenas para níveis de compatibilidade de banco de dados mais recentes. Portanto, você deve tentar testar o desempenho do aplicativo no nível de compatibilidade de banco de dados mais recente do SQL Server. O Repositório de Consultas e o QTA podem ajudar nesse teste de desempenho.
Quando habilitado em um banco de dados, o Repositório de Consultas reúne e relata as seguintes estatísticas para suas consultas:
- Consultas Regredidas
- Consumo Geral de Recursos
- Consultas que Mais Consomem Recursos
- Consultas com planos forçados
- Consultas com alta variação
- Estatísticas de Espera da Consulta
- Consultas Rastreadas
Uma consulta regredida ocorre quando o otimizador de consulta usa um plano de consulta diferente que faz com que o desempenho seja prejudicado. A regressão pode ocorrer após alterações importantes, como adicionar, remover ou alterar um índice, atualizar estatísticas ou alterar a cardinalidade de dados.
Antes do Repositório de Consultas, o SQL Server não forneceu informações sobre a causa das regressões e a identificação de falhas era um problema para desenvolvedores e administradores de banco de dados. Agora você pode usar o Repositório de Consultas para localizar consultas regredidas e forçar o otimizador a usar um plano específico do histórico.
É comum que um pequeno número de consultas, possivelmente entre milhares, consuma a maioria dos recursos do sistema. O Repositório de Consultas identifica quais consultas têm o consumo mais alto, seja devido à regressão ou a um ajuste inadequado. Dependendo da configuração, é possível filtrar os resultados por duração, CPU, memória, E/S ou número de execuções.
Você pode usar o Repositório de Consultas para monitorar o desempenho contínuo e para testes A/B para comparar o desempenho antes e depois de aplicar uma única alteração. Por exemplo, você pode ajustar o desempenho de uma consulta adicionando um índice a uma tabela a qual a consulta faz referência, de modo que a pesquisa de junção seja mais rápida. Comparar as estatísticas no Repositório de Consultas antes e depois de adicionar o índice informa se o índice afeta o desempenho. Você também pode comparar estatísticas depois de adicionar novo hardware ou atualizar um aplicativo.
Visão geral do Assistente de Ajuste de Consulta
O QTA (Assistente de Ajuste de Consulta) usa os dados do Repositório de Consultas para localizar consultas que estão começando a regredir. O QTA faz experimentos automaticamente para encontrar uma solução que acelerará a consulta, antes que seu desempenho seja prejudicado ao ponto de afetar os usuários.
Use o Repositório de Consultas e o QTA para monitorar e otimizar o desempenho do seu banco de dados após a atualização. Depois de migrar um banco de dados para o SQL Server 2016 ou superior, mantenha o nível de compatibilidade do banco de dados inalterado e habilite o Repositório de Consultas para coletar estatísticas de desempenho de consulta de linha de base.
Em seguida, altere o nível de compatibilidade e continue a usar os dados do Repositório de Consultas para medir as estatísticas de desempenho de suas consultas. Você pode comparar as estatísticas para descobrir se cada consulta está melhor, igual ou pior do que antes da atualização.
Quando o nível de compatibilidade é alterado para atualizar o banco de dados, o SQL Server altera a versão do avaliador de cardinalidade que ele usa. O QTA localiza possíveis padrões de regressão de consulta devido à alteração no avaliador de cardinalidade e faz experimentos para encontrar melhorias de desempenho. Você poderá criar guias de plano para as consultas que mostram melhoria.
Resumo
O Repositório de Consultas mede continuamente as estatísticas de desempenho das consultas, como uma caixa-preta captura os dados das atividades em um voo. Você pode habilitar o Repositório de Consultas em qualquer banco de dados no SQL Server 2016 ou superior, independentemente do nível de compatibilidade. Use o Repositório de Consultas para monitorar continuamente o desempenho de consultas e o teste A/B para medir os efeitos de uma única alteração.
Ao atualizar um banco de dados para um SQL Server 2014 ou posterior, as alterações no avaliador de cardinalidade podem tornar mais lentas as consultas que eram rápidas na versão anterior do SQL Server. Idealmente, você precisa encontrar e corrigir quaisquer regressões antes que elas afetem os usuários. A habilitação do Repositório de Consultas nos bancos de dados significa que as estatísticas são coletadas continuamente nas consultas. Em seguida, você poderá usar o QTA para identificar e corrigir consultas de regressão antes que elas se tornem um problema.