Atualizar bancos de dados usando o Assistente de Ajuste de Consulta

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

Ao migrar de uma versão mais antiga 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 pode estar exposta ao risco de regressão de desempenho. Isso também é possível em menor grau ao atualizar entre o SQL Server 2014 (12.x) e 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 limitadas ao nível de compatibilidade de banco de dados mais recente, portanto, os planos de execução não são alterados no momento da atualização, mas sim quando um usuário altera a opção de banco de dados COMPATIBILITY_LEVEL para a 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 Cardinality Estimator. Para obter mais informações sobre os níveis de compatibilidade e como eles podem afetar as atualizações, consulte Níveis de compatibilidade e atualizações do mecanismo de banco de dados.

Esse recurso de regulação fornecido pelo nível de compatibilidade do banco de dados, em combinação com o Repositório de Consultas, oferece um ótimo nível de controle sobre o desempenho da consulta no processo de atualização, se a atualização seguir o fluxo de trabalho recomendado visto abaixo. Para obter mais informações sobre o fluxo de trabalho recomendado para atualizar o nível de compatibilidade, consulte alterar o modo de compatibilidade do banco de dados e usar o repositório de consultas.

Esse controle sobre atualizações foi aprimorado com o SQL Server 2017 (14.x), onde de 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 de do Assistente de Otimização de Consulta (QTA) do guiará os usuários pelo fluxo de trabalho recomendado para manter a estabilidade de desempenho durante as atualizações para versões mais recentes do SQL Server, conforme documentado na seção Manter a estabilidade de desempenho durante a atualização para mais recente do SQL Server de Cenários de Uso do Repositório de Consultas. No entanto, o QTA não reverte para um bom plano previamente conhecido, como visto na última etapa do fluxo de trabalho recomendado. Em vez disso, o QTA rastreará quaisquer regressões encontradas na visualização Query Store Regressed Queries e iterará através de possíveis permutações de variações do modelo do otimizador aplicável para que um novo plano melhor possa ser produzido.

Importante

O QTA não gera carga de trabalho do usuário. Se estiver executando o QTA em um ambiente que não é usado por seus aplicativos, certifique-se de que você ainda possa executar a 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 (através de CREATE DATABASE ... PARA ANEXAR ou RESTAURAR) a 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 orientará através dos seguintes passos:

