Compartilhar via

OPPO (otimização de plano de parâmetro opcional)

Aplica-se a: SQL Server 2025 (17.x) Azure SQL Database SQLdatabase in Microsoft Fabric

A OPPO (otimização de plano de parâmetro opcional) melhora a qualidade do plano de consulta para consultas que incluem parâmetros opcionais. Nessas consultas, o plano de execução ideal depende se um valor de parâmetro está NULL em tempo de execução. O termo parâmetros opcionais refere-se a uma variação específica do problema de plano sensível a parâmetros (PSP), em que o valor do parâmetro em tempo de execução determina se a consulta requer uma busca ou uma verificação.

Visão geral

As consultas que usam parâmetros opcionais geralmente incluem predicados que aplicam filtros condicionalmente com base em se um valor de parâmetro é fornecido. Um padrão comum é o seguinte:

SELECT column1,
       column2
FROM Table1
WHERE (column1 = @p
       OR @p IS NULL);

Quando @p IS NOT NULL, uma busca por índice em col1 geralmente é o plano de execução mais eficiente. Quando @p IS NULL, o predicado é avaliado TRUE e uma varredura pode ser mais apropriada. Sem OPPO, o Mecanismo de Banco de Dados do SQL Server deve compilar e armazenar em cache um único plano de execução válido para ambos os casos. Como um plano baseado em busca não é válido quando @p IS NULL, o otimizador geralmente escolhe um plano conservador baseado em verificação para todas as execuções. Essa escolha pode resultar em opções de plano ineficientes e uso excessivo de recursos para execuções seletivas.

Técnicas tradicionais de dica, como OPTIMIZE FOR não são eficazes nesse cenário, porque o plano deve permanecer correto para ambos os estados de parâmetro.

O OPPO usa a infraestrutura de otimização de plano adaptável (Multiplan) introduzida com a otimização de PSP (Plano Sensível a Parâmetros). Essa infraestrutura gera e armazena em cache vários planos de execução para uma única instrução, o que permite que o OPPO faça suposições diferentes com base nos valores de parâmetro usados na consulta.

Terminologia e como ela funciona

A OPPO baseia-se na estrutura de otimização de plano adaptável (Multiplan), que também é usada pela Otimização do Plano Sensível a Parâmetros. Usando o Multiplan, o Mecanismo de Banco de Dados pode gerar e armazenar em cache vários planos de execução para uma única consulta.

Quando o Mecanismo de Banco de Dados detecta um padrão de parâmetro opcional qualificado, ele cria:

  • Um plano de dispatcher
  • Uma ou mais variantes de consulta, cada uma otimizada para um estado de valor de parâmetro específico

No momento da execução:

  • O Mecanismo de Banco de Dados avalia o valor do parâmetro.
  • O dispatcher Multiplan seleciona a variante de consulta apropriada.
  • A variante de consulta selecionada é executada.

Depois que o Mecanismo de Banco de Dados seleciona uma variante de consulta, ele simplifica predicados com base no valor real do parâmetro. Considere a seguinte expressão:

@p1 IS NULL

Neste exemplo, a expressão é simplificada para um resultado constante para a variante selecionada. O dobramento constante de resultados permite que o otimizador gere planos de execução que não são efetivos em um plano reutilizável único.

Ao selecionar planos dessa forma, OPPO possibilita a execução eficiente para diferentes estados de parâmetro sem a necessidade de reescritas de consulta ou instruções de consulta manuais.

As otimizações OPPO e PSP abordam diferentes variações de problemas de plano relacionados a parâmetros:

  • A otimização PSP seleciona planos com base nas diferenças de cardinalidade estimadas para predicados de igualdade ou intervalo.

  • O OPPO seleciona planos com base em se um valor de parâmetro é NULL.

Uma única consulta pode se beneficiar de um ou ambos os recursos, dependendo dos predicados envolvidos.

Padrões de consulta com suporte

