Decida se o Assistente de Ajuste de Consultas (QTA) é ideal para você
Você está ciente de possíveis regressões e perda de desempenho do plano de consulta após as atualizações de versão do banco de dados. Para ajudar a manter o desempenho após as atualizações do banco de dados, você precisa encontrar o melhor método para identificar e mitigar consultas regredidas. Esta unidade descreve como você pode usar o Repositório de Consultas e o Assistente de Ajuste de Consultas (QTA) para ajudar a garantir que o desempenho reduzido não seja um problema após as atualizações.
Comece a usar o Repositório de Consultas e o Assistente de Ajuste de Consulta
O QTA depende dos dados do Repositório de Consultas para localizar consultas que apresentem regressão após uma atualização. O Repositório de Consultas permite que você colete métricas da versão mais antiga do banco de dados antes de atualizá-lo.
O Repositório de Consultas foi introduzido no SQL Server 2016 e o QTA foi introduzido no SQL Server 2017. Qualquer versão de banco de dados sendo executada em uma instância do SQL Server 2022 pode usar esses dois recursos. As duas ferramentas são integradas no SQL Server Management Studio (SSMS) e operam no nível do banco de dados.
O nível de compatibilidade do banco de dados determina a respectiva versão, que, por sua vez, determina a versão do avaliador de cardinalidade que ele usa. O avaliador de cardinalidade prevê quantas linhas uma consulta provavelmente retornará, de modo que o otimizador de consultas possa selecionar o plano de custo mais baixo. O SQL Server 2014 introduziu um algoritmo de avaliador de cardinalidade atualizado que beneficia a maioria das consultas, mas, em raras ocasiões, pode afetar o desempenho negativamente.
Para medir o impacto sobre o desempenho, o Repositório de Consultas reporta as consultas regredidas e as consultas que consomem a maioria dos recursos do sistema. O QTA compara os dados de desempenho de consultas do Repositório de Consultas antes e depois da atualização do banco de dados e faz experimentos nas consultas para aprimorar o desempenho.
Observação
O QTA não está disponível para o Banco de Dados SQL do Azure ou bancos de dados da Instância Gerenciada de SQL do Azure. Para esses bancos de dados, pense em usar a extensão de migração do SQL do Azure para o Azure Data Studio.
O QTA e a correção automática de planos
Quando executa uma consulta de Transact-SQL (T-SQL), o SQL Server analisa os possíveis planos que podem executar a consulta. O SQL Server armazena em cache os planos para consultas que são executadas com sucesso e os reutiliza quando as consultas são executadas novamente.
O SQL Server seleciona o plano ideal para a consulta e o utiliza até que uma circunstância o obrigue a selecionar uma nova opção. Essas circunstâncias podem incluir a recompilação do plano por um mecanismo de banco de dados, o acréscimo ou remoção de um índice ou alterações de estatísticas.
Um novo plano nem sempre é uma melhoria do antigo. Você pode executar o seguinte comando para pesquisar as consultas que têm um plano regredido.
SELECT * FROM sys.dm_db_tuning_recommendations
Em seguida, você pode usar o procedimento sp_force_plan armazenado para forçar o SQL Server a usar um plano específico recomendado.
EXEC sp_force_plan @query_id = 1187, @plan_id = 1975
O procedimento sp_force_plan é um processo manual que pode se tornar entediante se muitas consultas estiverem regredindo em um banco de dados atualizado. O SQL Server 2017 introduziu um novo recurso chamado correção de plano automática para ajustar as consultas automaticamente e remover a necessidade de intervenção manual. Você pode habilitar a correção de plano automática em um banco de dados executando a seguinte instrução:
ALTER DATABASE <database-name> SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)
Quando configurada em um nível de banco de dados, a correção de plano automática instrui o SQL Server a usar o último plano de consulta comprovadamente bom. O SQL Server continua monitorando o plano para encontrar regressões enquanto o plano estiver em execução e garantir que produza um desempenho ideal.
O funcionamento da correção de plano automática é diferente do QTA. Usar o último plano comprovadamente bom pode significar a reversão para um avaliador de cardinalidade anterior. Por outro lado, o QTA faz seus experimentos usando a versão do avaliador de cardinalidade que estiver mapeada para o nível de compatibilidade do banco de dados de destino.
Resumo
O QTA está disponível no SQL Server 2022 e depende do Repositório de Consultas para funcionar. O QTA precisa ter os dados de linha de base do Repositório de Consultas para um banco de dados no nível de compatibilidade anterior, de modo que possa observar as consultas e fazer comparações após uma atualização.
A correção de plano automática, introduzida no SQL Server 2017, remove a necessidade de identificar e forçar um plano de consulta manualmente. A correção de plano automática pode ser habilitada no nível do banco de dados, mas poderá reverter o avaliador de cardinalidade para uma versão anterior. O QTA usa a versão do avaliador de cardinalidade que estiver mapeada para o nível de compatibilidade pretendido.