Introdução ao Assistente de Otimização de Consultas (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, você deseja inicialmente deixar os níveis de compatibilidade nas configurações originais e reapontar os aplicativos. O estimador 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 SQL Server 2022 (160).
Você precisa localizar e corrigir quaisquer consultas que regridam quando você move o nível de compatibilidade e aplica o novo algoritmo do estimador de cardinalidade. O uso dessa abordagem mede uma linha de base para o desempenho a ser comparado depois de atualizar o nível de compatibilidade.
A habilitação do Repositório de Consultas reúne as métricas necessárias para encontrar consultas regressivas e, o uso do QTA (Assistente de Otimização de Consultas) o orienta na correção das consultas que regressam. Esta unidade fornece uma visão geral do Repositório de Consultas e do QTA.
Descrição geral do Arquivo de Consultas
A funcionalidade Arquivo de Consultas foi introduzida no SQL Server 2016 para recolher continuamente informações sobre a execução e o desempenho de consultas numa base de dados. O Query Store funciona como um gravador de dados de voo para reunir informações de tempo de execução sobre consultas e planos. Se guardar estes dados do runtime, pode controlar o desempenho ao longo do tempo. Se algo der errado, há um histórico de informações para descobrir a causa do problema.
A partir do SQL Server 2022 e no Banco de Dados SQL do Azure e na Instância Gerenciada do SQL, o Repositório de Consultas é 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 trabalhar 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 deixar 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 outras melhorias automáticas de desempenho sã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 SQL Server mais recente. O Query Store e o QTA podem ajudar com esse 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 em Regressão
- Consumo Global do Recurso
- Consultas Principais de Consumo de Recursos
- Consultas com planos forçados
- Consultas com alta variação
- Estatísticas de espera de consulta
- Consultas Controladas
Uma consulta regressão ocorre quando o otimizador de consulta usa um novo plano de consulta que faz com que o desempenho se degrade. A regressão pode acontecer após alterações importantes, como adicionar, descartar ou alterar um índice, atualizar estatísticas ou alterar a cardinalidade dos dados.
Antes do Repositório de Consultas, o SQL Server não fornecia informações sobre a causa das regressões, e a identificação de problemas era um problema para os 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 algumas das milhares de consultas consumam a maior parte dos recursos do sistema. O Repositório de Consultas identifica quais consultas têm o maior consumo, seja por causa da regressão ou devido ao ajuste insatisfatório. Dependendo da configuração, você pode 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 à qual a consulta faz referência, para 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 Consultas
O Assistente de Otimização de Consultas (QTA) usa dados do Repositório de Consultas para localizar consultas que estão começando a regredir. O QTA experimenta automaticamente para encontrar uma solução que acelere a consulta, antes que a consulta tenha um desempenho inferior ao ponto de afetar os usuários.
Você pode usar o Repositório de Consultas e o QTA para monitorar e otimizar o desempenho do banco de dados após a atualização. Depois de migrar um banco de dados para o SQL Server 2016 ou superior, você deixa o nível de compatibilidade do banco de dados inalterado e habilita 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á indo melhor, da mesma forma ou pior do que antes da atualização.
Quando você altera o nível de compatibilidade para atualizar o banco de dados, o SQL Server altera a versão do estimador de cardinalidade que ele usa. O QTA encontra possíveis padrões de regressão de consulta devido à mudança no estimador de cardinalidade e experimenta para encontrar melhorias de desempenho. Em seguida, você pode criar guias de plano para consultas que mostram melhorias.
Resumo
O Query Store mede continuamente as estatísticas de desempenho de suas consultas, assim como o gravador de dados de voo de uma aeronave captura a atividade. 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 da consulta e para testes A/B para medir os efeitos de uma única alteração.
Quando você atualiza um banco de dados para o SQL Server 2014 ou superior, as alterações no estimador de cardinalidade podem tornar lentas as consultas que eram rápidas na versão anterior do SQL Server. Idealmente, você deseja localizar e corrigir quaisquer regressões antes que elas afetem os usuários. A ativação do Arquivo de Consultas nas suas bases de dados significa que as estatísticas estão a ser continuamente recolhidas nas consultas. Em seguida, você pode usar o QTA para identificar e corrigir consultas regressivas antes que elas se tornem um problema.