Condividi tramite

Ottimizzazione del piano per parametri opzionali (OPPO)

Si applica a:Si applica a: SQL Server 2025 (17.x) Anteprima Database SQL di AzureDatabase SQL in Microsoft Fabric

Il termine parametri facoltativi si riferisce a una variante specifica del problema del piano sensibile ai parametri (PSP) in cui il valore sensibile del parametro esistente durante l'esecuzione della query controlla se è necessario eseguire una ricerca in o analizzare una tabella. Un semplice esempio è simile al seguente:

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

In questo esempio SQL Server sceglie sempre un piano che analizza la tabella Table1, anche se è presente un indice in Table1(col1). Un piano di ricerca potrebbe non essere possibile con valori NULL. Le tecniche di hint per le query, ad esempio OPTIMIZE FOR, potrebbero non essere utili per questo tipo di problema di PSP perché attualmente non esiste un operatore che modifica dinamicamente una ricerca di un indice in un'analisi durante l'esecuzione. Questo tipo di combinazione di ricerca e scansione in fase di esecuzione potrebbe anche non essere efficace, perché è probabile che le stime della cardinalità relative a quell'operatore siano imprecise. Il risultato è una scelta di piano inefficiente e concessioni di memoria eccessive per query più complesse con modelli di query simili.

La funzionalità di ottimizzazione del parametro del piano facoltativo (OPPO) usa l'infrastruttura di ottimizzazione del piano adattivo (Multiplan) introdotta con il miglioramento dell'ottimizzazione del piano sensibile ai parametri, che genera più piani da una singola dichiarazione. Ciò consente alla funzionalità di effettuare presupposti diversi a seconda dei valori dei parametri usati nella query. Durante il tempo di esecuzione della query, OPPO seleziona il piano appropriato:

  • dove il valore del parametro IS NOT NULL, utilizza un piano di ricerca o qualcosa di più ottimizzato di un piano di scansione completo.
  • dove il valore del parametro è NULL, usa un piano di analisi.

Come parte della famiglia di funzionalità di ottimizzazione del piano adattivo che include l'ottimizzazione del piano sensibile ai parametri, OPPO fornisce una soluzione per il secondo componente delle funzionalità del set Multiplan, che copre le funzionalità di ricerca dinamica.

  • Predicati di uguaglianza

    WHERE column1 = @p

  • Ricerca dinamica

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

Terminologia e funzionamento

Termine Descrizione
Espressione dispatcher Questa espressione valuta la cardinalità dei predicati in base ai valori dei parametri di runtime e indirizza l'esecuzione a varianti di query diverse.
Piano del gestore Un piano contenente l'espressione dispatcher viene memorizzato nella cache per la query originale. Il piano dispatcher è essenzialmente una raccolta dei predicati selezionati dalla funzionalità, con alcuni dettagli aggiuntivi. Per ogni predicato selezionato alcuni dei dettagli inclusi nel piano dispatcher sono i valori limite superiore e inferiore. Questi valori vengono usati per dividere i valori dei parametri in bucket o intervalli diversi. Il piano dispatcher contiene anche le statistiche usate per calcolare i valori limite.
Variante di query Poiché il piano del dispatcher valuta la cardinalità dei predicati in base ai valori dei parametri di runtime e li raggruppa, genera query figlie separate da eseguire. Queste sottoquery sono denominate varianti di query. Le varianti di query dispongono di piani personalizzati nella cache dei piani e nel Query Store. In altre parole, usando varianti di query diverse, si ottiene l'obiettivo di più piani per una singola query.

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. Un antipattern comune può essere quello di esprimere il filtro facoltativo come:

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

Anche se il parametro @bedrooms = 10 viene rilevato dall'uso di marcatori di parametro e si sa che la cardinalità per il numero di camere da letto è probabilmente molto bassa, l'utilità di ottimizzazione non produce un piano che cerca su un indice che esiste nella colonna della camera da letto perché non è un piano valido per il caso in cui @bedrooms è NULL. Il piano generato non include un'analisi dell'indice.

Immagina che questo possa essere riscritto come due affermazioni separate. A seconda del valore di runtime del parametro, è possibile valutare un aspetto simile al seguente:

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

A tale scopo, è possibile usare l'infrastruttura di ottimizzazione del piano adattivo, che consente la creazione di un piano dispatcher che invia due varianti di query.

Analogamente all'intervallo di cardinalità predicato usato dall'ottimizzazione PSP, OPPO incorpora un hint per la query utilizzabile dal sistema con il testo della query del piano. Questo hint non è valido per l'uso da parte di un'applicazione o se si tenta di usarlo 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 della query ha riformulato la query originale per ottenere un piano di 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. Ciò significa che un database che usa il livello di compatibilità 170 (impostazione predefinita in SQL Server 2025) usa OPPO per impostazione predefinita.

È possibile assicurarsi che un database usi OPPO in SQL Server 2025 eseguendo le istruzioni seguenti:

ALTER DATABASE [<database-name-placeholder>] SET COMPATIBILITY_LEVEL = 170;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;

Per disabilitare l'ottimizzazione del piano di parametri facoltativa 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 dei parametri opzionali tramite suggerimenti per la query

È possibile utilizzare l'hint DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION per la query per disabilitare l'ottimizzazione del piano di parametri facoltativi per una determinata query. Gli hint devono essere specificati tramite la USE HINT clausola . Per ulteriori informazioni, vedere i suggerimenti di query .

Gli hint funzionano con qualsiasi livello di compatibilità e sostituiscono la configurazione con ambito database OPTIONAL_PARAMETER_OPTIMIZATION.

L'hint della DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION query può essere specificato direttamente nella query o tramite gli hint del Query Store.

Eventi estesi

  • 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 DI PSP. Poiché una query può generare sia varianti di ottimizzazione PSP che OPPO, dovresti verificare entrambi gli eventi per capire perché si è attivata una o nessuna delle funzionalità. 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: l'evento esteso segue lo stesso modello dell'evento query_with_parameter_sensitivity utilizzato dall'ottimizzazione PSP. Include i campi aggiuntivi disponibili nei miglioramenti per l'ottimizzazione PSP, che consiste nel visualizzare il numero di predicati che la funzionalità ha trovato interessante, maggiori dettagli in formato JSON relativi ai predicati interessanti, nonché se OPPO è supportato per il predicato o predicati.

Osservazioni:

  • Il codice XML ShowPlan per una variante di query sarà simile all'esempio seguente, in cui i predicati selezionati 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">
  • Esempio di output dell'evento query_with_optional_parameter_predicate esteso
Campo Valore
ottimizzazione_parametro_opzionale_supportata Vero
conteggio_predicati_parametro_opzionale 3
dettagli_predicato {"Predicati":[{"Asimmetria":1005.53},{"Asimmetria":1989.00},{"Asimmetria":1989.00}]}
tipo di query 193

Contenuti correlati