Atualizar bancos de dados usando o assistente de ajuste de consulta

Artigo
10/29/2023

Aplica-se a SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do Azure Azure Synapse Analytics PDW (Analytics Platform System)

Ao migrar de versões mais antigas do SQL Server para o SQL Server 2014 (12.x) ou mais recente e atualizar o nível de compatibilidade do banco de dados para o mais recente disponível, uma carga de trabalho poderá ser exposta ao risco de regressão de desempenho. Isso também é possível em um grau menor ao atualizar do SQL Server 2014 (12.x) para qualquer versão mais recente.

A partir do SQL Server 2014 (12.x), e a cada nova versão, todas as alterações do otimizador de consulta são restritas ao último nível de compatibilidade do banco de dados, então os planos de execução não são alterados diretamente no ponto de atualização, mas sim quando o usuário altera a opção do banco de dados COMPATIBILITY_LEVEL para a última versão disponível. Para obter mais informações sobre as alterações do otimizador de consulta introduzidas no SQL Server 2014 (12.x), confira Avaliador de cardinalidade. Para saber mais sobre os níveis de compatibilidade e como eles podem afetar as atualizações, confira Níveis de compatibilidade e atualizações do Mecanismo de Banco de Dados.

Essa funcionalidade associada fornecida pelo nível de compatibilidade do banco de dados, em combinação com o Repositório de Consultas oferece um excelente nível de controle sobre o desempenho da consulta no processo de atualização quando a atualização segue o fluxo de trabalho recomendado, mostrado abaixo. Para obter mais informações sobre o fluxo de trabalho recomendado para atualizar o nível de compatibilidade, confira Alterar o modo de compatibilidade do banco de dados e usar o Repositório de Consultas.

Recommended database upgrade workflow using Query Store

Esse controle sobre as atualizações foi aprimorado no SQL Server 2017 (14.x), em que o ajuste automático foi introduzido e permite automatizar a última etapa no fluxo de trabalho recomendado acima.

A partir do SQL Server Management Studio v18, o novo recurso QTA (assistente de ajuste de consulta) orientará os usuários pelo fluxo de trabalho recomendado para manter a estabilidade do desempenho durante as atualizações para as versões mais recentes do SQL Server, conforme documentado na seção Manter a estabilidade do desempenho durante a atualização para o SQL Server mais recente dos Cenários de uso do Repositório de Consultas. No entanto, o QTA não é revertido para um plano em bom estado anteriormente conhecido, como visto na última etapa do fluxo de trabalho recomendado. Em vez disso, o QTA vai rastrear todas as regressões encontradas na exibição Consultas Regredidas do Repositório de Consultas e iterar por meio de possíveis permutações das variações do modelo do otimizador aplicável para que um plano novo e melhor possa ser gerado.

Importante

O QTA não gera carga de trabalho do usuário. Ao executar o QTA em um ambiente que não seja usado pelos seus aplicativos, verifique se ainda é possível executar uma carga de trabalho de teste representativa no mecanismo de banco de dados do SQL Server de destino por outros meios.

O fluxo de trabalho do Assistente de Ajuste de Consulta

O ponto de partida do QTA pressupõe que um banco de dados de uma versão anterior do SQL Server é movido (por meio de CREATE DATABASE ... FOR ATTACH ou RESTORE) para uma versão mais recente do mecanismo de banco de dados do SQL Server, e o nível de compatibilidade do banco de dados anterior à atualização não é alterado imediatamente. O QTA orientará pelas etapas a seguir:

Configure o Repositório de Consultas de acordo com as configurações recomendadas de duração da carga de trabalho (em dias) definidas pelo usuário. Pense em uma duração de carga de trabalho que corresponda ao seu ciclo comercial típico.
Solicite o início da carga de trabalho necessária para que esse Repositório de Consultas possa coletar uma linha de base de dados de carga de trabalho (se ainda não houver nenhum disponível).
Atualize para o nível de compatibilidade do banco de dados de destino escolhido pelo usuário.
Solicite que uma segunda leva de dados de carga de trabalho seja coletada para a detecção de regressão e de comparação.
Itere por meio das regressões encontradas na exibição Consultas Regredidas do Repositório de Consultas, experimente a coleta de estatísticas de runtime sobre possíveis permutações de variações de modelo do otimizador aplicável e meça o resultado.
Relate as melhorias medidas e, opcionalmente, permita que essas alterações persistam usando guias de plano.

Para obter mais informações sobre como anexar um banco de dados, confira Anexar e desanexar bancos de dados.

