Ajustar consultas após a migração
Digamos que você seja um administrador de banco de dados de um escritório de advocacia. No fim de semana, você migrou alguns bancos de dados para o SQL Server 2019, incluindo o banco de dados do CRM (gerenciamento de relacionamentos com o cliente). Você está no escritório na segunda-feira de manhã, esperando que a equipe de vendas chegue dentro de algumas horas, fazendo e recebendo chamadas telefônicas de clientes. A equipe de vendas depende do banco de dados de CRM e, para que eles se envolvam com os clientes de maneira eficaz, o aplicativo precisa responder instantaneamente.
Depois de migrar os bancos de dados, você deixou o nível de compatibilidade na configuração anterior. Os aplicativos CRM são conectados ao novo servidor. Você deseja medir o desempenho das consultas no novo sistema em relação a uma carga de trabalho típica de segunda-feira, antes de alterar o nível de compatibilidade. Manter o desempenho das consultas é crucial ao atualizar bancos de dados.
Em vez de esperar que a equipe de vendas descubra que o aplicativo CRM está lento, você deseja que um processo meça automaticamente a carga de trabalho e o desempenho. Esse processo deve garantir que você possa corrigir as consultas lentas após alterar o nível de compatibilidade.
Repositório de Consultas
O recurso Repositório de Consultas foi lançado no SQL Server 2016 para reunir continuamente informações sobre suas consultas. O Repositório de Consultas coleta informações que você pode usar para solucionar problemas de consultas com desempenho ruim e funciona com instalações locais e na nuvem. Os planos de consulta em cache são armazenados no cache de procedimentos, mas o SQL Server só armazena o plano de execução mais recente.
É provável que os planos de execução de uma consulta sejam alterados ao longo do tempo devido a alterações de esquema ou à adição ou exclusão de índices. Além disso, as pressões na memória podem fazer com que os planos sejam removidos do cache de planos.
Por padrão, o Repositório de Consultas não está habilitado quando você cria ou migra um banco de dados para o SQL Server 2017 ou SQL Server 2019. No entanto, o recurso Repositório de Consultas pode ser habilitado antes de você atualizar o nível de compatibilidade do banco de dados no banco de dados migrado. Para habilitar o Repositório de Consultas, clique com o botão direito do mouse no banco de dados no Pesquisador de Objetos, selecione Propriedades e, em seguida, a página Repositório de Consultas. Na lista de seleção Modo de Operação (solicitado), escolha dentre Desativado, Somente Leitura ou Leitura/Gravação. Você também pode habilitar o Repositório de Consultas usando o Transact-SQL. Para habilitar o Repositório de Consultas para um banco de dados chamado CustomerServices, você executaria o seguinte comando:
ALTER DATABASE CustomerServices SET QUERY_STORE = ON
Quando uma consulta é compilada inicialmente, o texto da consulta e o primeiro plano são passados para o Repositório de Consultas. Se uma consulta for recompilada, o plano no Repositório de Consultas será atualizado. Após a criação de um plano, o Repositório de Consultas o adicionará e o salvará junto aos planos anteriores e às estatísticas de execução.
À medida que a consulta é executada, o Repositório de Consultas salva as estatísticas de runtime. Durante a compilação ou recompilação, o SQL Server verifica se existe um plano no Repositório de Consultas que deve ser aplicado à consulta. Quando um plano forçado difere do plano no cache de procedimento, a consulta é recompilada.
Até você alterar o nível de compatibilidade, o banco de dados não será exposto às alterações mais recentes do Otimizador de Consulta. Você pode coletar uma carga de trabalho típica e criar uma linha de base para medição, no nível de compatibilidade existente. Depois de coletar informações de consulta suficientes para ter a linha de base de desempenho, altere o nível de compatibilidade do banco de dados para a versão do servidor.
Para criar uma linha de base adequada, certifique-se de coletar dados suficientes de um período típico de atividade empresarial. Ao passar para o nível de compatibilidade do servidor atual, a carga de trabalho será exposta ao Otimizador de Consulta mais recente. O desempenho da consulta provavelmente melhorará, pois o novo Otimizador de Consulta deve produzir planos melhores.
Às vezes, o SQL Server usará um plano de consulta inferior ao ideal e, de repente, começará a ser executado lentamente e sem motivo claro. Esse problema pode ser corrigido forçando um plano anterior no Repositório de Consultas. O uso do Repositório de Consultas é semelhante ao uso da dica de consulta USAR PLANO, mas não requer nenhuma alteração nos aplicativos do usuário.
Depois de mover o nível de compatibilidade, o Repositório de Consultas continuará a fornecer suporte de consulta para manter o melhor desempenho. Ele mede e audita as Consultas Regredidas, o Consumo Geral de Recursos, as Consultas com Maior Consumo de Recursos, Consultas Com Planos Forçados, Consultas Com Variação Alta e Consultas Rastreadas.
Ajuste automático de consulta
A função do CE (Avaliador de Cardinalidade) no SQL Server é estimar o número de linhas retornadas em uma consulta. O Otimizador de Consulta usa o resultado do CE para gerar um plano de execução. Até o SQL Server 2014, o Avaliador de Cardinalidade teve poucas alterações. O lançamento do SQL Server 2014 trouxe uma revisão nos algoritmos do CE para melhorar as estimativas e fornecer os resultados mais rápidos de consultas. No entanto, o nível de compatibilidade do banco de dados determina qual CE será usado.
Embora o desempenho da consulta tenha melhorado muito do SQL Server 2014 em diante, um banco de dados migrado com um nível de compatibilidade atualizado pode sofrer degradação no desempenho. O Ajuste Automático de Consulta foi introduzido no SQL Server 2017. Use-o em conjunto com o Repositório de Consultas para localizar e corrigir automaticamente as consultas que estão regredindo. Depois de migrar seu banco de dados e capturar a carga de trabalho do banco de dados no nível de compatibilidade anterior, você atualizará o nível de compatibilidade e continuará executando o Repositório de Consultas.
O Ajuste Automático usa os dados do Repositório de Consultas para localizar consultas que estão começando a regressar devido a alterações no Avaliador de Cardinalidade após a mudança do nível de compatibilidade. O Ajuste Automático faz experimentos nas consultas que estão regredindo para aprimorar o desempenho, permitindo que você crie guias de plano em que o aprimoramento foi feito.
Como ajustar o desempenho de consulta no SQL Server 2019
No exemplo do escritório de advocacia, como você atualizou os servidores de banco de dados para o SQL Server 2019, você pode usar o Repositório de Consultas e o Ajuste Automático para otimizar o desempenho e garantir que a alteração no nível de compatibilidade não cause regressões. Recomendamos o seguinte processo:
- Atualize o servidor de banco de dados para SQL Server 2019, mas não altere o nível de compatibilidade.
- Habilite o Repositório de Consultas.
- Permita que o Repositório de Consultas colete dados de desempenho durante o horário comercial normal.
- Atualize o nível de compatibilidade do banco de dados.
- Verifique se o Repositório de Consultas encontrou regressões. Nesse caso, você pode forçar o último plano de consulta válido para retornar ao nível de desempenho mais alto.
O Repositório de Consultas automatiza o processo de monitoramento do desempenho de suas consultas. É uma ferramenta útil para ser executada em segundo plano para garantir que o banco de dados forneça resultados continuamente no tempo mais rápido possível. Combinado com o Repositório de Consultas, o QTA (Assistente de Ajuste de Consulta) aproveita os dados do Repositório de Consultas para localizar consultas regredidas em seus bancos de dados atualizados.
Depois de migrar um banco de dados e atualizar o nível de compatibilidade para SQL Server 2014 ou posterior, o novo Avaliador de Cardinalidade será usado para estimar o número de linhas retornadas por uma consulta. Use o QTA para localizar e corrigir consultas que retornam com a mudança para o CE mais recente.