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

No SQL Server 2014 (12.x) e versões posteriores, 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 COMPATIBILITY_LEVEL opção de banco de dados 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 Estimativa de cardinalidade (SQL Server). 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 seleçã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 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 atualizações foi aprimorado com o SQL Server 2017 (14.x), onde 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 Otimização de Consulta) orienta os usuários pelo fluxo de trabalho recomendado para manter a estabilidade de desempenho durante atualizações para versões mais recentes do SQL Server, conforme documentado na seção Manter estabilidade de desempenho durante a atualização para o SQL Server mais recente 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 rastreia quaisquer regressões encontradas na vista Consultas Regressadas do Repositório de Consultas e percorre possíveis permutações de variações do modelo do otimizador aplicável de forma a produzir um novo e melhor plano.

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 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 que o nível de compatibilidade do banco de dados anterior à atualização não seja alterado imediatamente. O QTA guia-o através das seguintes etapas:

  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 o início da carga de trabalho necessária, para que o Repositório de Consultas possa recolher uma linha de base dos dados da carga de trabalho, caso ainda não estejam disponíveis.

  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, coletando estatísticas de tempo de execução sobre possíveis permutações das variações de modelos otimizadores aplicáveis, e avalie o resultado.

  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.

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 válido, 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.

Diagrama do fluxo de trabalho de atualização de banco de dados recomendado usando QTA.

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.

O QTA tem como alvo possíveis padrões conhecidos de regressões de consulta devido a alterações nas versões do SQL Server (Cardinality Estimation ). 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 existente 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:

  • 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 seletividade mínima ao estimar AND predicados para filtros para levar em conta a correlação. Para obter mais informações, consulte USE HINT query hints e versões do CE.

  • Contenção simples vs. contenção de base: se uma contenção de junção diferente fornecer melhores estimativas para a consulta específica, a dica USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') de consulta 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 USE HINT query hints e 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).

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

      Captura de tela da janela de configuraçã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 rádio na coluna Atual aceita as configurações atuais e também permite ajustar mais precisamente a configuração atual do Repositório de Consultas.

      • A configuração proposta do Limite de Consulta Obsoleta é o dobro do valor de duração esperado da carga de trabalho, medido 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.

      Depois de concluir, 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 de tempo curto. No entanto, pode ser insuficiente manter informações sobre as cargas de trabalho na linha de base e pós-atualização do banco de dados para cargas de trabalho intensivas; ou seja, 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.

    Captura de ecrã da janela de ajuste de atualização da nova base de dados.

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ões lista as sessões atuais e passadas do banco de dados em questão. 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.

    Captura de ecrã da página Gestão de Sessões QTA.

    Observação

    Excluir sessão exclui todos os dados armazenados para a sessão selecionada. No entanto, excluir uma sessão fechada não exclui nenhum guia de plano implantado anteriormente. Se for eliminada uma sessão que implantou guias de plano, não será possível usar o QTA para reverter. Em vez disso, procure guias de planos usando a tabela do sistema sys.plan_guides e elimine-os 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ões, mantendo a sessão ativa como está.

    Esta etapa tem três subetapas:

    1. Coleta de Dados da Linha de Base solicita ao utilizador que execute o ciclo representativo de carga de trabalho, para que o Repositório de Consultas possa coletar uma linha de base. Quando essa carga de trabalho for concluída, marque a Opção de execução concluída com a 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 é retomado da mesma etapa em que foi interrompido.

      Captura de tela do QTA Etapa 2, Subetapa 1.

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

      Screenshot do QTA Etapa 2 Subetapa 2 - Atualizar o nível de compatibilidade da base de dados.

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

      Captura de ecrã do QTA Etapa 2 Subetapa 2.

    3. A Coleta de Dados Observados solicita que o usuário execute o ciclo de carga de trabalho representativo novamente, para que o Repositório de Consultas possa coletar uma linha de base comparativa usada para procurar 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 o valor de Consultas a mostrar para limitar o número de consultas exibidas. A ordem da lista é afetada pelo Métrico (Duração ou CpuTime) e pela Agregação (A média é o padrão). Selecione também quantas Consultas mostrar. Quando essa carga de trabalho for concluída, marque a opção Concluído com a execução da carga de trabalho e selecione Avançar.

      Screenshot do QTA Etapa 2 Subetapa 3.

      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. A valor de consultas para mostrar passa a ser o âmbito das consultas que são elegíveis para experimentação. Depois que as consultas desejadas estiverem marcadas, selecione Próximo para iniciar a experimentação.

    As consultas com Tunable definido como Falso não podem ser selecionadas para experimentação.

    Importante

    Um prompt informa que, uma vez que o QTA passe para a fase de experimentação, não seja 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.

    Captura de ecrã do QTA Passo 3.

  5. Ver Resultados permite selecionar as consultas às quais aplicar a otimização proposta como um guia de planos.

    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.

    • % 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 da consulta.

    • Pode implantar: True ou False dependendo se a otimização de consulta proposta pode ser implementada como um guia de planos.

    Captura de ecrã do QTA Passo 4.

  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 de se é possível reverter a otimização de consulta implementada e remover o seu guia de plano.

    Captura de ecrã do Passo 5 do QTA.

    Se, posteriormente, houver necessidade de reverter uma otimização proposta, selecione a consulta relevante e selecione Reversão. 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.

    Captura de ecrã da QTA Etapa 5 - Reversão.

    Observação

    A exclusão de uma sessão fechada não exclui nenhum guia de plano implantado anteriormente. Se for eliminada uma sessão que implantou guias de plano, não será possível usar o QTA para reverter. Em vez disso, procure guias de planos usando a tabela do sistema sys.plan_guides e elimine-os manualmente usando sp_control_plan_guide.

Permissões

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

Conteúdo relacionado

  • Last updated on