Veja abaixo como o QTA apenas muda as últimas etapas do fluxo de trabalho recomendado para atualizar o nível de compatibilidade usando o Repositório de Consultas mostrado acima. Em vez de oferecer a opção de escolher entre o plano de execução ineficiente no momento e o último plano de execução eficiente conhecido, o QTA apresenta opções de ajuste específicas das consultas regredidas selecionadas para a criação de um novo estado aprimorado com planos de execução ajustados.

Recommended database upgrade workflow using QTA

Espaço de pesquisa interno do Ajuste do QTA

O QTA é indicado apenas para consultas SELECT que podem ser executadas pelo Repositório de Consultas. As consultas parametrizadas são qualificadas quando o parâmetro compilado é conhecido. As consultas que dependem de construções de runtime, como tabelas temporárias ou variáveis de tabela não são qualificadas no momento.

O QTA é indicado para possíveis padrões de regressões de consulta devido a alterações nas versões CE (Estimador de Cardinalidade). Por exemplo, durante a atualização de um banco de dados do SQL Server 2012 (11.x) e do nível de compatibilidade do banco de dados 110 para o SQL Server 2017 (14.x) e o nível de compatibilidade do banco de dados 140, algumas consultas podem regredir porque foram projetadas especificamente para funcionar com a versão do CE que existia no SQL Server 2012 (11.x) (CE 70). Isso não significa que a reversão do CE 140 para o CE 70 é a única opção. Se apenas uma alteração específica da versão mais recente estiver apresentando a regressão, será possível indicar essa consulta a usar apenas a parte relevante da versão anterior do CE que estava funcionando melhor para a consulta específica, continuando usando todas as outras melhorias das versões mais recentes do CE. E também permitir que outras consultas na carga de trabalho que não regrediram se beneficiem das melhorias mais recentes do CE.

Os padrões do CE pesquisados pelo QTA são os seguintes:

Independência e correlação: se a pressuposição de independência fornecer estimativas melhores para a consulta específica, a dica de consulta USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') fará com o SQL Server gerar um plano de execução usando a seletividade mínima ao estimar predicados AND para os filtros a serem considerados para correlação. Para obter mais informações, confira Dicas de consulta USE HINT e Versões do CE.
Independência simples e independência de base: se uma independência de junção diferente fornecer estimativas melhores para a consulta específica, a dica de consulta USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') fará com que o SQL Server gere um plano de execução usando a pressuposição de independência simples em vez da pressuposição de independência de base padrão. Para obter mais informações, confira Dicas de consulta USE HINT e Versões do CE.
Estimativa fixa de cardinalidade da MSTVF (função com valor de tabela multi-instrução) em 100 linhas versus 1 coluna: se o padrão fixo de estimativa de TVFs de 100 linhas não resultar em um plano mais eficiente do que o uso da estimativa fixa de TVFs de 1 linha (correspondente ao padrão do modelo da CE do otimizador de consulta do SQL Server 2008 R2 [10.50.x] e versões anteriores), a dica de consulta QUERYTRACEON 9488 será usada para gerar um plano de execução. Para obter mais informações sobre MSTVFs, confira Criar funções definidas pelo usuário (Mecanismo de Banco de Dados).

Observação

Como último recurso, se as dicas com escopo reduzido não estiverem rendendo bons resultados suficientes para os padrões de consulta elegíveis, também será considerado o uso completo do CE 70 usando a dica de consulta USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') para gerar um plano de execução.

Importante

Toda dica força determinados comportamentos que poderão ser resolvidos em atualizações futuras do SQL Server. É recomendado aplicar dicas apenas quando não houver nenhuma outra opção e planejar rever o código com dicas a cada nova atualização. Ao forçar comportamentos, você pode impedir que sua carga de trabalho se beneficie das melhorias introduzidas nas versões mais recentes do SQL Server.

Iniciar o assistente de ajuste de consulta para atualizações de banco de dados

O QTA é um recurso baseado em sessão que armazena o estado de sessão no esquema msqta do banco de dados de usuário no qual uma sessão é criada pela primeira vez. Várias sessões de ajuste podem ser criadas em um banco de dados individual ao longo do tempo, mas somente uma sessão ativa pode existir para um determinado banco de dados.

Criar uma sessão de atualização de banco de dados

