Partilhar via

Ajuste automático

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores Azure SQL DatabaseAzure SQL Managed InstanceSQL database no Microsoft Fabric

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

O ajuste automático, introduzido no SQL Server 2017 (14.x), notifica sempre que um possível problema de desempenho é detetado e permite aplicar ações corretivas ou permite que o Mecanismo de Banco de Dados corrija automaticamente problemas de desempenho. Ajuste automático O SQL Server identifica e corrige problemas de desempenho causados por regressões de escolha do plano de execução de consulta. A sintonia automática no Azure SQL Database e na base de dados SQL no Microsoft Fabric também cria índices necessários e elimina í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 integrado que pode ajustar e melhorar automaticamente o desempenho de suas consultas, adaptando dinamicamente o banco de dados à sua carga de trabalho. Há dois recursos de ajuste automático disponíveis:

  • A correção automática de plano identifica planos de execução de consulta problemáticos, como sensibilidade a parâmetros ou problemas de deteção de parâmetros , e corrige problemas de desempenho relacionados ao plano de execução de consultas, forçando o último plano válido conhecido antes da regressão ocorrer. Aplica-se a: SQL Server (a partir do SQL Server 2017 (14.x)), Azure SQL Database, SQL Database no Microsoft Fabric e Azure SQL Managed Instance

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

Note

Neste artigo, as funcionalidades e comportamentos da Azure SQL Database também se aplicam à base de dados SQL no Microsoft Fabric.

Porquê a afinação automática?

Três das principais tarefas na administração clássica de banco de dados são monitorar a carga de trabalho, identificar consultas de 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 tediosa, especialmente quando se lida com muitos bancos de dados. Gerenciar um grande número de bancos de dados pode ser impossível de fazer de forma eficiente. Em vez de monitorar e ajustar seu banco de dados manualmente, você pode considerar delegar algumas das ações de monitoramento e ajuste ao Mecanismo de Banco de Dados usando o recurso de ajuste automático.

Como funciona o ajuste automático?

O ajuste automático é um processo contínuo de monitoramento e análise que aprende constantemente sobre as características de sua 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 à sua 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 descobertas, o ajuste automático aplica ações de ajuste que melhoram o desempenho de sua carga de trabalho. Além disso, o ajuste automático monitora continuamente o desempenho do banco de dados após a implementação de quaisquer alterações para garantir que ele melhore o desempenho de sua carga de trabalho. Qualquer ação que não tenha melhorado o desempenho é revertida automaticamente. Esse processo de verificação é um recurso fundamental que garante que qualquer alteração feita pelo ajuste automático não diminua o desempenho geral da sua carga de trabalho.

Correção automática do plano

A correção automática do plano é um recurso de ajuste automático que identifica a regressão da escolha do plano de execução e corrige automaticamente o problema forçando o último plano bom 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 de o Repositório de Consultas estar habilitado no banco de dados para o controle da carga de trabalho.

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

O Mecanismo de Banco de Dados do SQL Server pode usar diferentes planos de execução 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 a parâmetros ou um problema relacionado à deteção de parâmetros .

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

Sempre que notar que ocorreu uma regressão na escolha do plano, deve 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 do nível de compatibilidade do banco de dados, após a captura de uma linha de base, 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 de desempenho durante a atualização para o SQL Server mais recente.

Correção automática da escolha do plano

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

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

O Mecanismo de Banco de Dados deteta automaticamente qualquer possível regressão de escolha de plano, incluindo o plano que deve ser usado em vez do plano errado. O plano de execução resultante aplicado pela correção automática de planos será o mesmo ou semelhante ao último plano conhecido como bom. Como o plano resultante pode não ser idêntico ao último plano válido, 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 a substituição do plano.

Quando o Mecanismo de Banco de Dados aplica o último plano válido 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 uma 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 planos e os tipos de planos que podem ser forçados, consulte limitações de forçamento de planos.

Note

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

Ativar a correção automática da escolha do plano

Você pode habilitar o ajuste automático por banco de dados e especificar que o último plano válido deve ser forçado sempre que alguma regressão de alteração de plano for detetada. O ajuste automático é ativado 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 de CPU estimado for superior a 10 segundos ou o número de erros no novo plano for maior do que o número de erros no plano recomendado e verificará se o plano forçado é melhor do que o atual.

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

Alternativa - correção manual da escolha do plano

Sem ajuste automático, os usuários devem monitorar periodicamente o sistema e procurar as consultas que regrediram. Se algum plano regrediu, o usuário deve 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 válido conhecido porque os planos mais antigos podem ser inválidos devido a alterações estatísticas ou de índice. O utilizador que força o último plano bom conhecido deve monitorizar 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 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, porque o Mecanismo de Banco de Dados deve ser capaz de aplicar planos ideais. O utilizador ou DBA deve eventualmente libertar o plano usando o procedimento sp_query_store_unforce_plan e permitir que o motor de base de dados encontre o plano ideal.