A otimização de plano de parâmetro opcional se aplica a consultas NULL em que verificações em parâmetros afetam a validade do plano de execução. Por exemplo, considere um formulário web de um aplicativo para uma empresa imobiliária que permite filtragem opcional no número de quartos para um anúncio específico. OPPO se aplica a predicados de parâmetros opcionais disjuntivos, como os seguintes:

SELECT *
FROM Properties
WHERE bedrooms = @bedrooms
      OR @bedrooms IS NULL;

Mesmo que os marcadores de parâmetro possam detectar o @bedrooms = 10 parâmetro e você souber que a cardinalidade do número de quartos provavelmente será muito baixa, o otimizador não produzirá um plano que busque um índice que exista na coluna do quarto, pois esse não é um plano válido para o caso em @bedrooms que está NULL. O plano gerado não inclui uma verificação do índice.

Imagine se você pudesse reescrever essa consulta como duas instruções separadas. Dependendo do valor do runtime do parâmetro, você pode avaliar o seguinte exemplo:

IF @bedrooms IS NULL
    SELECT *
    FROM Properties;
ELSE
    SELECT *
    FROM Properties
    WHERE bedrooms = @bedrooms;

O recurso pode conseguir isso usando a infraestrutura Multiplan, que permite a criação de um plano de dispatcher que expede uma variante de consulta.

OPPO incorpora uma dica de consulta gerada pelo sistema PLAN PER VALUE nos metadados do plano (optional_predicate) para associar cada variante de consulta ao seu estado de parâmetro. Essa dica é gerada pelo sistema e inserida no texto da consulta do plano. Essa dica não é válida para uso por um aplicativo ou para ser aplicada manualmente.

Continuando com o exemplo anterior,

SELECT *
FROM Properties
WHERE bedrooms = @bedrooms
      OR @bedrooms IS NULL;

