Nota
O acesso a esta página requer autorização. Pode tentar iniciar sessão ou alterar os diretórios.
O acesso a esta página requer autorização. Pode tentar alterar os diretórios.
Aplica-se a:
SQL Server 2022 (16.x) Azure SQL DatabaseSQL database em Microsoft Fabric
A otimização de consultas é um processo multifásico de geração de um plano de execução de consulta "suficientemente bom". Em alguns casos, a compilação de consultas, uma parte da otimização de consultas, pode representar uma grande porcentagem do tempo geral de execução da consulta e consumir recursos significativos do sistema. A imposição otimizada de planos faz parte da família de recursos inteligentes de processamento de consultas. O forçamento de plano otimizado reduz a sobrecarga de compilação para consultas forçadas repetitivas e requer que o Repositório de Consultas esteja habilitado e no modo de "leitura e gravação". Depois que o plano de execução da consulta é gerado, etapas específicas de compilação são armazenadas para reutilização como um script de repetição de otimização. Um script de repetição de otimização é armazenado como parte do XML de plano de execução compactado em Query Store, em um atributo OptimizationReplay oculto.
Plano otimizado forçando a implementação
Quando uma consulta passa pela primeira vez pelo processo de compilação, um limite baseado na estimativa do tempo gasto na otimização (com base na árvore de entrada do otimizador de consulta) determina se um script de repetição de otimização é criado.
Após a conclusão da compilação, várias métricas de tempo de execução ficam disponíveis para avaliar se a estimativa anterior estava correta. Se o Mecanismo de Banco de Dados confirmar que o limite foi ultrapassado, o script de repetição de otimização estará qualificado para persistência. Essas métricas de tempo de execução incluem o número de objetos acessados, o número de junções, o número de tarefas de otimização executadas durante a otimização e o tempo real de otimização.
O benefício potencial de usar um script de repetição de otimização também é comparado à sobrecarga de armazenar o script de repetição de otimização. Uma estimativa do tempo relativo para reexecutar o script de otimização é comparada com o tempo gasto executando o processo normal de otimização. Essa estimativa é baseada no número de tarefas de otimização armazenadas no script de repetição de otimização e no número de tarefas de otimização executadas durante a compilação normal. Se a repetição do script de repetição de otimização mostrar um benefício substancial na redução do tempo de compilação, o script de repetição de otimização será mantido.
Considerations
Quando o recurso de aplicação de plano otimizado está ativado, os critérios de elegibilidade para a aplicação de plano otimizado são:
Apenas são elegíveis os planos de consulta que passam por otimização total, o que pode ser verificado pela presença da propriedade
StatementOptmLevel="FULL".Instruções com dica RECOMPILE e consultas distribuídas não são elegíveis.
No entanto, se o Repositório de Consultas capturar independentemente um plano de consulta que foi definido pela força de plano otimizado, o script de repetição de otimização será criado para uma segunda recompilação dessa mesma consulta, sujeita a eventos de recompilação padrão. Saiba mais sobre a recompilação em Recompilando planos de execução.
Mesmo que um script de repetição de otimização tenha sido gerado, ele pode não ser persistido no Repositório de Consultas se os critérios de políticas de captura configurados pelo Repositório de Consultas não forem atendidos, principalmente o número de execuções dessa instrução e seus tempos acumulados de compilação e execução. Nesse caso, o script de repetição de otimização inválido é removido da memória de forma assíncrona.
Ativar e desativar o plano otimizado forçando
Você pode habilitar ou desabilitar a força de plano otimizada para um banco de dados. Quando a imposição de plano otimizado está habilitada para um banco de dados, você pode desativá-la para consultas individuais usando a sugestão de consulta DISABLE_OPTIMIZED_PLAN_FORCING. Você também pode desabilitar a imposição de plano otimizado para um plano de consulta que é forçado no Repositório de Consultas.
Ativar ou desativar a forçagem do plano otimizado para um banco de dados
O forçamento otimizado de plano está ativado por padrão para novos bancos de dados criados no SQL Server 2022 (16.x) e superior. O Repositório de Consultas deve ser ativado para todas as bases de dados onde o forçamento de plano otimizado é usado. As instâncias atualizadas com bancos de dados existentes, ou bancos de dados restaurados a partir de uma versão inferior do SQL Server, otimizaram a força de plano habilitada por padrão.
Para habilitar a imposição de plano otimizado no nível do banco de dados, use a configuração do escopo do ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON banco de dados. Você deve habilitar o Repositório de Consultas se ele ainda não estiver habilitado. Encontre um código de exemplo no Exemplo A ou saiba mais sobre o Repositório de Consultas no Monitor de desempenho usando o Repositório de Consultas.
Para desabilitar a imposição de plano otimizado no nível do banco de dados, use a configuração do escopo do ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF banco de dados.
Desativar a imposição de planos otimizados usando uma dica de consulta
Quando o recurso de imposição de plano otimizado está habilitado num banco de dados, pode desativar a imposição de plano otimizado para uma consulta individual usando a sugestão de consulta.
Encontre um exemplo de aplicação desta dica de consulta no Exemplo E.
Forçar um plano com o Repositório de Consultas, mas desativar o forçamento de plano otimizado
O procedimento sp_query_store_force_plan inclui um disable_optimized_plan_forcing parâmetro. Para que este parâmetro possa ser utilizado, o procedimento armazenado sp_query_store_force_plan requer um parâmetro extra. O parâmetro extra é chamado @replica_group_id. Por padrão, o primário @replica_group_id tem um valor de um (1), mesmo no caso em que não há réplicas secundárias configuradas.
Encontre um exemplo de aplicação dos parâmetros apropriados ao sp_query_store_force_plan procedimento armazenado no Exemplo C.
A vista do catálogo sys.query_store_plan inclui colunas que indicam se o plano possui um script de repetição de otimização associado e adiciona um novo estado à coluna existente que indica o motivo de falha específico para o script de repetição de otimização associado. Saiba mais em sys.query_store_plan.
Examples
Os exemplos de código neste artigo usam o banco de dados de exemplo AdventureWorks2025 ou AdventureWorksDW2025, que pode ser descarregado da página inicial de Exemplos e Projetos da Comunidade do Microsoft SQL Server.
A. Habilitar o Repositório de Consultas e planejar o forcing otimizado para um banco de dados
O código a seguir habilita o Repositório de Consultas numa base de dados e depois ativa a imposição de planos otimizados na base de dados. Saiba mais sobre as opções que habilitam o Repositório de Consultas nas opções ALTER DATABASE SET.
Antes de executar o código, conecte-se ao banco de dados de usuário apropriado.
ALTER DATABASE CURRENT SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
DATA_FLUSH_INTERVAL_SECONDS = 900,
QUERY_CAPTURE_MODE = AUTO,
MAX_STORAGE_SIZE_MB = 1024,
INTERVAL_LENGTH_MINUTES = 60
);
GO
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;
GO
B. Selecione todas as consultas que têm um script de reexecução de otimização
O exemplo de código abaixo seleciona todos os query_ids que possuem um script de repetição de otimização no Query Store. Conecte-se ao banco de dados de usuário apropriado antes de executar o código de exemplo.
SELECT q.query_id,
t.query_sql_text,
p.plan_id,
TRY_CAST (p.query_plan AS XML) AS query_plan,
p.is_forced_plan,
p.count_compiles
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_query AS q
ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text AS t
ON q.query_text_id = t.query_text_id
WHERE p.has_compile_replay_script = 1;
GO
C. Forçar um plano e desativar a imposição de plano otimizado no Repositório de Consultas
O código a seguir força um plano no Repositório de Consultas, mas desabilita a força de plano otimizada. Antes de executar o código a seguir, substitua @query_id e @plan_id por uma combinação apropriada para sua instância. O sp_query_store_force_plan procedimento armazenado espera que o @replica_group_id parâmetro seja passado como o terceiro valor de parâmetro quando tentar desativar a imposição de planos otimizados no Repositório de Consultas. Isso pode ser usado para desabilitar a imposição de plano otimizado para um plano forçado específico em uma réplica específica. Um valor de @replica_group_id = 1 é usado para desativar o recurso na réplica primária.
EXECUTE sp_query_store_force_plan
@query_id = 148,
@plan_id = 4,
@replica_group_id = 1,
@disable_optimized_plan_forcing = 1;
GO
Saiba mais em sp_query_store_force_plan.
D. Selecione todas as consultas em que a imposição de plano otimizado está desativada pelo Repositório de Consultas
O exemplo a seguir consulta todos os planos forçados no Repositório de Consultas, onde is_optimized_plan_forcing_disabled está configurado como 1. Antes de executar o código, conecte-se ao banco de dados de usuário apropriado.
SELECT q.query_id,
t.query_sql_text,
p.plan_id,
TRY_CAST (p.query_plan AS XML) AS query_plan,
p.is_forced_plan,
p.count_compiles
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_query AS q
ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text AS t
ON q.query_text_id = t.query_text_id
WHERE p.is_optimized_plan_forcing_disabled = 1;
GO
E. Desativar a imposição de plano otimizado para uma consulta
O exemplo a seguir desativa a imposição de plano otimizado para uma consulta utilizando a dica de consulta.
SELECT ProductID,
OrderQty,
SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (USE HINT('DISABLE_OPTIMIZED_PLAN_FORCING'));
GO