1. Configure o Repositório de Consultas de acordo com as configurações recomendadas para a duração da carga de trabalho (em dias) definida pelo usuário. Pense na duração da carga de trabalho que corresponde ao seu ciclo de negócios típico.
2. Solicite para iniciar a carga de trabalho necessária, para que o Repositório de Consultas possa coletar uma linha de base de dados da carga de trabalho (se ainda não estiver 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 passagem de dados de carga de trabalho seja coletada para comparação e deteção de regressão.
5. Percorra quaisquer regressões encontradas com base na visualização de Query Store Regressed Queries; experimente coletar estatísticas de tempo de execução sobre possíveis permutações das variações de modelos otimizadores aplicáveis e avalie os resultados.
6. Informe sobre 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, consulte Anexar e desanexar banco de dados.

Veja abaixo 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 acima. Em vez de ter a opção de escolher entre o plano de execução atualmente ineficiente e o último plano de execução em boas condições, o QTA apresenta opções de ajuste que são específicas para as consultas regredidas selecionadas, para criar um novo estado melhorado com planos de execução ajustados.

QTA Otimizando o espaço de pesquisa interno

O QTA visa apenas consultas SELECT que podem ser executadas pelo Repositório de Consultas. As consultas parametrizadas são elegíveis se o parâmetro compilado for conhecido. As consultas que dependem de construções de tempo de execução, como tabelas temporárias ou variáveis de tabela, não são qualificadas no momento.

QTA tem como alvo padrões conhecidos e possíveis de regressões de consulta devido a alterações nas versões do Estimador de Cardinalidade (CE) . 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 funcionar com a versão do CE que existia no SQL Server 2012 (11.x) (CE 70). Isso não significa que reverter do CE 140 para o CE 70 seja a única opção. Se apenas uma alteração específica na versão mais recente estiver introduzindo a regressão, então é possível sugerir que a consulta use apenas a parte relevante da versão anterior do CE que estava funcionando melhor para a consulta específica, enquanto ainda usa todas as outras melhorias das versões mais recentes do CE. E também permitir que outras consultas que não regrediram na carga de trabalho se beneficiem das mais recentes melhorias do CE.

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

• Independência versusde correlação: Se a suposição de independência fornecer melhores estimativas para a consulta específica, a dica de consulta USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') fará com que o SQL Server gere um plano de execução usando seletividade mínima ao estimar AND predicados para filtros para levar em conta a correlação. Para obter mais informações, consulte dicas de consulta USE HINT e versões doCE.
• Contenção Simples vs. Contenção Base: Se uma contenção de junção diferente fornecer melhores estimativas 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 suposição de Contenção Simples em vez da suposição de Contenção de Base padrão. Para obter mais informações, consulte as dicas de consulta USE HINT e as versões do CE.
• Estimativa de cardinalidade fixa da função com valor de tabela de múltiplas instruções (FVMT) de 100 linhas vs. 1 linha: Se a estimativa fixa padrão para FVMTs de 100 linhas não resultar em um plano mais eficiente do que ao usar a estimativa fixa para FVMTs de 1 linha (correspondente ao padrão no modelo CE do otimizador de consultas do SQL Server 2008 R2 (10.50.x) e versões anteriores), nesse caso, a dica de consulta QUERYTRACEON 9488 é usada para gerar um plano de execução. Para obter mais informações sobre MSTVFs, consulte Criar funções definidas pelo usuário (Mecanismo de Banco de Dados).

Observação

Como último recurso, se as dicas de escopo estreito não estiverem produzindo resultados bons o suficiente para os padrões de consulta qualificados, o uso completo do CE 70 também será considerado, 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 abordados em futuras atualizações do SQL Server. Recomendamos que aplique sugestões apenas quando não existir outra opção e planeie revisitar o código com sugestões em cada nova atualização. Ao forçar comportamentos, você pode estar impedindo que sua carga de trabalho se beneficie dos aprimoramentos introduzidos em versões mais recentes do SQL Server.

Iniciar o Assistente de Ajuste de Consulta para atualizações de banco de dados

QTA é um recurso baseado em sessão que armazena o estado da sessão no esquema msqta do banco de dados do usuário onde uma sessão é criada pela primeira vez. Várias sessões de ajuste podem ser criadas em um único banco de dados ao longo do tempo, mas apenas uma sessão ativa pode existir para qualquer 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 que pretende atualizar o nível de compatibilidade, clique com o botão direito do rato no nome do banco de dados, selecione Tarefas, selecione Atualização de Banco de Dadose selecione Nova Sessão de Atualização de Banco de Dados.

3. Na janela do Assistente de QTA, duas etapas são necessárias para configurar uma sessão:

   1. Na janela de Configuração, configure o Repositório de Consultas para capturar o equivalente a um ciclo comercial completo de dados de carga de trabalho para analisar e ajustar.

      • Insira a duração esperada da carga de trabalho em dias (o mínimo é de 1 dia). Isso será usado para propor configurações recomendadas do Repositório de Consultas para permitir provisoriamente que toda a linha de base seja coletada. A captura de uma boa linha de base é importante para garantir que todas as consultas regredidas encontradas após a 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 no qual o banco de dados do usuário deve estar, após a conclusão do fluxo de trabalho QTA. Depois de concluir, 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 da coluna Atual aceita as configurações atuais e também permite ajustar com precisão a configuração atual do Repositório de Consultas.
      • A configuração proposta de Limite de Consulta Obsoleta é o dobro do número de dias de duração prevista da carga de trabalho. Isso ocorre porque o Repositório de Consultas precisará armazenar informações sobre a carga de trabalho de linha de base e a carga de trabalho de atualização pós-banco de dados. Depois de concluir, selecione Avançar.

      

      Importante

      O valor arbitrário proposto para o Tamanho Máximo pode ser adequado para uma carga de trabalho de curta duração. No entanto, tenha em mente que pode ser insuficiente reter 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, nomeadamente quando muitos planos diferentes podem ser gerados. Se você antecipar que esse será o caso, insira um valor mais alto que seja apropriado.

4. A janela Tuning conclui a configuração da sessão e fornece instruções sobre as próximas etapas para abrir e prosseguir com a sessão. Depois de concluir, selecione Concluir.

   

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

1. Para a base de dados que se destina a atualizar o nível de compatibilidade da base de dados, clique com o botão direito sobre o nome da base de dados, selecione Tarefas, selecione Atualização da Base de Dados, e selecione Monitorizar Sessões.

2. A página de gestão de sessão lista as sessões atuais e passadas do banco de dados em escopo. Selecione 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 de sessão
   • Nome da Sessão: Nome gerado pelo sistema composto pelo nome do banco de dados, data e hora da criação da sessão.
   • Status: Status da sessão (Ativa ou Fechada).
   • Descrição: Gerado pelo sistema, composto pelo nível de compatibilidade do banco de dados de destino selecionado pelo utilizador e pelo número de dias para a carga de trabalho do ciclo de negócios.
   • Hora de início: Data e hora de quando a sessão foi criada.

   

   Observação

   Excluir sessão exclui todos os dados armazenados para a sessão selecionada. No entanto, excluir uma sessão fechada excluir nenhum guia de plano implantado anteriormente. Se você excluir uma sessão que tenha implantado guias de plano, não poderá usar o QTA para reverter. Em vez disso, procure guias de planos usando a tabela sistema sys.plan_guides e elimine 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 gestão de sessão , saindo da sessão ativa as-is.

   Esta etapa tem três subetapas:

   1. de Coleta de Dados da 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 a carga de trabalho estiver concluída, verifique Concluído com a execução da carga de trabalho e selecione Avançar.

      Observação

      A janela QTA pode ser fechada enquanto a carga de trabalho é executada. O retorno à sessão que permanece em estado ativo em um momento posterior será retomado a partir da mesma etapa em que foi interrompido.

      

   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. Coleta de Dados Observados pede ao utilizador para executar novamente o ciclo de carga de trabalho representativo, de modo que o Query Store possa coletar uma linha de base comparativa que será usada para encontrar oportunidades de otimização. À medida que a carga de trabalho é executada, use o botão Atualizar para continuar atualizando a lista de consultas regredidas, se alguma for encontrada. Altere as Consultas para mostrar o valor de modo a limitar o número de consultas exibidas. A ordem da lista é afetada pelo Métrico (Duração ou CpuTime) e pelo de Agregação (Média é padrão). Selecione também quantas as consultas devem mostrar. Quando essa carga de trabalho for concluída, verifique a Concluído com a execução da carga de trabalho e selecione Seguinte.

      

      A lista contém as seguintes informações:

      • ID de consulta
      • Texto de consulta: Transact-SQL instrução que pode ser expandida selecionando o botão ....
      • Executa: Exibe o número de execuções dessa consulta para toda a coleção de carga de trabalho.
      • Métrica da Linha de Base: A métrica selecionada (duração ou tempo de CPU) em ms para a coleta de dados da linha de base antes da atualização de compatibilidade da base de dados.
      • Métrica Observada: A métrica selecionada (Duration ou CpuTime) em milissegundos para a recolha de dados após a atualização de compatibilidade da base de dados.
      • % Alterar: Alteração percentual da métrica selecionada entre os estados de compatibilidade do banco de dados antes e depois da atualização. Um número negativo representa a quantidade de regressão medida para a consulta.
      • Ajustável: Verdadeiro ou Falso dependendo da elegibilidade da consulta para experimentação.

4. O View Analysis permite selecionar quais consultas experienciar para encontrar oportunidades de otimização. As consultas para mostrar valor de tornam-se o âmbito das consultas elegíveis para experimentar. Depois que as consultas desejadas estiverem marcadas, selecione Próximo para iniciar a experimentação.

   Observação

   Consultas onde Ajustável é Falso não podem ser selecionadas para experiências.

   Importante

   Um prompt informa que, uma vez que o QTA passe para a fase de experimentação, não será possível retornar à página View Analysis.
   Se você não selecionar todas as consultas qualificadas antes de passar para a fase de experimentação, precisará criar uma nova sessão posteriormente e repetir o fluxo de trabalho. Isso requer a redefinição do nível de compatibilidade do banco de dados para o valor anterior.

   

5. Ver Resultados permite selecionar quais consultas aplicar a otimização proposta como orientação para o plano.

   A lista contém as seguintes informações:

   • ID de consulta
   • Texto de consulta: Transact-SQL instrução que pode ser expandida selecionando o botão ....
   • Status: Exibe o estado de experimentação atual da consulta.
   • Métrica da Linha de Base: A métrica selecionada (Duração ou CpuTime) em ms para a consulta conforme executada na Etapa 2 Subetapa 3, representando a consulta regressada após a atualização de compatibilidade do banco de dados.
   • Métrica Observada: A métrica selecionada (Duration ou CpuTime) em ms para a consulta após a experimentação, para uma otimização proposta suficientemente boa.
   • % Change: Variação percentual da 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 da consulta.
   • Pode implantar: True ou False dependendo se a otimização de consulta proposta pode ser implantada como um guia de plano.

   

6. Verificação mostra o estado de implementação de consultas previamente selecionadas para esta sessão. A lista nesta página difere da página anterior ao alterar a coluna Can Deploy para Can Rollback. Esta coluna pode ser True ou False dependendo se a otimização de consulta implantada pode ser revertida e seu guia de plano removido.

   

   Se, posteriormente, houver necessidade de reverter uma otimização proposta, selecione a consulta relevante e selecione Reverter. O guia de plano de consulta é removido e a lista é atualizada para excluir a consulta que foi 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 implantado anteriormente. Se eliminar uma sessão que tenha implementado guias de plano, não poderá utilizar o QTA para reverter o processo. Em vez disso, procure guias de plano usando a tabela do sistema sys.plan_guides e elimine manualmente usando sp_control_plan_guide.

Permissões

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

Ver também

• Níveis de Compatibilidade e Upgrades do Mecanismo de Banco de Dados
• Ferramentas de monitoramento e ajuste de desempenho
• Monitorando o desempenho usando o repositório de consultas
• alterar o modo de compatibilidade de banco de dados e usar o repositório de consultas
• Sinalizadores de rastreamento
• USE dicas de consulta HINT
• Estimador de cardinalidade
• Ajuste automático
• Usar o Assistente de Otimização de Consulta do SQL Server