No SQL Server Management Studio, abra o Pesquisador de Objetos e conecte-se ao mecanismo de banco de dados.
Para o banco de dados cujo nível de compatibilidade será atualizado, clique com o botão direito do mouse no nome do banco de dados, escolha Tarefas, Atualizar banco de dados e Nova sessão de atualização do banco de dados.
Na janela do Assistente QTA, são necessárias duas etapas para configurar uma sessão:
1. Na janela Configuração, configure o Repositório de Consultas para capturar o equivalente a um ciclo comercial completo de dados de carga de trabalho para análise e ajuste.
  - Digite a duração esperada da carga de trabalho em dias (o mínimo é 1 dia). Isso será usado para propor configurações recomendadas do Repositório de Consultas para tentar permitir que a linha de base inteira seja coletada. Capturar uma boa linha de base é importante para garantir que todas as consultas regredidas encontradas depois da alteração do nível de compatibilidade do banco de dados possam ser analisadas.
  - Defina o nível de compatibilidade do banco de dados de destino pretendido para o banco de dados de usuário, depois que o fluxo de trabalho do QTA for concluído. Após a conclusão, selecione Avançar.
2. Na janela Configurações, duas colunas mostram o estado Atual do Repositório de Consultas no banco de dados de destino, bem como as configurações Recomendadas.
  - As configurações Recomendadas são selecionadas por padrão, mas selecionar o botão de opção na coluna Atual aceita as configurações atuais e também permite ajustar melhor a configuração atual do Repositório de Consultas.
  - A configuração Limite de consulta obsoleto proposta é o dobro do número da duração da carga de trabalho esperada, em dias. Isso ocorre porque o Repositório de Consultas precisará manter as informações sobre a carga de trabalho de linha de base e a carga de trabalho após a atualização do banco de dados. Após a conclusão, selecione Avançar.
  Importante
  
  O Tamanho Máximo proposto é um valor arbitrário que pode ser adequado para uma carga de trabalho com tempo curto. No entanto, saiba que pode não ser suficiente manter informações sobre as cargas de trabalho de linha de base e após a atualização do banco de dados para cargas de trabalho muito intensivas, ou seja, quando muitos planos diferentes podem ser gerados. Se você puder prever que isso ocorrerá, insira um valor mais alto que seja apropriado.
A janela Ajuste conclui a configuração da sessão e instrui sobre as próximas etapas para abrir e prosseguir com a sessão. Após a conclusão, selecione Concluir.

Executar o fluxo de trabalho de atualização de banco de dados

Para o banco de dados cujo nível de compatibilidade do banco de dados será atualizado, clique com o botão direito do mouse no nome do banco de dados, escolha Tarefas, Atualizar banco de dados e Monitorar sessões.
A página gerenciamento de sessão lista as sessões atuais e antigas do banco de dados no escopo. Escolha a sessão desejada e selecione Detalhes.

Observação

Se a sessão atual não estiver presente, selecione o botão Atualizar.

A lista contém as seguintes informações:
- ID da Sessão
- Nome da sessão: nome gerado pelo sistema composto por nome de banco de dados, data e hora de criação da sessão.
- Status: status da sessão (Ativa ou Fechada).
- Descrição: gerada pelo sistema composta pelo nível de compatibilidade do banco de dados de destino selecionado pelo usuário e pelo número de dias da carga de trabalho do ciclo comercial.
- Hora de Início: data e hora de quando a sessão foi criada.
Observação

Excluir Sessão exclui todos os dados armazenados na sessão selecionada. No entanto, a exclusão de uma sessão fechada não exclui nenhum guia de plano já implantado. Ao excluir uma sessão que implantou guias de plano, você não poderá usar o QTA para reversão. Nesse caso, pesquise guias de plano usando a tabela de sistema sys.plan_guides e exclua manualmente usando sp_control_plan_guide.
O ponto de entrada para uma nova sessão é a etapa Coleta de Dados.

Observação

O botão Sessões retorna à página de gerenciamento de sessão, deixando a sessão ativa no estado em que se encontra.

Essa etapa tem três subetapas:
1. A Coleta de dados de linha de base solicita que o usuário execute o ciclo de carga de trabalho representativo para que o Repositório de Consultas possa coletar uma linha de base. Quando essa carga de trabalho for concluída, marque Execução de carga de trabalho concluída e selecione Avançar.
  
  Observação
  
  A janela do QTA pode ser fechada enquanto a carga de trabalho é executada. Quando você retornar mais tarde para a sessão que permanece no estado ativo, ela será retomada na mesma etapa em que parou.
