Condividi tramite

Ottimizzazione del piano per parametri opzionali (OPPO)

Si applica a: SQL Server 2025 (17.x) Azure SQL DatabaseSQL database in Microsoft Fabric

L'ottimizzazione dei piani di query con parametri facoltativi (OPPO) migliora la qualità dei piani di query per le query che includono parametri facoltativi. In queste query, il piano di esecuzione ottimale dipende dal fatto che un valore del parametro sia NULL in fase di esecuzione. Il termine parametri facoltativi si riferisce a una variante specifica del problema del piano sensibile ai parametri (PSP), in cui il valore del parametro in fase di esecuzione determina se la query richiede una ricerca o un'analisi.

Informazioni generali

Le query che usano parametri facoltativi spesso includono predicati che applicano in modo condizionale filtri in base al fatto che venga fornito un valore di parametro. Un modello comune è il seguente:

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

Quando @p IS NOT NULL, una ricerca di indice su col1 è spesso il piano di esecuzione più efficiente. Quando @p IS NULL, il predicato restituisce TRUE e una scansione potrebbe essere più appropriata. Senza OPPO, il motore di database di SQL Server deve compilare e memorizzare nella cache un singolo piano di esecuzione valido per entrambi i casi. Poiché un piano basato sulla ricerca non è valido quando @p IS NULL, l'ottimizzatore spesso sceglie un piano basato sull'analisi conservativa per tutte le esecuzioni. Questa scelta può comportare scelte di piano inefficienti e un utilizzo eccessivo delle risorse per le esecuzioni selettive.

Le tecniche di hint tradizionali, OPTIMIZE FOR ad esempio non sono valide in questo scenario, perché il piano deve rimanere corretto per entrambi gli stati dei parametri.

OPPO usa l'infrastruttura di ottimizzazione del piano adattivo (Multiplan) introdotta con l'ottimizzazione PSP (Parameter Sensitive Plan). Questa infrastruttura genera e memorizza nella cache più piani di esecuzione per una singola istruzione, che consente a OPPO di effettuare presupposti diversi in base ai valori dei parametri usati nella query.

Terminologia e funzionamento

OPPO si basa sul framework di ottimizzazione del piano adattivo (Multiplan), che viene utilizzato anche dall'ottimizzazione del piano sensibile ai parametri. Usando multiplan, il motore di database può generare e memorizzare nella cache più piani di esecuzione per una singola query.

Quando il motore di database rileva un modello di parametro facoltativo idoneo, crea:

  • Piano del dispatcher
  • Una o più varianti di query, ognuna ottimizzata per uno stato di valore del parametro specifico

In fase di esecuzione:

  • Il motore di database valuta il valore del parametro.
  • Il dispatcher Multiplan seleziona la variante di query appropriata.
  • Viene eseguita la variante di query selezionata.

Dopo aver selezionato una variante di query, il motore di database semplifica i predicati in base al valore effettivo del parametro. Si consideri l'espressione seguente:

@p1 IS NULL

In questo esempio l'espressione viene semplificata in un risultato costante per la variante selezionata. Questa unificazione costante dei risultati consente all'ottimizzatore di generare piani di esecuzione che non sarebbero validi in un unico piano riutilizzabile.

Selezionando i piani in questo modo, OPPO consente un'esecuzione efficiente per diversi stati di parametro senza richiedere la riscrittura della query o suggerimenti manuali per le query.

L'ottimizzazione di OPPO e PSP risolve diverse variazioni dei problemi di piano correlati ai parametri:

  • L'ottimizzazione PSP seleziona i piani in base alle differenze di cardinalità stimate per predicati di uguaglianza o di intervallo.

  • OPPO seleziona i piani in base al fatto che il valore di un parametro sia NULL.

Una singola query può trarre vantaggio da entrambe le funzionalità o a seconda dei predicati coinvolti.

Modelli di query supportati

L'ottimizzazione del piano dei parametri facoltativa si applica alle query in cui NULL i controlli sui parametri influiscono sulla validità del piano di esecuzione. Si consideri, ad esempio, un modulo Web dell'applicazione per una società realty che consente di filtrare facoltativamente il numero di camere da letto per un determinato elenco. OPPO si applica ai predicati dei parametri facoltativi disgiuntivi, ad esempio:

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

Anche se i marcatori di parametro possono individuare il @bedrooms = 10 parametro e si sa che la cardinalità per il numero di camere da letto è probabilmente molto bassa, l'ottimizzatore non produce un piano che cerca su un indice esistente sulla colonna delle camere da letto, perché non è un piano valido per il caso in cui @bedrooms è NULL. Il piano generato non include un'analisi dell'indice.