O OPPO pode gerar duas variantes de consulta que podem ter os seguintes atributos adicionados a elas dentro do XML Showplan:

  • @bedrooms é NULL. A variante de consulta reformula predicados com base no valor do parâmetro, permitindo que um plano baseado em varredura seja gerado.

    SELECT * FROM Properties PLAN PER VALUE(ObjectID = 1234, QueryVariantID = 1, optional_predicate(@bedrooms é NULL))

  • @bedrooms IS NOT NULL

    SELECT * FROM Properties WHERE bedrooms = @bedrooms PLAN PER VALUE(ObjectID = 1234, QueryVariantID = 2, optional_predicate(@bedrooms is NULL)

Usar otimização de plano de parâmetro opcional

Para habilitar o OPPO para um banco de dados, os seguintes pré-requisitos são necessários:

  • O banco de dados deve usar o nível de compatibilidade 170.
  • A OPTIONAL_PARAMETER_OPTIMIZATION configuração com escopo de banco de dados deve ser habilitada.

A OPTIONAL_PARAMETER_OPTIMIZATION configuração com escopo de banco de dados é habilitada por padrão, portanto, um banco de dados usando o nível de compatibilidade 170 (o padrão no SQL Server 2025 (17.x)) usa OPPO por padrão.

Você pode garantir que um banco de dados use OPPO no SQL Server 2025 (17.x) executando as seguintes instruções:

ALTER DATABASE [<database-name-placeholder>]
SET COMPATIBILITY_LEVEL = 170;

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;

Para desabilitar o OPPO para um banco de dados, desabilite a OPTIONAL_PARAMETER_OPTIMIZATION configuração com escopo de banco de dados:

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

Usar a otimização de plano de parâmetro opcional por meio de dicas de consulta

Use a dica de consulta DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION para desabilitar o OPPO em uma determinada consulta. Especifique a dica por meio da USE HINT cláusula. Para obter mais informações, consulte Dicas de consulta.

Essa dica funciona em qualquer nível de compatibilidade e substitui a configuração no escopo do OPTIONAL_PARAMETER_OPTIMIZATION banco de dados.

Especifique a DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION dica de consulta diretamente na consulta ou por meio de dicas do Query Store.

Eventos Estendidos

Use os seguintes eventos estendidos para solução de problemas e diagnóstico. Esses eventos não são necessários para usar o recurso.

  • optional_parameter_optimization_skipped_reason: ocorre quando o OPPO decide que uma consulta não está qualificada para otimização. Esse evento estendido segue o mesmo padrão que o parameter_sensitive_plan_optimization_skipped_reason evento usado pela otimização PSP. Como uma consulta pode gerar tanto a otimização PSP quanto variantes de consulta OPPO, verifique ambos os eventos para entender por que um ou nenhum dos recursos foi ativado.

    A consulta a seguir mostra todas as possíveis razões para o PSP ter sido ignorado:

    SELECT map_value
FROM sys.dm_xe_map_values
WHERE [name] = 'opo_skipped_reason_enum'
ORDER BY map_key;

  • query_with_optional_parameter_predicate: esse evento estendido segue o mesmo padrão que o query_with_parameter_sensitivity evento usado pela otimização PSP. Ele inclui os campos adicionais que estão disponíveis nas melhorias para otimização do PSP.

    Estes campos são exibidos:

    • o número de predicados que a funcionalidade considerou interessante,
    • mais detalhes em formato JSON sobre os predicados interessantes e
    • se o OPPO tem suporte para o predicado ou predicados.

Remarks

  • O ShowPlan XML para uma variante de consulta é semelhante ao exemplo a seguir. Os predicados que o recurso seleciona têm suas respectivas informações adicionadas à dica PLAN PER VALUE (optional_predicate).
<Batch>
  <Statements>
    <StmtSimple StatementCompId="4" StatementEstRows="1989" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="170" StatementSubTreeCost="0.0563916" StatementText="SELECT PropertyId, AgentId, ListingPrice, ZipCode, SquareFootage, &#xD;&#xA;           Bedrooms, Bathrooms, ListingDescription&#xD;&#xA;    FROM dbo.Property &#xD;&#xA;    WHERE (@AgentId IS NULL OR AgentId = @AgentId)&#xD;&#xA;      AND (@ZipCode IS NULL OR ZipCode = @ZipCode)&#xD;&#xA;      AND (@MinPrice IS NULL OR ListingPrice &gt;= @MinPrice)&#xD;&#xA;      AND (@HasDescription IS NULL OR &#xD;&#xA;           (@HasDescription = 1 AND ListingDescription IS NOT NULL) OR&#xD;&#xA;           (@HasDescription = 0 AND ListingDescription IS NULL)) option (PLAN PER VALUE(ObjectID = 1269579561, QueryVariantID = 7, optional_predicate(@MinPrice IS NULL),optional_predicate(@ZipCode IS NULL),optional_predicate(@AgentId IS NULL)))" StatementType="SELECT" QueryHash="0x2F701925D1202A9F" QueryPlanHash="0xBA0B2B1A18AF1033" RetrievedFromCache="true" StatementSqlHandle="0x09000033F4BE101B2EE46B1615A038D422710000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="1269579561" StatementParameterizationType="1" SecurityPolicyApplied="false">
      <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
      <Dispatcher>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@MinPrice] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@MinPrice" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@ZipCode] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@ZipCode" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@AgentId] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
      </Dispatcher>
      <QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="1" CompileCPU="1" CompileMemory="376" QueryVariantID="7">

  • Exemplo de saída do evento estendido query_with_optional_parameter_predicate:

    Field Value
    optional_parameter_optimization_supported True
    optional_parameter_predicate_count 3
    predicate_details {"Predicates":[{"Skewness":1005.53},{"Skewness":1989.00},{"Skewness":1989.00}]}
    query_type 193

Qualificação e limitações de consulta

O OPPO aplica-se somente a consultas qualificadas para otimização Multiplan. O recurso não é aplicado em cenários que incluem:

  • Consultas que usam variáveis locais em vez de parâmetros
  • Consultas compiladas com OPTION (RECOMPILE)
  • Consultas executadas com SET ANSI_NULLS OFF
  • Instruções parametrizadas automaticamente

Conteúdo relacionado

  • Last updated on