Compartilhar via

Ajuste automático

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada do Banco de Dados SQL do Azurebanco de dados SQL no Microsoft Fabric

O ajuste automático é um recurso de banco de dados que fornece insights sobre possíveis problemas de desempenho de consulta, recomenda soluções e corrige automaticamente problemas identificados.

O ajuste automático, introduzido no SQL Server 2017 (14.x), notifica você sempre que um possível problema de desempenho é detectado e permite aplicar ações corretivas ou permite que o Mecanismo de Banco de Dados corrija automaticamente problemas de desempenho. O ajuste automático do SQL Server identifica e corrige problemas de desempenho causados por regressões de escolha do plano de execução de consulta. O ajuste automático no Banco de Dados SQL do Azure e no Banco de Dados SQL no Microsoft Fabric também cria índices necessários e descarta índices não utilizados. Para obter mais informações sobre planos de execução de consulta, consulte Planos de Execução.

O Mecanismo de Banco de Dados do SQL Server monitora as consultas executadas no banco de dados e melhora automaticamente o desempenho da carga de trabalho. O Mecanismo de Banco de Dados tem um mecanismo de inteligência interno que pode ajustar e melhorar automaticamente o desempenho de suas consultas adaptando dinamicamente o banco de dados à carga de trabalho. Há dois recursos de ajuste automático disponíveis:

  • A correção automática do plano identifica planos de execução de consulta problemáticos, como uma confidencialidade de parâmetro ou problemas de detecção de parâmetro , e corrige problemas de desempenho relacionados ao plano de execução de consulta forçando o último plano válido conhecido antes da regressão ocorrer. Aplica-se a: SQL Server (começando com SQL Server 2017 (14.x)), Banco de Dados SQL do Azure e Banco de Dados SQL no Microsoft Fabric e Instância Gerenciada de SQL do Azure

  • O gerenciamento automático de índice identifica índices que devem ser adicionados ao banco de dados e índices que devem ser removidos. Aplica-se a: Banco de Dados SQL do Azure e banco de dados SQL no Microsoft Fabric

Note

Neste artigo, os recursos e comportamentos do Banco de Dados SQL do Azure também se aplicam ao banco de dados SQL no Microsoft Fabric.

Por que ajuste automático?

Três das principais tarefas na administração clássica do banco de dados são monitorar a carga de trabalho, identificar consultas Transact-SQL críticas e identificar índices que devem ser adicionados para melhorar o desempenho ou índices que raramente são usados e podem ser removidos para melhorar o desempenho. O Mecanismo de Banco de Dados do SQL Server fornece informações detalhadas sobre as consultas e índices que você precisa monitorar. No entanto, monitorar constantemente um banco de dados é uma tarefa difícil e entediante, especialmente ao lidar com muitos bancos de dados. O gerenciamento de um grande número de bancos de dados pode ser impossível de fazer com eficiência. Em vez de monitorar e ajustar seu banco de dados manualmente, você pode considerar delegar algumas das ações de monitoramento e ajuste para o Mecanismo de Banco de Dados usando o recurso de ajuste automático.

Como funciona o ajuste automático?

O ajuste automático é um processo de monitoramento e análise contínuo que aprende constantemente sobre as características da carga de trabalho e identifica possíveis problemas e melhorias.

Processo de ajuste automático.

Esse processo permite que o banco de dados se adapte dinamicamente à carga de trabalho encontrando quais índices e planos podem melhorar o desempenho de suas cargas de trabalho e quais índices afetam suas cargas de trabalho. Com base nessas conclusões, o ajuste automático aplica ações de ajuste que aprimoram o desempenho da carga de trabalho. Além disso, o ajuste automático monitora continuamente o desempenho do banco de dados depois de implementar qualquer alteração para garantir que ela aprimore o desempenho da carga de trabalho. Qualquer ação que não melhora o desempenho é revertida automaticamente. Esse processo de verificação é um recurso importante que garante que qualquer alteração feita pelo ajuste automático não diminua o desempenho geral da carga de trabalho.

Correção automática de plano

A correção automática do plano é um recurso de ajuste automático que identifica a regressão de escolha do plano de execução e corrige automaticamente o problema forçando o último bom plano conhecido. Para obter mais informações sobre planos de execução de consulta e o Otimizador de Consulta, consulte o Guia de Arquitetura de Processamento de Consultas.

Important

A correção automática do plano depende da habilitação do Repositório de Consultas no banco de dados para acompanhamento de carga de trabalho.

O que é regressão de escolha do plano de execução?

