Compartilhar via

Atualizar bancos de dados usando o assistente de ajuste de consulta

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores

Ao migrar de uma versão mais antiga do SQL Server para o SQL Server 2014 (12.x) ou versões posteriores e atualizar o nível de compatibilidade do banco de dados para o mais recente disponível, uma carga de trabalho pode 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.

No SQL Server 2014 (12.x) e em versões posteriores, todas as alterações do otimizador de consulta são fechadas para o nível de compatibilidade mais recente do banco de dados, portanto, os planos de execução não são alterados diretamente no ponto de atualização, mas sim quando um usuário altera a opção de COMPATIBILITY_LEVEL banco de dados para o mais recente disponível. Para obter mais informações sobre as alterações do otimizador de consulta introduzidas no SQL Server 2014 (12.x), consulte a Estimativa de Cardinalidade (SQL Server). 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 de controle fornecida pelo nível de compatibilidade do banco de dados, em combinação com o Query Store, oferece um alto grau de controle sobre o desempenho de consultas no processo de atualização, se a atualização seguir o fluxo de trabalho recomendado no diagrama a seguir. Para obter mais informações sobre o fluxo de trabalho recomendado para atualizar o nível de compatibilidade, consulte Alterar o nível de compatibilidade do banco de dados e usar o Repositório de Consultas.

Diagrama do fluxo de trabalho de atualização de banco de dados recomendado usando o Repositório de Consultas.

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

A partir do SQL Server Management Studio v18, o recurso QTA (Assistente de Ajuste de Consulta) orienta os usuários por meio do fluxo de trabalho recomendado para manter a estabilidade do desempenho durante as atualizações para 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 rastreia quaisquer regressões encontradas na exibição Repositório de Consultas Consultas Regredidas e itera por meio de possíveis permutações de variações de modelos de otimizador aplicáveis para que um novo plano melhor possa ser produzido.

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 inicial do QTA pressupõe que um banco de dados de uma versão anterior do SQL Server seja movido (por meio de anexar um banco de dados ou instruções 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 antes da atualização não é alterado imediatamente. O QTA orienta as seguintes etapas:

  1. 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.

  2. 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).

  3. Atualize para o nível de compatibilidade do banco de dados de destino escolhido pelo usuário.

  4. Solicite que uma segunda leva de dados de carga de trabalho seja coletada para a detecção de regressão e de comparação.

  5. 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.

  6. 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.

O diagrama a seguir mostra como o QTA altera apenas as últimas etapas do fluxo de trabalho recomendado para atualizar o nível de compatibilidade usando o Repositório de Consultas visto anteriormente. Em vez de escolher entre o plano de execução atualmente ineficiente e o último plano de execução bom conhecido, o QTA apresenta opções de ajuste específicas para as consultas regredidas selecionadas, para criar um novo estado aprimorado com planos de execução ajustados.

Diagrama do fluxo de trabalho de atualização de banco de dados recomendado usando 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 tem como alvo padrões possíveis conhecidos de regressões de consulta devido a alterações nas versões de Estimativa de Cardinalidade (SQL Server ). Por exemplo, ao atualizar 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 trabalhar com a versão 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 na versão mais recente estiver introduzindo a regressão, será possível sugerir que a consulta use apenas a parte relevante da versão anterior da CE que estava funcionando melhor para a consulta específica, enquanto ainda usa todas as outras melhorias das versões mais recentes da 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 de CE pesquisados pelo QTA são:

  • Independência versus correlação: se a suposição de independência fornecer melhores estimativas para a consulta específica, a dica USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') de consulta fará com que o SQL Server gere um plano de execução usando a seletividade mínima ao estimar AND predicados para filtros que explicam a correlação. Para obter mais informações, confira Dicas de consulta USE HINT e Versões do CE.

  • Contenção simples versus contenção base: se uma contenção de junção diferente fornece melhores estimativas para a consulta específica, a dica USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') de consulta faz com que o SQL Server gere um plano de execução usando a suposição de Contenção Simples em vez da suposição de contenção 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).

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

