Descrever ajuste automático

O ajuste automático é um recurso de monitoramento e análise que aprende continuamente sobre sua carga de trabalho e identifica possíveis problemas e melhorias.

As recomendações de ajuste automático são baseadas nos dados coletados de Repositório de Consultas. Os planos de execução evoluem ao longo do tempo devido a alterações de esquema, modificações de índice ou alterações nos dados que causam atualizações às estatísticas. Essa evolução pode fazer com que as consultas sejam executadas inadequadamente, pois o plano de execução não atende mais às demandas da consulta em questão.

Além disso, o ajuste automático permite a coleta e a aplicação de serviços de aprendizado de máquina em relação a métricas de desempenho para fornecer melhorias sugeridas ou até mesmo permitir a autocorreção.

Seja no local ou na nuvem, o ajuste automático permite identificar problemas causados pela regressão do plano de execução de consulta. Além disso, no Banco de Dados SQL do Azure, você pode melhorar o desempenho de consulta por ajuste de índice. O ajuste automático do Banco de Dados SQL do Azure pode identificar índices que devem ser adicionados ou até mesmo removidos do banco de dados para melhorar o desempenho da consulta.

Correção automática de plano

Com a ajuda dos dados do Repositório de Consultas, o mecanismo de banco de dados pode determinar quando os planos de execução de consulta regrediram no desempenho. Embora você possa identificar manualmente um plano regressivo por meio da interface do usuário, o Repositório de Consultas também fornece a opção de notificar você automaticamente.

No exemplo acima, você pode ver uma marca de seleção na ID do plano 1, o que significa que o plano foi forçado. Depois que o recurso estiver habilitado, o mecanismo de banco de dados forçará automaticamente qualquer plano de execução de consulta recomendado, quando:

• O plano anterior tiver uma taxa de erro maior do que o plano recomendado
• O ganho estimado da CPU foi maior que 10 segundos
• O plano de força tiver um desempenho melhor do que o anterior

O plano será revertido para o último plano bom conhecido depois de 15 execuções da consulta.

Quando a imposição de plano ocorrer automaticamente, o Mecanismo de Banco de Dados aplicará o último plano bom conhecido e monitorará o desempenho da execução da consulta. Se o plano forçado não tiver um desempenho melhor do que o anterior, ele se torna não forçado, e um novo plano é elaborado. No entanto, se o plano forçado continuar a superar o plano ruim anterior, ele permanecerá em vigor até ocorrer uma recompilação.

Você pode habilitar a correção automática do plano por meio de uma consulta T-SQL. O Repositório de Consultas deve estar habilitado e deve estar no modo de leitura/gravação para que o comando seja bem sucedido. Se um desses dois critérios não for atendido, a instrução ALTER falhará.

ALTER DATABASE [WideWorldImporters] SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

Você pode examinar as recomendações de ajuste automático por meio de uma DMV (exibição de gerenciamento dinâmico), sys.dm_db_tuning_recommendations, que está disponível no SQL Server 2017 ou superior e também em soluções de Banco de Dados SQL do Azure. Essa DMV oferece informações como, por exemplo, os motivos pelos quais a recomendação foi fornecida, o tipo de recomendação e o estado da recomendação. Para confirmar se o ajuste automático está habilitado para um banco de dados, verifique a exibição sys.database_automatic_tuning_options.

Gerenciamento automático de índice

O Banco de Dados SQL do Azure tem a capacidade de executar o ajuste automático de índice. Com o tempo, ele aprende com cargas de trabalho existentes e fornece recomendações para adicionar ou remover índices para melhorar o desempenho. Semelhante a forçar planos de consulta aprimorados, o banco de dados pode ser configurado para criar ou remover índices automaticamente com base em seu desempenho, conforme mostrado na imagem a seguir.

Quando habilitada, a página Recomendações de Desempenho identifica índices que podem ser criados ou descartados dependendo do desempenho da consulta. Lembre-se de que esse recurso não está disponível para bancos de dados locais e está disponível somente para o Banco de Dados SQL do Azure.

Como alternativa, use a seguinte consulta para ver os recursos de ajuste automático habilitados em seu banco de dados:

SELECT name,
    desired_state_desc,
    actual_state_desc,
    reason_desc
FROM sys.database_automatic_tuning_options

A criação de novos índices pode consumir recursos, e o tempo das criações de índice é essencial para garantir que não haja nenhum efeito negativo em suas cargas de trabalho.

O Banco de Dados SQL do Azure monitora os recursos necessários para implementar novos índices para evitar causar degradação de desempenho. A ação de ajuste será adiada até que os recursos disponíveis estejam disponíveis, por exemplo, se os recursos forem necessários para cargas de trabalho existentes e não estiverem disponíveis para a criação de um índice.

O monitoramento garante que qualquer ação executada não prejudique o desempenho. Se um índice for descartado e o desempenho da consulta estiver visivelmente degradado, o índice descartado recentemente será recriado automaticamente.