O Mecanismo de Banco de Dados do SQL Server pode usar planos de execução diferentes para executar as consultas Transact-SQL. Os planos de consulta dependem das estatísticas, índices e outros fatores. O plano ideal que deve ser usado para executar uma consulta Transact-SQL pode mudar ao longo do tempo, dependendo das alterações nesses fatores. Em alguns casos, o novo plano pode não ser melhor do que o anterior e o novo plano pode causar uma regressão de desempenho, como uma sensibilidade de parâmetro ou um problema relacionado à detecção de parâmetros .

Regressão de escolha do plano de execução de consulta.

Sempre que você observar que ocorreu uma regressão de escolha de plano, você deverá encontrar um bom plano anterior e forçá-lo a ser usado em vez do atual. Isso pode ser feito usando o sp_query_store_force_plan procedimento. O Mecanismo de Banco de Dados no SQL Server 2017 (14.x) fornece informações sobre planos regredidos e ações corretivas recomendadas. Além disso, o Mecanismo de Banco de Dados permite automatizar totalmente esse processo e permitir que o Mecanismo de Banco de Dados corrija qualquer problema encontrado relacionado à alteração do plano.

Important

A correção automática do plano deve ser usada no escopo de uma atualização no nível de compatibilidade do banco de dados, depois que uma linha de base for capturada, para reduzir automaticamente os riscos de atualização da carga de trabalho. Para obter mais informações sobre esse caso de uso, consulte Manter a estabilidade do desempenho durante a atualização para o SQL Server mais recente.

Correção automática de escolha do plano

O Mecanismo de Banco de Dados pode alternar automaticamente para o último plano válido conhecido sempre que uma regressão de escolha de plano for detectada.

Correção de escolha do plano de execução de consulta.

O Mecanismo de Banco de Dados detecta automaticamente qualquer regressão de escolha de plano potencial, incluindo o plano que deve ser usado em vez do plano errado. O plano de execução resultante forçado pela correção automática do plano será o mesmo ou semelhante ao último bom plano conhecido. Como o plano resultante pode não ser idêntico ao último plano bom conhecido, o desempenho do plano forçado pode variar. Em casos raros, a diferença de desempenho pode ser significativa e negativa; nesse caso, a correção automática do plano interromperá automaticamente a tentativa de forçar o plano de substituição.

Quando o Mecanismo de Banco de Dados aplica o último plano válido conhecido antes da regressão ocorrer, ele monitora automaticamente o desempenho do plano forçado. Se o plano forçado não for melhor do que o plano regredido, o novo plano não será forçado e o Mecanismo de Banco de Dados compilará um novo plano. Se o Mecanismo de Banco de Dados verificar se o plano forçado é melhor do que o plano regredido, o plano forçado será mantido. Ele será mantido até que ocorra um recompilação (por exemplo, na próxima atualização de estatísticas ou alteração de esquema). Para obter mais informações sobre o forçamento de plano e os tipos de planos que podem ser forçados, consulte Limitações do Forçamento de Plano.

Note

Se a instância do SQL Server for reiniciada antes que uma ação de imposição de plano seja verificada, esse plano será automaticamente liberado da imposição. Caso contrário, a imposição de plano será mantida nas reinicializações do SQL Server.

Habilitar a correção automática de escolha do plano

Você pode habilitar o ajuste automático por banco de dados e especificar que o último bom plano deve ser forçado sempre que alguma regressão de alteração de plano for detectada. O ajuste automático está habilitado usando o seguinte comando:

ALTER DATABASE <yourDatabase>
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

Depois de habilitar essa opção, o Mecanismo de Banco de Dados forçará automaticamente qualquer recomendação em que o ganho estimado da CPU seja maior que 10 segundos ou o número de erros no novo plano seja maior do que o número de erros no plano recomendado e verifique se o plano forçado é melhor que o atual.

Para habilitar o ajuste automático no Banco de Dados SQL do Azure e na Instância Gerenciada de SQL do Azure, consulte Habilitar ajuste automático no Banco de Dados SQL do Azure usando o portal do Azure.

Alternativa – correção de escolha do plano manual

Sem ajuste automático, os usuários devem monitorar periodicamente o sistema e procurar as consultas que regrediram. Se qualquer plano tiver regredido, o usuário deverá encontrar um bom plano anterior e forçá-lo em vez do atual usando sp_query_store_force_plan o procedimento. A melhor prática seria forçar o último plano bom conhecido porque planos mais antigos podem ser inválidos devido a alterações de estatística ou índice. O usuário que força o último bom plano conhecido deve monitorar o desempenho da consulta executada usando o plano forçado e verificar se o plano forçado funciona conforme o esperado. Dependendo dos resultados do monitoramento e da análise, o plano deve ser forçado ou o usuário deve encontrar outra maneira de otimizar a consulta, como reescrevê-la. Planos forçados manualmente não devem ser forçados para sempre, pois o Mecanismo de Banco de Dados deve ser capaz de aplicar planos ideais. O usuário ou o DBA devem, eventualmente, cancelar o controle do plano usando sp_query_store_unforce_plan o procedimento e permitir que o Mecanismo de Banco de Dados localize o plano ideal.

