Ajustar consultas com o Assistente de Ajuste de Consulta (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 encontrar 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 da empresa, 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 outros.

Você pode usar o Repositório de Consultas para monitorar continuamente o desempenho das 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 a fim de localizar e corrigir consultas regredidas 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

Use o banco de dados do aplicativo de produção ou um com uma carga de trabalho de aplicativo que corresponda de perto à carga de trabalho do banco de dados de produção, para que o Repositório de Consultas possa coletar métricas realistas nas consultas.

  1. Migre o banco de dados para o SQL Server 2022.
  2. Manter 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. Permitir que o Repositório de Consultas colete métricas de linha de base nas consultas com base em atividades de usuário suficientemente realistas.
  5. Atualize o nível de compatibilidade para o SQL Server 2022 (160).
  6. Novamente, permita que o Repositório de Consultas colete dados nas consultas com base em atividades de usuário suficientemente realistas.
  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 o banco 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, você pode usar um backup e uma restauração simples, usar um espelhamento de banco de dados ou um carregamento em massa. A escolha mais apropriada depende da configuração do seu ambiente atual e da versão do SQL Server do qual você está migrando. O Serviço de Migração de Banco de Dados do Azure (DMS) é uma boa solução, pois dá suporte a bancos de dados do SQL Server 2005 em diante.

Observação

O DMS do Azure também dá suporte a migrações de banco de dados para a Instância Gerenciada de SQL do Azure. Use a extensão de migração do 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, pois você deseja que a linha de base seja medida usando a configuração atual do banco de dados. Até mover o nível de compatibilidade para o SQL Server 2014 (120) ou superior, o SQL Server usará o avaliador de cardinalidade herdado. O SQL Server 2014 introduziu um avaliador 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 como na versão anterior, você pode habilitar o Repositório de Consultas no banco de dados, pois 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 e 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 colete dados

Coloque seu banco de dados migrado de volta à 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 coletar uma carga de trabalho realista no banco de dados.

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

Muitas empresas têm grandes ciclos de negócios e, portanto, atividades exclusivas para o processamento quinzenal de folha de pagamento ou de fim de mês. Você deve estar ciente do tempo dos ciclos de negócios dos seus bancos de dados. Para um supermercado, os ciclos semanais de chegada e reabastecimento de estoque abrangem a maioria das atividades de banco de dados.

Você pode ver os dados coletados navegando nas guias do Repositório de Consultas. Para ver as guias, no Pesquisador de Objetos do SSMS, expanda a árvore de banco de dados para exibir o Repositório de Consultas. Depois de estar satisfeito avaliando que dados suficientes foram coletados, 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 coletando dados

Depois que o banco de dados é atualizado e os aplicativos são retomados, o Repositório de Consultas continua sendo executado em segundo plano para coletar métricas para as consultas. As consultas agora são expostas a possíveis problemas devido ao novo avaliador 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 de regressão. 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 Ajuste de Consulta, insira a Duração da carga de trabalho (dias) para capturar e o Nível de compatibilidade do banco de dados de destino.
  3. Selecione Avançar para definir 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 Tarefas>Atualização do banco de dados>Monitorar sessões. O QTA fornece um relatório de resumo para as principais consultas regredidas, comparando os dados observados com os dados de linha de base. Em seguida, você pode exibir as alterações recomendadas pelo QTA para ajustar as consultas em que o desempenho foi degradado.

Resumo

Use o QTA após a atualização do banco de dados para localizar e corrigir consultas que regredim como resultado da atualização. Para que o QTA localize as consultas regredidas, primeiro é necessário criar uma linha de base usando o Repositório de Consultas para medir as consultas no nível de compatibilidade antigo.

Em seguida, o Repositório de Consultas coleta 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 de QTA que o Repositório de Consultas colete dados antes e depois da atualização.

Quando o QTA identifica consultas regredidas, ele tenta encontrar as melhores ações para melhorar o desempenho. Você pode aplicar essas ações.