Imposição de plano otimizado com Repositório de Consultas

Artigo
05/30/2023

Aplica-se a: SQL Server 2022 (16.x)

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 passar pelo processo de compilação pela primeira vez, um limite baseado na estimativa do tempo gasto na otimização (com base na árvore de entrada do otimizador de consulta) determinará 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 for confirmado que o limite foi ultrapassado, o script de reprodução de otimização será 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, com base no número de tarefas de otimização armazenadas no script de reprodução de otimização, bem como 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 a dica RECOMPILE e consultas distribuídas não são qualificadas.

No entanto, se o Repositório de Consultas capturar de maneira independente um plano de consulta com escopo 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, sujeita 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 mantido no Repositório de Consultas se os critérios das políticas de captura configurados no Repositório de Consultas não forem atendidos, em particular o número de execuções dessa instrução e os tempos de compilação e execução acumulados dela. Nesse caso, o script de reprodução de otimização inválido será removido da memória de maneira 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, é possível desabilitá-la para consultas individuais usando a dica de consulta DISABLE_OPTIMIZED_PLAN_FORCING. Você também pode desabilitar a imposição de plano otimizado para um plano de consulta 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. Instâncias atualizadas com bancos de dados existentes ou bancos de dados restaurados de uma versão inferior do SQL Server não terão a imposição de plano otimizado 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 consultaDISABLE_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 adicional é exigido pelo procedimento armazenado sp_query_store_force_plan. O parâmetro adicional é chamado replica_group_id. Por padrão, o replica_group_id primário terá um valor de um (1) mesmo no caso em que não houver réplicas secundárias configuradas.

Encontre um exemplo de aplicação dos parâmetros apropriados ao procedimento armazenado sp_query_store_force_plan 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 (Transact-SQL).

Exemplos

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 em Opções ALTER DATABASE SET (Transact-SQL).

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 procedimento armazenado sp_query_store_force_plan esperará que o parâmetro @replica_group_id seja passado como o terceiro valor de parâmetro ao tentar desabilitar a imposição do 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 1 – @replica_group_id=1 será usado para desabilitar o recurso na réplica primária.

EXEC 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 (Transact-SQL).

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 foi 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 consultaDISABLE_OPTIMIZED_PLAN_FORCING. Este exemplo usa o banco de dados de exemplo AdventureWorks.

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

Próximas etapas

Saiba mais sobre o Repositório de Consultas e a imposição de plano otimizado nos seguintes artigos: