Imposição de plano otimizado com Repositório de Consultas
Aplica-se a: 
SQL Server 2022 (16.x) Banco de Dados SQL do Azure
A otimização de consulta é um processo com várias fases de geração de um plano de execução de consulta "bom o suficiente". Em alguns casos, a compilação de consultas, uma parte da otimização de consulta, pode representar um grande percentual do tempo de execução geral da consulta e consumir recursos significativos do sistema. A imposição de plano otimizado faz parte da família de recursos de processamento de consulta inteligente. A imposição de plano otimizado reduz a sobrecarga de compilação para repetir consultas forçadas e exige que o Repositório de Consultas esteja habilitado e no modo de "leitura/gravação". Depois que o plano de execução de consulta é gerado, as etapas de compilação específicas são armazenadas para reutilização como um script de reprodução de otimização. Um script de reprodução de otimização é armazenado como parte do XML do plano de execução compactado no Repositório de Consultas, em um atributo OptimizationReplay oculto.
Implementação da imposição de plano otimizado
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 reprodução de otimização é criado.
Após a conclusão da compilação, várias métricas de runtime 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 reprodução de otimização estará qualificado para persistência. Essas métricas de runtime 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 possível benefício de usar um script de reprodução de otimização também é comparado com a sobrecarga de armazenamento do script de reprodução de otimização. Uma estimativa do tempo relativo para reproduzir o script de reprodução de otimização é comparada com o tempo gasto na execução do processo de otimização normal. Essa estimativa é baseada no número de tarefas de otimização armazenadas no script de reprodução de otimização e no número de tarefas de otimização executadas durante a compilação normal. Se a reprodução do script de reprodução de otimização mostrar um benefício significativo na redução do tempo de compilação, o script de reprodução de otimização persistirá.
Considerações
Quando o recurso de imposição de plano otimizado está habilitado, os critérios de elegibilidade para a imposição de plano otimizado são:
Somente os planos de consulta que passam pela otimização completa são qualificados, 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 qualificadas.
No entanto, se o Repositório de Consultas capturar independentemente um plano de consulta que foi definido pela imposição de plano otimizado, o script de reprodução de otimização será criado para uma segunda recompilação dessa mesma consulta, sujeito a eventos de recompilação padrão. Saiba mais sobre a recompilação nos Planos de execução de recompilação.
Mesmo que um script de reprodução de otimização tenha sido gerado, ele poderá 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 de compilação e execução acumulados. Nesse caso, o script de reprodução de otimização inválido é removido da memória de forma assíncrona.
Habilitar e desabilitar a imposição de plano otimizado
Você pode habilitar ou desabilitar a imposição de plano otimizado para um banco de dados. Quando a imposição de plano otimizado está habilitada para um banco de dados, você pode desabilitá-la para consultas individuais usando a dica de DISABLE_OPTIMIZED_PLAN_FORCING consulta. Você também pode desabilitar a imposição de plano otimizado para um plano de consulta que é forçado no Repositório de Consultas.
Habilitar ou desabilitar a imposição de plano otimizado para um banco de dados
A imposição de plano otimizado é habilitada por padrão nos novos bancos de dados criados no SQL Server 2022 (16.x) e posteriores. O Repositório de Consultas deve ser habilitado para cada banco de dados em que a imposição de plano otimizado é usada. As instâncias atualizadas com bancos de dados existentes ou bancos de dados restaurados de uma versão inferior do SQL Server têm a imposição de plano otimizada habilitada por padrão.
Para habilitar a imposição de plano otimizado no nível do banco de dados, use a configuração com escopo do banco de dados ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON. Você deve precisará habilitar o Repositório de Consultas se ele ainda não estiver habilitado. Localize o código de exemplo no Exemplo A ou saiba mais sobre Repositório de Consultas em Monitorar o 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 com escopo do banco de dados ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF.
Desabilitar a imposição de plano otimizado com uma dica de consulta
Quando o recurso de imposição de plano otimizado está habilitado em um banco de dados, é possível desabilitar a imposição de plano otimizado de uma consulta individual usando a dica de consulta DISABLE_OPTIMIZED_PLAN_FORCING.
Encontre um exemplo de aplicação dessa dica de consulta no Exemplo E.
Forçar um plano com Repositório de Consultas, mas desabilitar a imposição de plano otimizado
O procedimento sp_query_store_force_plan inclui um parâmetro disable_optimized_plan_forcing. Para usar esse parâmetro, um parâmetro extra é necessário pelo sp_query_store_force_plan procedimento armazenado. 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 exibição de catálogo sys.query_store_plan inclui colunas que indicam se o plano tem um script de reprodução de otimização associado e adiciona um novo estado à coluna de motivo de falha existente específica ao script de reprodução de otimização associado. Saiba mais em sys.query_store_plan.
Exemplos
Os exemplos de código do Transact-SQL deste artigo usa o banco de dados de exemplo AdventureWorks2022 ou AdventureWorksDW2022, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects.
R. Habilitar Repositório de Consultas e imposição de plano otimizado para um banco de dados
O código a seguir habilita o Repositório de Consultas em um banco de dados e, depois, habilita a imposição de plano otimizado no banco 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. Selecionar todas as consultas que têm um script de reprodução de otimização
O código de exemplo a seguir seleciona todos as query_ids que têm um script de reprodução de otimização no Repositório de Consultas. Antes de executar o código de exemplo, 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.has_compile_replay_script = 1;
GO
C. Forçar um plano e desabilitar 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 imposição de plano otimizado. 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 ao tentar desabilitar a imposição de plano otimizado 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 desabilitar 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 é desabilitada pelo Repositório de Consultas
O exemplo a seguir consulta todos os planos que foram forçados no Repositório de Consultas, em que is_optimized_plan_forcing_disabled está definido 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. Desabilitar a imposição de plano otimizado para uma consulta
O exemplo a seguir desabilita a imposição de plano otimizado para uma consulta usando a dica de consulta DISABLE_OPTIMIZED_PLAN_FORCING.
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