Tip

Como alternativa, use o modo de exibição Consultas com Planos Forçados do Repositório de Consultas para localizar e cancelar planos.

O SQL Server fornece todas as exibições e procedimentos necessários para monitorar o desempenho e corrigir problemas no Repositório de Consultas.

No SQL Server 2016 (13.x), você pode encontrar regressões nas escolhas de plano usando visões do sistema do Query Store (Repositório de Consultas). A partir do SQL Server 2017 (14.x), o Mecanismo de Banco de Dados detecta e mostra possíveis regressões de escolha do plano e as ações recomendadas que devem ser aplicadas na DMV sys.dm_db_tuning_recommendations (Transact-SQL). A DMV mostra informações sobre o problema, a importância do problema e detalhes como a consulta identificada, a ID do plano regredido, a ID do plano que foi usado como linha de base para comparação e a instrução Transact-SQL que pode ser executada para corrigir o problema.

tipo description datetime pontuação details ...
FORCE_LAST_GOOD_PLAN Tempo de CPU alterado de 4 ms para 14 ms 3/17/2017 83 queryId recommendedPlanId regressedPlanId T-SQL
FORCE_LAST_GOOD_PLAN Tempo de CPU alterado de 37 ms para 84 ms 3/16/2017 26 queryId recommendedPlanId regressedPlanId T-SQL

Algumas colunas dessa visualização são descritas na seguinte lista:

  • Tipo da ação FORCE_LAST_GOOD_PLANrecomendada.
  • Descrição que contém informações por que o Mecanismo de Banco de Dados acha que essa alteração de plano é uma possível regressão de desempenho.
  • Data e hora em que a regressão potencial é detectada.
  • Pontuação dessa recomendação.
  • Detalhes sobre os problemas, como ID do plano detectado, ID do plano regredido, ID do plano que deve ser forçado a corrigir o problema, Transact-SQL script que pode ser aplicado para corrigir o problema etc. Os detalhes são armazenados no formato JSON.

Use a consulta a seguir para obter um script que corrija o problema e informações adicionais sobre o ganho estimado:

SELECT reason, score,
      script = JSON_VALUE(details, '$.implementationDetails.script'),
      planForceDetails.*,
      estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount)
                  * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000,
      error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES','NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            regressedPlanId int '$.regressedPlanId',
            recommendedPlanId int '$.recommendedPlanId',
            regressedPlanErrorCount int,
            recommendedPlanErrorCount int,
            regressedPlanExecutionCount int,
            regressedPlanCpuTimeAverage float,
            recommendedPlanExecutionCount int,
            recommendedPlanCpuTimeAverage float
          ) AS planForceDetails;

Veja aqui o conjunto de resultados.

reason pontuação script query_id id_do_plano_atual ID do plano recomendado ganho_estimado propenso a erros
Tempo de CPU alterado de 3 ms para 46 ms 36 EXEC sp_query_store_force_plan 12, 17; 12 28 17 11:59 0

A coluna estimated_gain representa o número estimado de segundos que seriam salvos se o plano recomendado fosse usado para execução de consulta em vez do plano atual. O plano recomendado deve ser adotado em vez do plano atual se o ganho for superior a 10 segundos. Se houver mais erros (por exemplo, tempo limite ou execuções anuladas) no plano atual do que no plano recomendado, a coluna error_prone será definida como o valor YES. Um plano propenso a erros é outro motivo pelo qual o plano recomendado deve ser forçado em vez do atual.

Embora o Mecanismo de Banco de Dados forneça todas as informações necessárias para identificar regressões de escolha do plano, o monitoramento contínuo e a correção de problemas de desempenho podem se tornar um processo entediante. O ajuste automático facilita muito esse processo.

Note

Os dados na sys.dm_db_tuning_recommendations DMV não são persistentes após uma reinicialização do mecanismo de banco de dados. Use a coluna sqlserver_start_time em sys.dm_os_sys_info para localizar a última hora de inicialização do mecanismo de banco de dados.

Gerenciamento de índice automático

No Banco de Dados SQL do Azure, o gerenciamento de índice é fácil porque o Banco de Dados SQL do Azure aprende sobre sua carga de trabalho e garante que seus dados estejam sempre indexados de forma ideal. O design de índice adequado é crucial para o desempenho ideal da carga de trabalho, e o gerenciamento automático de índices pode ajudá-lo a otimizar seus índices. O gerenciamento automático de índice pode corrigir problemas de desempenho em bancos de dados indexados incorretamente ou manter e melhorar índices no esquema de banco de dados existente. O ajuste automático no Banco de Dados SQL do Azure executa as seguintes ações:

  • Identifica índices que podem melhorar o desempenho de suas consultas Transact-SQL que leem dados das tabelas.
  • Identifica índices ou índices redundantes que não foram usados em um período mais longo que poderiam ser removidos. A remoção de índices desnecessários melhora o desempenho de consultas que atualizam dados em tabelas.