2. Atualizar banco de dados solicitará permissão para atualizar o nível de compatibilidade do banco de dados para o destino desejado. Para prosseguir para a próxima subetapa, selecione Sim.
  
  A página a seguir confirma que o nível de compatibilidade do banco de dados foi atualizado com êxito.
3. A Coleta de Dados Observados solicita que o usuário execute o ciclo de carga de trabalho representativo mais uma vez para que o Repositório de Consultas possa coletar uma linha de base de comparação que será usada para pesquisar oportunidades de otimização. Conforme a carga de trabalho é executada, use o botão Atualizar para continuar atualizando a lista de consultas regredidas, caso alguma seja encontrada. Altere o valor de Consultas a serem mostradas para limitar o número de consultas exibidas. A ordem da lista é afetada pela Métrica (Duração ou CpuTime) e pela Agregação (Média é o padrão). Também selecione quantas Consultas a serem mostradas. Quando essa carga de trabalho for concluída, marque Execução de carga de trabalho concluída e selecione Avançar.
  
  A lista contém as seguintes informações:
  - ID da Consulta
  - Texto da consulta: instrução Transact-SQL que pode ser expandida selecionando o botão ....
  - Execuções: exibe o número de execuções dessa consulta para a coleção da carga de trabalho inteira.
  - Métrica de linha de base: a métrica selecionada (Duração ou CpuTime) em ms para a coleta de dados de linha de base antes da atualização de compatibilidade do banco de dados.
  - Métrica observada: a métrica selecionada (Duração ou CpuTime) em ms para a coleta de dados após a atualização de compatibilidade do banco de dados.
  - % de alteração: percentual de alteração da métrica selecionada entre o estado anterior e posterior de atualização de compatibilidade do banco de dados. Um número negativo representa o valor da regressão medida para a consulta.
  - Ajustável: True ou False dependendo da qualificação da consulta para experimentação.
Exibir análise permite selecionar quais consultas devem ser experimentadas e encontrar oportunidades de otimização. O valor Consultas a serem mostradas torna-se o escopo das consultas qualificadas para experimentação. Depois que as consultas desejadas forem verificadas, selecione Avançar para começar a experimentação.

Observação

As consultas com Ajustável = False não podem ser selecionadas para experimentação.

Importante

Um prompt avisa que depois que QTA mudar para a fase de experimentação, não será possível retornar para a página Exibir análise.
Se você não selecionar todas as consultas qualificadas antes de passar para a fase de experimentação, será necessário criar uma sessão mais tarde e repetir o fluxo de trabalho. Isso exige a reinicialização do nível de compatibilidade do banco de dados para o valor anterior.
Exibir descobertas permite selecionar as consultas para implantar a otimização proposta como um guia de plano.

A lista contém as seguintes informações:
- ID da Consulta
- Texto da consulta: instrução Transact-SQL que pode ser expandida selecionando o botão ....
- Status: exibe o estado atual de experimentação para a consulta.
- Métrica de linha de base: a métrica selecionada (Duração ou CpuTime) em ms para a consulta executada na Subetapa 3 da Etapa 2, que representa a consulta regredida após a atualização de compatibilidade do banco de dados.
- Métrica observada: a métrica selecionada (Duração ou CpuTime) em ms para a consulta após a experimentação, para uma otimização suficientemente boa.
- % de alteração: percentual de alteração da métrica selecionada entre o estado de experimentação anterior e posterior, que representa o valor da melhoria medida para a consulta com a otimização proposta.
- Opção de consulta: link para a dica proposta que melhora a métrica de execução de consulta.
- Pode implantar: True ou False, dependendo se a otimização de consulta proposta pode ser implantada como um guia de plano.
Verificação mostra o status de implantação das consultas já selecionadas para essa sessão. A lista nesta página diferencia-se da página anterior pela alteração da coluna Pode implantar para Pode reverter. Essa coluna pode ser True ou False dependendo se a otimização de consulta implantada pode ser revertida e seu guia de plano e removido.

Se em um momento posterior houver a necessidade de reverter para uma otimização proposta, selecione a consulta relevante e selecione Reverter. Esse guia de plano de consulta será removido e a lista será atualizada para remover a consulta revertida. Observe na imagem abaixo que a consulta 8 foi removida.

Observação

A exclusão de uma sessão fechada não exclui nenhum guia de plano já implantado. Ao excluir uma sessão que implantou guias de plano, você não poderá usar o QTA para reversão. Nesse caso, pesquise guias de plano usando a tabela de sistema sys.plan_guides e exclua manualmente usando sp_control_plan_guide.