Decida se o Assistente de Otimização de Consultas (QTA) é adequado para si
Você está ciente de possíveis regressões de plano de consulta e perda de desempenho após 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.
Introdução ao Repositório de Consultas e ao Assistente de Ajuste de Consultas
O QTA depende dos dados do Repositório de Consultas para localizar consultas que regridem após uma atualização. O Repositório de Consultas permite coletar métricas na versão mais antiga do banco de dados antes de atualizar.
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 executada em uma instância do SQL Server 2022 pode usar esses dois recursos. Essas ferramentas são integradas ao SQL Server Management Studio (SSMS) e operam no nível do banco de dados.
O nível de compatibilidade do banco de dados determina sua versão, que por sua vez determina a versão do estimador de cardinalidade que ele usa. O estimador de cardinalidade prevê quantas linhas uma consulta provavelmente retornará, para que o otimizador de consulta possa selecionar o plano de menor custo. O SQL Server 2014 introduziu um algoritmo de estimador de cardinalidade atualizado que beneficia a maioria das consultas, mas raramente pode ter um impacto negativo no desempenho.
Para medir o impacto no desempenho, o Repositório de Consultas relata consultas regredidas e consultas que consomem a maioria dos recursos do sistema. O QTA compara os dados de desempenho da consulta do Repositório de Consultas antes e depois da atualização do banco de dados e experimenta as consultas para melhorar o desempenho.
Nota
O QTA não está disponível para o Banco de Dados SQL do Azure ou bancos de dados da Instância Gerenciada SQL. Para esses bancos de dados, considere usar a extensão de migração SQL do Azure para o Azure Data Studio.
O QTA e a correção automática do plano
Quando o SQL Server executa uma consulta Transact-SQL (T-SQL), ele analisa possíveis planos que podem executar a consulta. O SQL Server armazena em cache planos para consultas que são executadas com êxito e as reutiliza quando as consultas são executadas novamente.
O SQL Server escolhe o plano ideal para uma consulta e o usa até que uma circunstância o force a escolher um novo. Essas circunstâncias podem incluir o mecanismo de banco de dados recompilando o plano, um índice sendo adicionado ou removido ou estatísticas sendo alteradas.
O novo plano não é sempre uma melhoria do antigo. Você pode executar o seguinte comando para procurar consultas que tenham um plano regredido.
SELECT * FROM sys.dm_db_tuning_recommendations
Em seguida, você pode usar o procedimento armazenado para forçar o sp_force_plan SQL Server a usar um plano específico recomendado.
EXEC sp_force_plan @query_id = 1187, @plan_id = 1975
O sp_force_plan procedimento é um processo manual que é potencialmente tedioso se muitas consultas estiverem regredindo em um banco de dados atualizado. O SQL Server 2017 introduziu um novo recurso chamado correção automática de plano para ajustar automaticamente as consultas e remover a necessidade de intervenção manual. Você pode habilitar a correção automática de plano em um banco de dados executando a seguinte instrução:
ALTER DATABASE <database-name> SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)
Quando definido em um nível de banco de dados, a correção automática de plano instrui o SQL Server a usar o último plano de consulta válido. O SQL Server continua monitorando o plano para encontrar regressões enquanto o plano está em execução e para garantir que ele ofereça o desempenho ideal.
A correção automática do plano funciona de forma diferente do QTA. Usar o último bom plano pode significar reverter para um estimador de cardinalidade anterior. Por outro lado, o QTA faz seus experimentos usando a versão do estimador de cardinalidade 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 operar. O QTA deve ter dados de linha de base do Repositório de Consultas para um banco de dados em seu nível de compatibilidade anterior, para que possa observar consultas e fazer comparações após uma atualização.
A correção automática de planos, introduzida no SQL Server 2017, elimina a necessidade de identificar e forçar manualmente um plano de consulta. A correção automática do plano pode ser habilitada no nível do banco de dados, mas pode reverter a versão do estimador de cardinalidade. O QTA usa a versão do estimador de cardinalidade mapeada para o nível de compatibilidade de destino.