Por que você precisa de gerenciamento de índice?

Os índices aceleram algumas de suas consultas que leem dados das tabelas, no entanto, elas podem diminuir a velocidade das consultas que atualizam os dados. Você precisa analisar cuidadosamente quando criar um índice e quais colunas você precisa incluir no índice. Alguns índices podem não ser necessários após algum tempo. Portanto, você precisaria identificar e remover periodicamente esses índices que não trazem benefícios. Se você ignorar os índices não utilizados, o desempenho das consultas que atualizam dados será reduzido sem qualquer benefício para as consultas que leem dados. Índices não utilizados também afetam o desempenho geral do sistema porque atualizações adicionais exigem registro em log desnecessário.

Encontrar o conjunto ideal de índices que melhoram o desempenho das consultas que leem dados de suas tabelas e têm impacto mínimo nas atualizações pode exigir uma análise contínua e complexa.

O Banco de Dados SQL do Azure usa inteligência interna e regras avançadas que analisam suas consultas, identificam índices que seriam ideais para suas cargas de trabalho atuais e identificam os índices que podem precisar ser removidos. O Banco de Dados SQL do Azure garante que você tenha um conjunto mínimo necessário de índices que otimizam as consultas que leem dados, com impacto minimizado nas outras consultas.

Gerenciamento de índice automático

Além da detecção, o Banco de Dados SQL do Azure pode aplicar automaticamente as recomendações identificadas. Se você descobrir que as regras internas melhoram o desempenho do banco de dados, você pode permitir que o Banco de Dados SQL do Azure gerencie automaticamente seus índices.

Quando o Banco de Dados SQL do Azure aplica uma recomendação CREATE INDEX ou DROP INDEX, ele monitora automaticamente o desempenho das consultas afetadas pelo índice. O novo índice será mantido somente se os desempenhos das consultas afetadas forem aprimorados. O índice descartado será recriado automaticamente se houver algumas consultas que são mais lentas devido à ausência do índice.

Considerações sobre gerenciamento automático de índices

As ações necessárias para criar índices necessários no Banco de Dados SQL do Azure podem consumir recursos e afetar temporalmente o desempenho da carga de trabalho. Para minimizar o impacto da criação de índice no desempenho da carga de trabalho, o Banco de Dados SQL do Azure encontra uma janela de tempo apropriada para qualquer operação de gerenciamento de índice. A ação de ajuste será adiada se o banco de dados precisar de recursos para executar sua carga de trabalho e for reiniciado quando o banco de dados tiver recursos não utilizados suficientes que podem ser usados para a tarefa de manutenção. Um recurso importante no gerenciamento automático de índice é uma verificação das ações. Quando o Banco de Dados SQL do Azure cria ou descarta um índice, um processo de monitoramento analisa o desempenho da carga de trabalho para verificar se a ação melhorou o desempenho geral. Se não trouxer melhorias significativas, a ação será revertida imediatamente. Dessa forma, o Banco de Dados SQL do Azure garante que as ações de ajuste automático não afetem negativamente o desempenho da carga de trabalho. Os índices criados pelo ajuste automático são transparentes para a operação de manutenção no esquema subjacente. Alterações de esquema, como remover ou renomear colunas, não são bloqueadas pela presença de índices criados automaticamente. Os índices criados automaticamente pelo Banco de Dados SQL do Azure são removidos imediatamente quando a tabela ou as colunas relacionadas são descartadas.

Alternativa – gerenciamento manual de índice

Sem o gerenciamento automático de índice, um usuário ou DBA precisaria consultar manualmente a exibição sys.dm_db_missing_index_details (Transact-SQL) ou usar o relatório painel de desempenho no Management Studio para encontrar índices que possam melhorar o desempenho, criar índices usando os detalhes fornecidos nessa exibição e monitorar manualmente o desempenho da consulta. Para localizar os índices que devem ser descartados, os usuários devem monitorar as estatísticas de uso operacional dos índices para encontrar índices raramente usados.

O Banco de Dados SQL do Azure simplifica esse processo. O Banco de Dados SQL do Azure analisa sua carga de trabalho, identifica as consultas que podem ser executadas mais rapidamente com um novo índice e identifica índices não utilizados ou duplicados. Encontre mais informações sobre a identificação de índices que devem ser alterados em Localizar recomendações de índice no portal do Azure.

Próximas etapas

  • Last updated on