Immagina se potessi trasformare questa query in due istruzioni separate. A seconda del valore di runtime del parametro, è possibile valutare l'esempio seguente:

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

La funzionalità può ottenere questo risultato usando l'infrastruttura Multiplan, che consente la creazione di un piano dispatcher che instrada una variante di query.

OPPO incorpora un hint di query generato dal sistema PLAN PER VALUE (optional_predicate) nei metadati del piano per associare ogni variante di query al relativo stato dei suoi parametri. Questo suggerimento è generato dal sistema e incorporato nel testo della query del piano. Questo hint non è valido per l'uso da parte di un'applicazione o per l'applicazione manualmente.

Continuando con l'esempio precedente,

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

OPPO può generare due varianti di query che potrebbero avere gli attributi seguenti aggiunti all'interno del file XML Showplan:

  • @bedrooms è NULL. La variante di query aggruppa i predicati in base al valore del parametro, consentendo la generazione di un piano basato su scansione.

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

  • @bedrooms IS NOT NULL

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

Usare l'ottimizzazione facoltativa del piano dei parametri

Per abilitare OPPO per un database, sono necessari i prerequisiti seguenti:

  • Il database deve usare il livello di compatibilità 170.
  • La OPTIONAL_PARAMETER_OPTIMIZATION configurazione con ambito database deve essere abilitata.

La OPTIONAL_PARAMETER_OPTIMIZATION configurazione con ambito database è abilitata per impostazione predefinita, quindi un database che usa il livello di compatibilità 170 (impostazione predefinita in SQL Server 2025 (17.x)) usa OPPO per impostazione predefinita.

È possibile assicurarsi che un database usi OPPO in SQL Server 2025 (17.x) eseguendo le istruzioni seguenti:

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

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;

Per disabilitare OPPO per un database, disabilitare la OPTIONAL_PARAMETER_OPTIMIZATION configurazione con ambito database:

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

Usare l'ottimizzazione del piano di parametri facoltativa tramite hint per le query

Usare il DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION suggerimento di query per disabilitare OPPO per una determinata query. Specificare l'hint tramite la USE HINT clausola . Per ulteriori informazioni, vedere i suggerimenti di query .

Questo hint funziona con qualsiasi livello di compatibilità e sovrascrive la configurazione con ambito database OPTIONAL_PARAMETER_OPTIMIZATION.

Specificare l'hint della DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION query direttamente nella query o tramite gli hint del Query Store.

Eventi estesi

Usare i seguenti eventi estesi per la risoluzione di problemi e diagnosi. Questi eventi non sono necessari per usare la funzionalità.

  • optional_parameter_optimization_skipped_reason: si verifica quando OPPO decide che una query non è idonea per l'ottimizzazione. Questo evento esteso segue lo stesso modello dell'evento parameter_sensitive_plan_optimization_skipped_reason utilizzato dall'ottimizzazione PSP. Dal momento che una query può generare sia un'ottimizzazione PSP che varianti di query OPPO, controllare entrambi gli eventi per capire perché una o entrambe le funzionalità non siano state attivate.

    La query seguente mostra tutti i possibili motivi per cui viene ignorata l'ottimizzazione piano sensibile ai parametri:

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

  • query_with_optional_parameter_predicate: questo evento esteso segue lo stesso modello dell'evento usato dall'ottimizzazione query_with_parameter_sensitivity PSP. Include i campi aggiuntivi disponibili nei miglioramenti per l'ottimizzazione di PSP.

    Questi campi vengono visualizzati:

    • numero di predicati che la funzionalità ha trovato interessante,
    • altri dettagli in formato JSON relativi ai predicati interessanti e
    • se OPPO è supportato per il predicato o i predicati.

Remarks

  • Il codice XML ShowPlan per una variante di query è simile all'esempio seguente. I predicati selezionati dalla funzionalità hanno le rispettive informazioni aggiunte all'hint 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">

  • Output di esempio dell'evento query_with_optional_parameter_predicate esteso:

    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

Ammissibilità e limitazioni delle query

OPPO si applica solo alle query idonee per l'ottimizzazione Multiplan. La funzionalità non viene applicata negli scenari che includono:

  • Query che usano variabili locali anziché parametri
  • Query compilate con OPTION (RECOMPILE)
  • Query eseguite con SET ANSI_NULLS OFF
  • Istruzioni con parametri automatici

Contenuti correlati

  • Last updated on