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

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

A otimização opcional do plano de parâmetros (OPPO) melhora a qualidade do plano de consulta para consultas que incluem parâmetros opcionais. Nestas consultas, o plano de execução ótimo depende de 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 do plano sensível ao parâmetro (PSP), em que o valor do parâmetro no momento de execução determina se a consulta requer uma pesquisa ou uma varridura.

Visão geral

Consultas que utilizam parâmetros opcionais frequentemente incluem predicados que aplicam filtros condicionalmente com base no valor do parâmetro fornecido ou não. 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 de índice em col1 é frequentemente o plano de execução mais eficiente. Quando @p IS NULL, o predicado avalia-se a TRUE, e uma análise pode ser mais apropriada. Sem o OPPO, o Motor de Base de Dados SQL Server tem de 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 frequentemente escolhe um plano conservador baseado em varrimento para todas as execuções. Esta escolha pode resultar em escolhas de plano ineficientes e num uso excessivo de recursos para execuções seletivas.

Técnicas tradicionais de hinting como OPTIMIZE FOR não são eficazes neste cenário, porque o plano deve manter-se correto para ambos os estados dos parâmetros.

O OPPO utiliza a infraestrutura de otimização adaptativa de planos (Multiplano) introduzida com a otimização do Plano Sensível ao Parâmetro (PSP). Esta infraestrutura gera e armazena em cache múltiplos planos de execução para uma única instrução, o que permite ao OPPO fazer diferentes suposições com base nos valores dos parâmetros usados na consulta.

Terminologia e funcionamento

O OPPO baseia-se no framework de otimização adaptativa de planos (Multiplan), que também é utilizado pela otimização de planos sensíveis a parâmetros. Ao usar o Multiplan, o Motor de Base de Dados pode gerar e armazenar em cache múltiplos planos de execução para uma única consulta.

Quando o Motor de Base de Dados deteta um padrão opcional de parâmetros elegível, cria:

• Um plano de distribuição
• 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 Motor de Base de Dados avalia o valor do parâmetro.
• O despachante Multiplan seleciona a variante de consulta apropriada.
• A variante de consulta selecionada é executada.

Depois de o Motor de Base de Dados selecionar uma variante de consulta, simplifica os 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. Este processo de dobragem constante de resultados permite ao otimizador gerar planos de execução que não são válidos dentro de um único plano reutilizável.

Ao selecionar planos desta forma, OPPO permite uma execução eficiente para diferentes estados de parâmetros sem necessidade de reescrita de queries ou dicas manuais.

A otimização do OPPO e PSP aborda diferentes variações de problemas relacionados com planos de parâmetros.

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

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

Uma única consulta pode beneficiar de ambas ou de qualquer uma das funcionalidades, dependendo dos predicados envolvidos.

Padrões de consulta suportados

A otimização do plano de parâmetros opcional aplica-se a consultas nas quais as verificações de parâmetros afetam a validade do plano de execução. 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. OPPO aplica-se a predicados de parâmetros opcionais disjuntivos, tais como:

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

Mesmo que os marcadores de parâmetros consigam detectar o @bedrooms = 10 parâmetro, e saiba que a cardinalidade para o número de quartos é provavelmente muito baixa, o otimizador não produz um plano que procure um índice que exista na coluna do quarto, porque esse não é um plano válido para o caso em que @bedrooms é NULL. O plano gerado não inclui uma verificação do índice.

Imagine se pudesse reescrever esta consulta como duas instruções separadas. Dependendo do valor em tempo de execução do parâmetro, pode avaliar o seguinte exemplo:

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

A funcionalidade pode conseguir isto utilizando a infraestrutura Multiplan, que permite a criação de um plano de dispatcher que despacha uma variante de consulta.

OPPO incorpora uma dica de consulta gerada pelo sistema PLAN PER VALUE (optional_predicate) nos metadados do plano para associar cada consulta variante ao seu estado de parâmetro. Esta dica é gerada pelo sistema e incorporada no texto da consulta do plano. Esta dica não é válida para uso por uma aplicação nem 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 do Showplan:

• @bedrooms é NULL. A variante de consulta agrega predicados com base no valor do parâmetro, permitindo gerar um plano baseado em 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 configuração com âmbito de base de dados OPTIONAL_PARAMETER_OPTIMIZATION está ativada automaticamente, portanto, uma base de dados com nível de compatibilidade 170 (o padrão no SQL Server 2025 (17.x)) usa OPPO como padrão.

Pode garantir que uma base de dados utiliza 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 desativar o OPPO para uma base de dados, desative a OPTIONAL_PARAMETER_OPTIMIZATION configuração com âmbito de base de dados:

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

Utilize a otimização de plano de parâmetro opcional através de dicas de consulta

Use a DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION sugestão de consulta para desativar o OPPO para uma consulta específica. Especifique a dica através da USE HINT cláusula. Para obter mais informações, consulte Dicas de consulta.

Esta sugestão funciona em qualquer nível de compatibilidade e substitui a OPTIONAL_PARAMETER_OPTIMIZATION configuração com escopo ao nível da base de dados.

Especifique a DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION pista de consulta diretamente na consulta ou a partir das dicas do Query Store.

Eventos Prolongados

Use os seguintes eventos estendidos para resolução de problemas e diagnóstico. Estes eventos não são obrigatórios para usar a funcionalidade.

• optional_parameter_optimization_skipped_reason: Ocorre quando a OPPO decide que uma consulta não é elegível para otimização. Este evento estendido segue o mesmo padrão que o parameter_sensitive_plan_optimization_skipped_reason evento utilizado pela otimização para PSP. Como uma consulta pode gerar tanto variantes de otimização PSP como de OPPO, verifique ambos os eventos para entender porque uma ou nenhuma funcionalidade foi ativada.

  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: Este evento estendido segue o mesmo padrão que o query_with_parameter_sensitivity evento utilizado pela otimização PSP. Inclui os campos adicionais disponíveis nas melhorias para otimização da PSP.

  Estes campos apresentam:

  • o número de predicados que a funcionalidade achou interessantes,
  • mais detalhes em formato JSON relativamente aos predicados interessantes, e
  • se o OPPO é compatível com o predicado ou os predicados.

Remarks

• O XML do ShowPlan para uma variante de consulta é semelhante ao seguinte exemplo. Os predicados que a funcionalidade seleciona têm a sua respetiva informação adicionada à PLAN PER VALUE dica (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

Elegibilidade e limitações para consultas

OPPO aplica-se apenas a consultas elegíveis para otimização do Multiplan. A funcionalidade não é aplicada em cenários que incluem:

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

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)