Qualquer dica força determinados comportamentos que podem 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 estar impedindo sua carga de trabalho de se beneficiar de aprimoramentos introduzidos em 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

  1. No SQL Server Management Studio, abra o Pesquisador de Objetos e conecte-se ao Mecanismo de Banco de Dados.

  2. 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.

  3. 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 é usado para propor configurações recomendadas do Repositório de Consultas para permitir que toda a linha de base seja coletada provisoriamente. 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.

      Captura de tela da janela de instalação da nova sessão de atualização do banco de dados.

    2. Na janela Configurações , duas colunas mostram o estado atual do Repositório de Consultas no banco de dados de destino e 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 de Limite de Consulta Obsoleta proposta é o dobro do valor de duração da carga de trabalho esperado, em dias. Isso ocorre porque o Repositório de Consultas precisa conter informações sobre a carga de trabalho de linha de base e a carga de trabalho de atualização pós-banco de dados.

      Após a conclusão, selecione Avançar.

      Captura de tela da janela Novas configurações de atualização do banco de dados.

      Importante

      O Tamanho Máximo proposto é um valor arbitrário que pode ser adequado para uma carga de trabalho com pouco tempo. No entanto, pode ser insuficiente reter informações sobre as cargas de trabalho de linha de base e pós-atualização do banco de dados para cargas de trabalho intensivas, nomeadamente quando muitos planos diferentes podem ser gerados. Se você prever que esse será o caso, insira um valor mais alto apropriado.

  4. 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.

    Captura de tela da nova janela de ajuste de atualização do banco de dados.

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

  1. 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.

  2. 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.

    Captura de tela da página Gerenciamento de Sessão do QTA.

    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. Se você excluir uma sessão que implantou guias de plano, não poderá usar o QTA para reverter. Nesse caso, pesquise guias de plano usando a tabela de sistema sys.plan_guides e exclua manualmente usando sp_control_plan_guide.

  3. 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. Depois que essa carga de trabalho for concluída, marque a opção Concluir execução da carga de trabalho e selecione Avançar.

      Observação

      A janela do QTA pode ser fechada enquanto a carga de trabalho é executada. Retornar à sessão que permanece em estado ativo a um momento posterior retoma a partir da mesma etapa onde foi interrompida.

      Captura de tela da Etapa 2 do QTA Substep 1.

    2. Atualizar o banco de dados solicita 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.

      Captura de tela da Etapa 2 do QTA Substep 2 – Atualizar o nível de compatibilidade do banco de dados.

      A página a seguir confirma que o nível de compatibilidade do banco de dados foi atualizado com êxito.

      Captura de tela da Etapa 2 do QTA Substep 2.

    3. A Coleta de Dados Observada solicita que o usuário execute o ciclo de carga de trabalho representativa novamente, para que o Repositório de Consultas possa coletar uma linha de base comparativa 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. Depois que essa carga de trabalho for concluída, verifique o concluído com a execução da carga de trabalho e selecione Avançar.

      Captura de tela da Etapa 2 Subetapa 3 do QTA.

      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.

  4. 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.

    Consultas com Tunable definidas como False não podem ser selecionadas para experimentação.

    Importante

    Um prompt aconselha que, uma vez que QTA passe para a fase de experimentação, não será possível retornar à página de Análise de Visualização. 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.

    Captura de tela da Etapa 3 do QTA.

  5. 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.

    • % Alteração: especifica a alteração percentual para a métrica selecionada entre o estado de experimentação antes e depois, representando a quantidade de 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.

    Captura de tela da Etapa 4 do QTA.

  6. 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.

    Captura de tela da Etapa 5 do QTA.

    Se, em uma data posterior, houver a necessidade de reverter 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.

    Captura de tela da Etapa 5 do QTA – Reversão.

    Observação

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

Permissões

Requer a associação à função db_owner.

Conteúdo relacionado

  • Last updated on