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

Aplica-se a: SQL Server 2025 (17.x) Banco de Dados SQL do Azurebanco de dados SQL no Microsoft Fabric

O termo parâmetros opcionais refere-se a uma variação específica do problema de PSP (plano sensível a parâmetros ) no qual o valor de parâmetro confidencial que existe durante a execução da consulta controla se precisamos executar uma busca ou verificar uma tabela. Um exemplo simples seria algo como:

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

Neste exemplo, o SQL Server sempre escolhe um plano que varre a tabela Table1, mesmo que haja um índice em Table1(col1). Um plano de busca pode não ser possível com valores nulos. Técnicas de dica de consulta, como OPTIMIZE FOR, podem não ser úteis para esse tipo de problema de PSP, pois atualmente não existe nenhum operador que mude de forma dinâmica uma busca de índice para uma varredura durante a execução. Esse tipo de combinação de busca-varredura> em tempo de execução também pode não ser eficaz, porque as estimativas de cardinalidade por cima desse operador provavelmente seriam imprecisas. O resultado são opções de plano ineficientes e concessões excessivas de memória para consultas mais complexas com padrões de consulta semelhantes.

O recurso de otimização de plano de parâmetro opcional (OPPO) utiliza a infraestrutura de otimização de plano adaptativo (Multiplan), que foi introduzida com a melhoria da otimização do Plano Sensível a Parâmetros e que gera múltiplos planos a partir de uma única instrução. Isso permite que o recurso faça suposições diferentes dependendo dos valores de parâmetro usados na consulta. Durante o tempo de execução da consulta, o OPPO seleciona o plano apropriado:

• onde o valor do parâmetro IS NOT NULL usa um plano de busca ou algo mais otimizado do que um plano de varredura completa.
• onde o valor do parâmetro é NULL, ele usa um plano de escaneamento.

Como parte da família de recursos de otimização de plano adaptável, que inclui otimização de Plano Sensível a Parâmetros, a OPPO oferece uma solução para o segundo componente do conjunto de funcionalidades multiplano, que abrange funcionalidades de busca dinâmica.

• Predicados de igualdade

  WHERE column1 = @p
  

• Pesquisa dinâmica

  WHERE (column1 = @p1 OR @p1 IS NULL)
    AND (column2 = @p2 OR @p2 IS NOT NULL)
  

Terminologia e como ela funciona

Term	Description
Expressão do dispatcher	Essa expressão avalia a cardinalidade de predicados com base em valores de parâmetro de runtime e roteia a execução para diferentes variantes de consulta.
Plano do dispatcher	Um plano que contém a expressão dispatcher é armazenado em cache para a consulta original. O plano do despachante é essencialmente uma coleção dos predicados que foram selecionados pela funcionalidade, com alguns detalhes adicionais. Para cada predicado selecionado, alguns dos detalhes incluídos no plano do despachante são os valores de limite alto e baixo. Esses valores são usados para dividir valores de parâmetro em diferentes buckets ou intervalos. O plano do dispatcher também contém as estatísticas que foram usadas para calcular os valores de limite.
Variante de consulta	À medida que o plano do dispatcher avalia a cardinalidade de predicados com base nos valores de parâmetro de runtime, ele os bucketiza e gera consultas filho separadas a serem executadas. Essas consultas filho são chamadas de variantes de consulta. As variantes de consulta têm seus próprios planos no cache de planos e no Repositório de Consultas. Em outras palavras, usando diferentes variantes de consulta, alcançamos a meta de vários planos para uma única consulta.

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. Um antipadrão comum pode ser expressar o filtro opcional como:

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

Mesmo que o parâmetro @bedrooms = 10 seja detectado pelo uso de marcadores de parâmetro e saibamos que a cardinalidade para o número de quartos provavelmente será muito baixa, o otimizador não produzirá um plano que procure um índice existente na coluna de quarto, pois esse não é um plano válido para a situação em que @bedrooms é NULL. O plano gerado não inclui uma verificação do índice.

Imagine se isso pudesse ser reescrito como duas instruções separadas. Dependendo do valor do runtime do parâmetro, podemos avaliar algo assim:

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

Podemos conseguir isso usando a infraestrutura de otimização de plano adaptável, que permite a criação de um plano de dispatcher que expedi duas variantes de consulta.

Semelhante à faixa de cardinalidade de predicado que a otimização PSP utiliza, o OPPO insere uma dica de consulta que pode ser usada pelo sistema junto com o texto da consulta do plano. Essa dica não é válida para uso por um aplicativo ou se você tentar usá-la por conta própria.

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 dobrou a consulta original para obter um plano de verificação.

  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. Isso significa que um banco de dados usando o nível de compatibilidade 170 (o padrão no SQL Server 2025) usa OPPO por padrão.

Você pode garantir que um banco de dados use OPPO no SQL Server 2025 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 a otimização de plano de parâmetro opcional para um banco de dados, desabilite a configuração com escopo no banco de dados OPTIONAL_PARAMETER_OPTIMIZATION.

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

Você pode usar o DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION indicador de consulta para desabilitar a otimização do plano de parâmetro opcional para uma determinada consulta. As dicas devem ser especificadas por meio da USE HINT cláusula. Para obter mais informações, consulte Dicas de consulta.

As dicas funcionam em qualquer nível de compatibilidade e substituem a configuração no escopo do banco de dados OPTIONAL_PARAMETER_OPTIMIZATION.

A dica de consulta DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION pode ser especificada diretamente na consulta ou por meio de Repositório de Consultas.

Eventos Estendidos

• 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 evento parameter_sensitive_plan_optimization_skipped_reason, utilizado no processo de otimização do PSP. Como uma consulta pode gerar tanto a otimização PSP quanto variantes de consulta OPPO, seria recomendável verificar ambos os eventos a fim de compreender 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: O evento estendido segue o mesmo padrão que o evento Query_with_Parameter_Sensitivity usado pela otimização PSP. Ele inclui os campos adicionais disponíveis nas melhorias para a otimização do PSP, que consistem em exibir o número de predicados que a funcionalidade considerou interessantes, fornecer mais detalhes sobre os predicados interessantes no formato JSON, assim como indicar se há suporte para OPPO para o predicado ou predicados.

Remarks

• O XML do ShowPlan para uma variante de consulta seria semelhante ao exemplo a seguir, onde os predicados selecionados têm suas respectivas informações adicionadas ao PLANO POR VALOR, sugestão de predicado opcional.

<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 resultado 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

Conteúdo relacionado

• Guia de arquitetura de processamento de consultas
• Recompilando planos de execução
• Reutilização de parâmetros e plano de execução
• Parametrização simples
• Parametrização forçada
• Dicas de consulta (Transact-SQL)
• Processamento inteligente de consultas em bancos de dados SQL
• Sensibilidade do parâmetro
• ALTERAR CONFIGURAÇÃO ESPECÍFICA DO BANCO DE DADOS (Transact-SQL)