Tip

Como alternativa, use o modo de exibição Consultas com Planos Forçados para localizar e desforçar planos.

O SQL Server fornece todos os modos de exibição 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 na escolha de plano usando visualizações do sistema do Query Store. A partir do SQL Server 2017 (14.x), o Mecanismo de Banco de Dados deteta e mostra possíveis regressões de escolha de plano e as ações recomendadas que devem ser aplicadas no sys.dm_db_tuning_recommendations (Transact-SQL) DMV. O Detran mostra informações sobre o problema, a importância do problema e detalhes como a consulta identificada, o ID do plano regredido, o ID do plano que foi usado como linha de base para comparação e a declaração de Transact-SQL que pode ser executada para corrigir o problema.

tipo description datetime classificação details ...
FORCE_LAST_GOOD_PLAN O tempo da CPU mudou 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 desse modo de exibição são descritas na lista a seguir:

  • Tipo de ação FORCE_LAST_GOOD_PLANrecomendada .
  • Descrição que contém informações sobre por que o Mecanismo de Banco de Dados acha que essa alteração de plano é uma regressão de desempenho potencial.
  • Data/hora em que a regressão potencial é detetada.
  • Pontuação desta recomendação.
  • Detalhes sobre os problemas, como ID do plano detetado, 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;

Aqui está o conjunto de resultados.

reason classificação script query_id id_do_plano_atual plano_id recomendado ganho_estimado propenso a erros
O tempo da CPU mudou 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 a execução da consulta em vez do plano atual. O plano recomendado deve ser forçado em vez do plano atual se o ganho for superior a 10 segundos. Se houver mais erros (por exemplo, tempos limite ou execuções abortadas) no plano atual do que no plano recomendado, a coluna error_prone será definida como o valor YES. Um plano propenso a erros é outra razão pela 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 de plano, o monitoramento contínuo e a correção de problemas de desempenho podem se tornar um processo tedioso. O ajuste automático torna esse processo muito mais fácil.

Note

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

Gerenciamento automático de índice

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 sejam sempre indexados de forma otimizada. O design adequado do índice é crucial para o desempenho ideal de sua carga de trabalho, e o gerenciamento automático de índices pode ajudá-lo a otimizar seus índices. O gerenciamento automático de índices 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 de Transact-SQL que leem dados das tabelas.
  • Identifica índices redundantes ou índices que não foram usados em um longo período de tempo 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 das suas consultas que leem dados das tabelas, no entanto, podem tornar mais lentas as consultas que atualizam 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, seria necessário identificar e eliminar periodicamente os índices que não trazem nenhum benefício. Se você ignorar os índices não utilizados, o desempenho das consultas que atualizam dados será diminuído sem qualquer benefício para as consultas que leem dados. Os í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 talvez precisem 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 automático de índice

Além da deteção, o Banco de Dados SQL do Azure pode aplicar automaticamente as recomendações identificadas. Se você achar que as regras internas melhoram o desempenho do seu banco de dados, poderá 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 o desempenho das consultas afetadas for melhorado. O índice descartado será recriado automaticamente se houver algumas consultas que são executadas mais lentamente devido à ausência do índice.

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

As ações necessárias para criar os índices necessários no Banco de Dados SQL do Azure podem consumir recursos e afetar temporariamente 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 é adiada se o banco de dados precisar de recursos para executar sua carga de trabalho e é reiniciada quando o banco de dados tem recursos não utilizados suficientes que podem ser usados para a tarefa de manutenção. Uma característica importante na gestão automática de índices é a 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 de sua carga de trabalho para verificar se a ação melhorou o desempenho geral. Se não trouxe melhoria significativa - a ação é imediatamente revertida. 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 sua carga de trabalho. Os índices criados pelo ajuste automático são transparentes para a operação de manutenção no esquema subjacente. As alterações de esquema, como soltar 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 imediatamente descartados quando a tabela ou colunas relacionadas são descartadas.

Alternativa - gestão manual de índices

Sem o gerenciamento automático de índices, um usuário ou DBA precisaria consultar manualmente a exibição sys.dm_db_missing_index_details (Transact-SQL) ou usar o relatório do Painel de Desempenho no Management Studio para localizar índices que possam melhorar o desempenho, criar índices usando os detalhes fornecidos nessa exibição e monitorar manualmente o desempenho da consulta. A fim de encontrar 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 poderiam 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óximos passos

  • Last updated on