Ajustar consultas com o Assistente de Ajuste de Consultas (QTA)

  • 5 minutos

Você decide usar o Repositório de Consultas para monitorar o desempenho do banco de dados antes da migração e comparar esses dados com o desempenho após a atualização. Você planeja usar o Assistente de Ajuste de Consulta (QTA) para localizar consultas regredidas e sugerir melhorias. Esta unidade descreve as etapas para usar o Repositório de Consultas e o QTA para manter o desempenho da consulta.

Como seus aplicativos de banco de dados são sistemas críticos que sustentam as operações do negócio, você precisa ter um plano de ação e um plano de contingência para a atualização. Atualize os principais bancos de dados de produção, um de cada vez. Determine um plano de teste para os bancos de dados atualizados para garantir que cada banco de dados esteja de volta à produção sem problemas antes de atualizar qualquer outro.

Você pode usar o Repositório de Consultas para monitorar continuamente o desempenho de consultas e para testes A/B para medir os efeitos de uma alteração, como uma atualização de banco de dados. O QTA fornece suporte automático após uma atualização para localizar e corrigir consultas regressadas com base nos dados capturados no Repositório de Consultas.

Para que o QTA funcione corretamente, você precisa aplicar as etapas a seguir na ordem.

Importante

Certifique-se de usar seu banco de dados de aplicativo de produção ou um com uma carga de trabalho de aplicativo que corresponda à carga de trabalho do banco de dados de produção, para que o Repositório de Consultas possa reunir métricas realistas nas consultas.

  1. Migre o banco de dados para o SQL Server 2022.
  2. Deixe o nível de compatibilidade inalterado na versão anterior do SQL Server.
  3. Habilite o Repositório de Consultas no banco de dados.
  4. Permita que o Repositório de Consultas reúna métricas de linha de base nas consultas com base na atividade realista suficiente do usuário.
  5. Atualize o nível de compatibilidade para o SQL Server 2022 (160).
  6. Novamente, permita que o Repositório de Consultas reúna dados sobre as consultas com base em atividade de usuário realista suficiente.
  7. Use o QTA para comparar o desempenho das consultas antes e depois da alteração do nível de compatibilidade do banco de dados. Se forem encontradas consultas regredidas, identifique as correções.

Migrar a base de dados

Quando estiver pronto para migrar para o SQL Server 2022, comece migrando seu banco de dados para a nova instância. Há várias maneiras de fazer essa migração. Por exemplo, pode utilizar uma cópia de segurança simples e restaurar, utilizar o espelhamento da base de dados ou utilizar o carregamento em massa. A escolha mais apropriada depende da configuração do seu ambiente atual e da versão do SQL Server da qual você está migrando. O Serviço de Migração de Dados do Azure (DMS) é uma boa solução, pois dá suporte a bancos de dados do SQL Server 2005 em diante.

Nota

O Azure DMS também dá suporte a migrações de banco de dados para a Instância Gerenciada SQL do Azure. Use a extensão de migração SQL do Azure para o Azure Data Studio para começar.

Deixar o nível de compatibilidade inalterado

Depois de migrar o banco de dados, deixe o nível de compatibilidade inalterado. Esta etapa é crítica, porque você deseja que a linha de base seja medida usando a configuração atual do banco de dados. Até que você mova o nível de compatibilidade para o SQL Server 2014 (120) ou superior, o SQL Server usa o estimador de cardinalidade herdado. O SQL Server 2014 introduziu um estimador de cardinalidade atualizado que beneficia a maioria das consultas, mas raramente pode ter um impacto negativo no desempenho.

Habilitar o repositório de consultas

Embora o nível de compatibilidade do banco de dados permaneça na versão anterior, você pode habilitar o Repositório de Consultas no banco de dados, porque o Repositório de Consultas é um recurso no nível do servidor. Para habilitar o Repositório de Consultas:

  1. No SQL Server Management Studio (SSMS), clique com o botão direito do mouse no banco de dados e selecione Propriedades.
  2. Na janela Propriedades do Banco de Dados, selecione Repositório de Consultas no painel esquerdo.
  3. Defina o Modo de Operação (Solicitado) como Somente leitura ou Gravação de leitura.
  4. Selecione OK.

Como alternativa, você pode executar a seguinte instrução para habilitar o Repositório de Consultas no modo padrão READ WRITE :

ALTER DATABASE <database-name> SET QUERY_STORE = ON

