Otimização opcional do plano de parâmetros (OPPO)

Aplica-se a:Aplica-se a: SQL Server 2025 (17.x) Base de Dados Azure SQLBase de Dados SQL no Microsoft Fabric

O termo parâmetros opcionaisrefere-se a uma variação específica do problema do plano sensível a parâmetros (PSP) no qual o valor de parâmetro sensível que existe durante a execução da consulta, controla se precisamos executar uma busca ou varredura em 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 verifica a tabela Table1, mesmo que haja um índice no Table1(col1). Um plano de busca pode não ser possível com valores NULL. Técnicas de sugestão de consulta, como OPTIMIZE FOR, podem não ser úteis para esse tipo de problema de PSP porque atualmente não há um operador que altere dinamicamente uma busca de índice em uma verificação no decurso da execução. Esse tipo de combinação de busca e varredura> em execução também pode não ser eficaz, porque as estimativas de cardinalidade associadas a esse operador provavelmente serão imprecisas. O resultado são escolhas de plano ineficientes e concessões de memória excessivas 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 de otimização de plano sensível aos parâmetros e gera vários planos a partir de uma única instrução. Isso permite que o recurso faça diferentes suposições, dependendo dos valores de parâmetro usados na consulta. Durante o tempo de execução da consulta, a OPPO seleciona o plano apropriado:

• quando o valor do parâmetro IS NOT NULL, ele usa um plano de pesquisa ou algo mais eficaz do que um plano de verificação completa.
• onde o valor do parâmetro é NULL, ele usa um plano de varrimento.

Como parte da família de recursos de otimização de plano adaptativo que inclui a otimização de Plano Sensível a Parâmetros, a OPPO fornece uma solução para o segundo componente do conjunto de recursos Multiplan, que abrange recursos de pesquisa 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 funcionamento

Term	Description
Expressão do despachante	Esta expressão avalia a cardinalidade de predicados com base em valores de parâmetros de tempo de execução e encaminha a execução para diferentes variantes de consulta.
Plano do despachante	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 extras. Para cada predicado selecionado, alguns dos detalhes incluídos no plano do distribuidor incluem os valores de limite superior e inferior. Esses valores são usados para dividir os valores dos parâmetros em diferentes buckets ou intervalos. O plano do despachante também contém as estatísticas que foram usadas para calcular os valores limite.
Variante de consulta	À medida que o plano do dispatcher avalia a cardinalidade dos predicados com base nos valores dos parâmetros de tempo de execução, ele os classifica e gera consultas filhas separadas para serem executadas. Essas consultas filho são chamadas de variantes de consulta. As variantes de consulta têm os seus próprios planos no cache de planos e no Repositório de Consultas. Em outras palavras, usando diferentes variantes de consulta, alcançamos o objetivo de vários planos para uma única consulta.

Como exemplo, considere um formulário web de candidatura para uma empresa imobiliária que permita a filtragem opcional do número de quartos para um determinado anúncio. Um antipadrão comum poderia ser expressar o filtro opcional como:

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

Mesmo que o parâmetro @bedrooms = 10 seja identificado pelo uso de marcadores de parâmetros, e saibamos que a cardinalidade para o número de quartos provavelmente será muito baixa, o otimizador não produz um plano que utilize um índice existente na coluna de quartos porque esse não é um plano válido para o caso onde @bedrooms é NULL. O plano gerado não inclui uma verificação do índice.

Imagine se isso pudesse ser reescrito como duas declarações separadas. Dependendo do valor de tempo de execução do parâmetro, podemos avaliar algo assim:

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

Podemos alcançar isso utilizando a infraestrutura de otimização adaptativa de planos, que permite a criação de um plano de despacho que executa duas variantes de consulta.

Semelhante ao intervalo de cardinalidade de predicados utilizado pela otimização PSP, a OPPO incorpora no texto de consulta do plano uma dica de consulta que pode ser usada pelo sistema. Esta dica não é válida para uso por um aplicativo ou se você tentar usá-lo sozinho.

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 do Showplan:

• @bedrooms é NULL. A variante de consulta dobrou a consulta original para alcançar um plano de varredura.

  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 é nulo))

Utilizar a otimização de plano de parâmetros opcionais

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 do escopo do 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 desativar a otimização de parâmetro opcional para um banco de dados, desative a configuração de escopo do banco de dados OPTIONAL_PARAMETER_OPTIMIZATION.

ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

Use a otimização opcional do plano de parâmetros por meio de dicas de consulta

Você pode usar a dica de consulta DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION para desabilitar a otimização do plano de parâmetros opcionais para uma determinada consulta. As dicas devem ser especificadas através 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 de âmbito do banco de dados OPTIONAL_PARAMETER_OPTIMIZATION.

A DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION dica de consulta pode ser especificada diretamente na consulta ou através de dicas do Repositório de Consultas.

Eventos Prolongados

• optional_parameter_optimization_skipped_reason: Ocorre quando a OPPO decide que uma consulta não é elegível para otimização. Esse evento estendido segue o mesmo padrão do evento parameter_sensitive_plan_optimization_skipped_reason usado na otimização do PSP. Como uma consulta pode gerar variantes de otimização PSP e consulta OPPO, você deve verificar ambos os eventos para entender por que um ou nenhum recurso foi ativado.

  A consulta seguinte mostra todas as possíveis razões pelas quais a PSP foi ignorada:

  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 do PSP. Inclui os campos adicionais disponíveis nas melhorias da otimização do PSP, que incluem mostrar o número de predicados que a função considerou interessantes, fornecer informações detalhadas em formato JSON sobre os predicados interessantes, bem como indicar se o OPPO é suportado para o predicado ou predicados.

Remarks

• O XML ShowPlan para uma variante de consulta seria semelhante ao exemplo a seguir, onde os predicados selecionados têm suas respetivas 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 query_with_optional_parameter_predicate evento estendido

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 ao parâmetro
• ALTERAR A CONFIGURAÇÃO DE ESCOPO DA BASE DE DADOS (Transact-SQL)