Permitir que o Repositório de Consultas reúna dados

Coloque seu banco de dados migrado de volta em produção e alterne todas as conexões de banco de dados de aplicativos ou relatórios. O banco de dados começa a receber consultas de aplicativos de produção. Permita que o Repositório de Consultas seja executado por tempo suficiente para reunir uma carga de trabalho realista no banco de dados.

O Repositório de Consultas deve capturar um ciclo típico de atividade comercial, incluindo horário comercial, processamento noturno, janelas de manutenção e outras atividades. Para muitas empresas, uma semana de atividade é suficiente, mas para algumas empresas este período pode ser mais curto ou mais longo.

Muitas empresas têm ciclos de negócios importantes e, portanto, atividades únicas, para processamento quinzenal de folha de pagamento ou fim de mês. Você deve estar ciente do tempo dos ciclos de negócios que seus bancos de dados experimentam. Para uma mercearia, os ciclos semanais de chegada e reposição de estoque cobrem a maior parte da atividade do banco de dados.

Você pode ver os dados coletados navegando pelas guias do Repositório de Consultas. Para ver as guias, no Pesquisador de Objetos do SSMS, expanda a árvore do banco de dados para exibir o Repositório de Consultas. Quando estiver satisfeito com a coleta de dados suficientes, você poderá agendar a atualização.

Atualizar o nível de compatibilidade

Antes de fazer qualquer alteração em um banco de dados, é uma boa prática fazer backup do banco de dados, fora do horário de trabalho, se possível. Depois de fazer o backup, atualize o nível de compatibilidade da seguinte maneira:

  1. Clique com o botão direito do mouse no banco de dados no Pesquisador de Objetos do SSMS e escolha Propriedades.
  2. Na janela Propriedades do Banco de Dados, selecione a guia Opções.
  3. Altere o nível de compatibilidade para SQL Server 2022 (160) e selecione OK.

Como alternativa, você pode executar a seguinte instrução:

ALTER DATABASE <database-name> SET COMPATIBILITY_LEVEL = 160

Permitir que o Repositório de Consultas continue a coletar dados

Depois que o banco de dados for atualizado e os aplicativos forem retomados, o Repositório de Consultas continuará a ser executado em segundo plano para coletar métricas para as consultas. As consultas agora estão expostas a possíveis problemas devido ao novo estimador de cardinalidade que o otimizador de consulta usa.

Continue a executar o Repositório de Consultas e permita que ele colete dados pela mesma duração que antes da atualização. No entanto, a regressão de consulta pode aparecer imediatamente para que você possa tomar medidas para corrigir quaisquer problemas de desempenho imediatamente.

Executar o Assistente de Ajuste de Consulta

Execute o QTA para resolver quaisquer consultas regressivas. Para configurar o QTA:

  1. Clique com o botão direito do mouse no banco de dados no Pesquisador de Objetos do SSMS e selecione Tarefas>Atualização do Banco de Dados Nova>Sessão de Atualização do Banco de Dados.
  2. Na tela Configuração do Assistente de Otimização de Consulta, insira a Duração da carga de trabalho (dias) a ser capturada e o Nível de compatibilidade do banco de dados de destino.
  3. Selecione Avançar para configurar as telas Configurações e Ajuste .
  4. Selecione Concluir.

Para monitorar o QTA, clique com o botão direito do mouse no nome do banco de dados, selecione Sessões do Monitor de Atualização>do Banco de Dados de Tarefas.> O QTA fornece um relatório de resumo das principais consultas em regressão, comparando os dados observados aos dados de linha de base. Em seguida, você pode visualizar as alterações recomendadas pela QTA para ajustar as consultas em que o desempenho se degradou.

Resumo

Use o QTA após a atualização do banco de dados para localizar e corrigir consultas que regridem como resultado da atualização. Para que o QTA localize consultas em regressão, primeiro, tem de criar uma linha de base com o Arquivo de Consultas para medir as consultas no nível de compatibilidade anterior.

Em seguida, o Repositório de Consultas reúne métricas após a atualização, que você pode usar com o QTA para comparar o novo desempenho com a linha de base. É crucial para a operação QTA que o Query Store reúna dados antes e depois da atualização.

Quando o QTA identifica consultas regredidas, ele experimenta encontrar as melhores ações para melhorar o desempenho. Em seguida, você pode aplicar essas ações.