Ottimizzazione dei piani sensibili ai parametri

Si applica a: SQL Server 2022 (16.x) e versioni successive Azure SQL DatabaseIstanza Gestita di SQL di AzureDatabase SQL in Microsoft Fabric

L'ottimizzazione del piano sensibile ai parametri (PSP) fa parte della famiglia di funzionalità di elaborazione di query intelligenti. Fa riferimento allo scenario in cui un singolo piano memorizzato nella cache per una query con parametri non risulta ottimale per tutti i possibili valori dei parametri in ingresso. Questa situazione si verifica in caso di distribuzioni di dati non uniformi. Per altre informazioni, vedere Sensibilità dei parametri e Parametri e riutilizzo del piano di esecuzione.

Per altre informazioni sulle soluzioni alternative esistenti per questo scenario di problema, vedere:

• Analizzare e risolvere i problemi sensibili ai parametri
• Parametri e riutilizzo del piano di esecuzione
• Query con problemi relativi al piano sensibile ai parametri (PSP)

L'ottimizzazione PSP abilita automaticamente più piani attivi memorizzati nella cache per una singola istruzione con parametri. I piani di esecuzione memorizzati nella cache supportano dimensioni di dati diverse in base ai valori dei parametri di runtime forniti dal cliente.

Comprendere la parametrizzazione

In motore di database SQL Server l'uso di parametri o di marcatori di parametro nelle istruzioni di Transact-SQL (T-SQL) consente di aumentare la capacità del motore relazionale di trovare una corrispondenza tra le nuove istruzioni T-SQL e i piani di esecuzione esistenti compilati in precedenza. Per altre informazioni, vedere Parametrizzazione semplice.

È anche possibile ignorare il comportamento predefinito parametrizzazione semplice di SQL Server specificando la parametrizzazione di tutte le istruzioni SELECT, INSERT, UPDATE e DELETE di un database in base a limiti specifici. Per altre informazioni, vedere Parametrizzazione forzata.

Implementazione dell'ottimizzazione PSP

Durante la compilazione iniziale, gli istogrammi delle statistiche delle colonne identificano le distribuzioni non uniformi e valutano i predicati con parametri più a rischio, fino a tre predicati su tutti quelli disponibili. In altre parole, se più predicati all'interno della stessa query soddisfano i criteri, l'ottimizzazione PSP sceglierà i primi tre. La funzionalità PSP limita il numero di predicati valutati, per evitare di gonfiare la cache dei piani e il Query Store, se questo è abilitato, con troppi piani.

Per i piani idonei, la compilazione iniziale produce un piano dispatcher contenente la logica di ottimizzazione PSP, chiamata espressione dispatcher. Un dispatcher esegue il mapping delle varianti di query in base ai predicati dei valori limite della gamma di cardinalità.

Terminology

Espressione dispatcher

Valuta la cardinalità dei predicati in base ai valori dei parametri di runtime e indirizza l'esecuzione a varianti di query diverse.

Piano di instradamento

Un piano che contiene l'espressione del dispatcher viene salvato 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 di limite alto e basso. Questi valori vengono usati per dividere i valori dei parametri in categorie o intervalli diversi. Il piano dispatcher contiene anche le statistiche usate per calcolare i valori limite.

Variante di query

Un piano dispatcher che valuta la cardinalità dei predicati in base ai valori dei parametri di runtime, suddivide in blocchi tali valori e genera query figlie separate per la compilazione e l’esecuzione. Queste sottoquery sono chiamate varianti di query. Le varianti di query hanno i propri piani nella cache dei piani e nel Query Store.

Intervallo di cardinalità del predicato

Durante il runtime, la cardinalità di ogni predicato viene valutata in base ai valori dei parametri di runtime. Il dispatcher suddivide i valori di cardinalità in tre intervalli di cardinalità dei predicati durante la fase di compilazione. Ad esempio, la funzionalità di ottimizzazione PSP può creare tre intervalli che rappresentano intervalli di cardinalità bassi, medi e alti, come illustrato nel diagramma seguente.

In altre parole, quando viene inizialmente compilata una query con parametri, la funzionalità di ottimizzazione PSP genererà un piano shell noto come piano dispatcher. L'espressione del dispatcher ha la logica che raggruppa le query in varianti di query in base ai valori di runtime dei parametri. Quando inizia l'esecuzione effettiva, il dispatcher esegue due passaggi:

• Il dispatcher valuta l'espressione dispatcher per il set specificato di parametri per calcolare l'intervallo di cardinalità.

• Il dispatcher mappa questi intervalli a varianti di query specifiche e le compila ed esegue. A causa della presenza di più varianti di query, la funzionalità di ottimizzazione PSP ottiene più piani per una singola query.

I limiti dell'intervallo di cardinalità possono essere visualizzati all'interno del codice XML ShowPlan di un piano dispatcher:

<Dispatcher>
  <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1000000">
    <StatisticsInfo Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Statistics="[NCI_Property_AgentId]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-09-07T15:32:16.89" />
    <Predicate>
      <ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
        <Compare CompareOp="EQ">
          <ScalarOperator>
            <Identifier>
              <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
            </Identifier>
          </ScalarOperator>
          <ScalarOperator>
            <Identifier>
              <ColumnReference Column="@AgentId" />
            </Identifier>
          </ScalarOperator>
        </Compare>
      </ScalarOperator>
    </Predicate>
  </ParameterSensitivePredicate>
</Dispatcher>

Un hint generato dall'ottimizzazione PSP viene aggiunto all'istruzione SQL nel ShowPlan XML di una variante di query. L'hint non può essere usato direttamente e non viene analizzato se aggiunto manualmente. L'hint include gli elementi seguenti:

opzione ( PIANO PER VALORE ( ObjectID = (int), QueryVariantID = (int), predicate_range ( [databaseName].[schemaName].[tableName].[columnName] = @paramName, lowBoundaryValue, highBoundaryValue ) ) )

• ObjectID deriva dal modulo (ovvero stored procedure, funzione, trigger) che include l'istruzione corrente, presupponendo che l'istruzione sia stata generata da un modulo. Se l'istruzione è il risultato di SQL dinamico o ad hoc, ovvero sp_executesql, l'elemento ObjectID è uguale a 0.
• QueryVariantID equivale approssimativamente alla combinazione di intervalli per tutti i predicati selezionati dall'ottimizzazione PSP. Ad esempio, se una query ha due predicati idonei per il piano sensibile ai parametri (PSP) e ogni predicato ha tre intervalli, ci saranno nove varianti di intervalli della query numerate da 1 a 9.
• intervallo di cardinalità dei predicati è l'informazione sull'intervallo di cardinalità dei predicati generato dall'espressione dispatcher.

Inoltre, all'interno del codice XML ShowPlan di una variante di query (all'interno dell'elemento Dispatcher):

<Batch>
  <Statements>
    <StmtSimple StatementText="SELECT PropertyId,&#xD;&#xA;       AgentId,&#xD;&#xA;       MLSLinkId,&#xD;&#xA;       ListingPrice,&#xD;&#xA;       ZipCode,&#xD;&#xA;       Bedrooms,&#xD;&#xA;       Bathrooms&#xD;&#xA;FROM dbo.Property&#xD;&#xA;WHERE AgentId = @AgentId&#xD;&#xA;ORDER BY ListingPrice DESC option (PLAN PER VALUE(ObjectID = 613577224, QueryVariantID = 1, predicate_range([PropertyMLS].[dbo].[Property].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090085E4372DFC69BB9E7EBA421561DE8E1E0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="0.021738" StatementEstRows="3" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x476167A000F589CD" QueryPlanHash="0xDE982107B7C28AAE" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160">
      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />

      <Dispatcher>
        <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
          <StatisticsInfo LastUpdate="2019-09-07T15:32:16.89" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_Property_AgentId]" Table="[Property]" Schema="[dbo]" Database="[PropertyMLS]" />
          <Predicate>
            <ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
              <Compare CompareOp="EQ">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </ParameterSensitivePredicate>
      </Dispatcher>

    </StmtSimple>
  </Statements>
</Batch>

Remarks

• A partire da SQL Server 2025 (17.x) e compatibilità del database 170, l'ottimizzazione PSP include i quattro miglioramenti seguenti:

  • Supporto per istruzioni del linguaggio di manipolazione dei dati (DML), ad esempio DELETE, INSERT, MERGE e UPDATE.
  • Supporto espanso per tempdb.
  • Considerazioni aggiuntive in scenari in cui esistono più predicati idonei nella stessa tabella.
  • Modifiche apportate all'evento esteso query_with_parameter_sensitivity che includeva i campi interesting_predicate_count, max_skewness, psp_optimization_supported e query_type prima delle modifiche in SQL Server 2025 (17.x) e nella compatibilità del database 170. Ma ora includono i campi interesting_predicate_count, interesting_predicate_details, psp_optimization_supported e query_type. Per altre informazioni, vedere la sezione Eventi estesi .

• La funzionalità di ottimizzazione PSP attualmente funziona solo con predicati di uguaglianza.

• I piani dispatcher vengono ricompilati automaticamente in presenza di modifiche significative alla distribuzione dei dati. I piani varianti di query ricompilano in modo indipendente in base alle esigenze, come per qualsiasi altro tipo di piano di query, soggetti a eventi di ricompilazione predefiniti. Per altre informazioni sulla ricompilazione, vedere Ricompilazione dei piani di esecuzione.

• La sys.query_store_plan vista del catalogo di sistema di Query Store è stata modificata per distinguere tra un piano compilato normale, un piano dispatcher e un piano di variante di query. La nuova vista del catalogo di sistema di Query Store, sys.query_store_query_variant, contiene informazioni sulle relazioni padre-figlio tra le query con parametri originali (note anche come query padre), i piani del dispatcher e le relative varianti di query figlio.

• Quando sono presenti più predicati che fanno parte della stessa tabella, l'ottimizzazione PSP seleziona il predicato con il maggiore skew dei dati in base all'istogramma delle statistiche sottostanti. Ad esempio, con SELECT * FROM table WHERE column1 = @predicate1 AND column2 = @predicate2, poiché sia column1 = @predicate1 che column2 = @predicate2 provengono dalla stessa tabella, table1, solo il predicato più asimmetrico verrà valutato dalla funzionalità. Tuttavia, se la query di esempio prevede un operatore come UNION, il piano di valutazione dei parametri valuta più di un predicato. Ad esempio, se una query presenta caratteristiche simili a SELECT * FROM table WHERE column1 = @predicate UNION SELECT * FROM table WHERE column1 = @predicate, il PSP seleziona al massimo due predicati in questo caso, perché il sistema la tratta come se fossero due tabelle differenti. Lo stesso comportamento può essere osservato nelle query che si auto-collegano tramite alias di tabella.

• Il codice XML ShowPlan per una variante di query sarà simile al seguente esempio, in cui i due predicati selezionati hanno le rispettive informazioni aggiunte all'hint correlato a PLAN PER VALUE PSP.

  <Batch>
    <Statements>
      <StmtSimple StatementText="SELECT  b.PropertyId, &#xD;&#xA;       AgentId, &#xD;&#xA;       MLSLinkId, &#xD;&#xA;       ListingPrice, &#xD;&#xA;       ZipCode, &#xD;&#xA;       Bedrooms, &#xD;&#xA;       Bathrooms &#xD;&#xA;FROM dbo.AgentProperty a join  PropertyDetails b on a.PropertyId = b.PropertyId&#xD;&#xA;WHERE AgentId = @AgentId and  Property_id=@Property_id&#xD;&#xA;UNION&#xD;&#xA;          SELECT  c.PropertyId, &#xD;&#xA;       AgentId, &#xD;&#xA;       MLSLinkId, &#xD;&#xA;       ListingPrice, &#xD;&#xA;       ZipCode, &#xD;&#xA;       Bedrooms, &#xD;&#xA;       Bathrooms &#xD;&#xA;FROM dbo.AgentProperty c join  PropertyDetails d on c.PropertyId = d.PropertyId&#xD;&#xA;WHERE AgentId = @AgentId and  Property_id=@Property_id option (PLAN PER VALUE(ObjectID = 981578535, QueryVariantID = 9, predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0),predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090051FBCD918F8DFD60D324887356B422D90000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="29.2419" StatementEstRows="211837" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x6D2A4E407085C01E" QueryPlanHash="0x72101C0A0DD861AB" CardinalityEstimationModelVersion="160" BatchModeOnRowStoreUsed="true">
        <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
        <Dispatcher>
          <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
            <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" />
            <Predicate>
              <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [a].[AgentId]=[@AgentId]">
                <Compare CompareOp="EQ">
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[a]" Column="AgentId" />
                    </Identifier>
                  </ScalarOperator>
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Column="@AgentId" />
                    </Identifier>
                  </ScalarOperator>
                </Compare>
              </ScalarOperator>
            </Predicate>
          </ParameterSensitivePredicate>
          <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
            <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" />
            <Predicate>
              <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [c].[AgentId]=[@AgentId]">
                <Compare CompareOp="EQ">
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[c]" Column="AgentId" />
                    </Identifier>
                  </ScalarOperator>
                  <ScalarOperator>
                    <Identifier>
                      <ColumnReference Column="@AgentId" />
                    </Identifier>
                  </ScalarOperator>
                </Compare>
              </ScalarOperator>
            </Predicate>
          </ParameterSensitivePredicate>
        </Dispatcher>
        <QueryPlan CachedPlanSize="160" CompileTime="17" CompileCPU="17" CompileMemory="1856" QueryVariantID="9">
  

• È possibile influenzare le soglie di skewness correnti utilizzate dalla funzionalità di ottimizzazione PSP, con uno o più dei metodi seguenti:

  • Flag di traccia della stima della cardinalità (CE), come il flag di traccia 9481 (a livello globale, di sessione o di query)

  • Opzioni di configurazione con ambito database che tentano di ridurre il modello di stima della cardinalità in uso o influenzare le ipotesi che il modello di stima della cardinalità fa riguardanti l'indipendenza di più predicati. Ciò è particolarmente utile quando non esistono statistiche sulle colonne multiple, il che influisce sulla capacità dell'ottimizzazione PSP di valutare l'idoneità di tali predicati.

  • Per altre informazioni, vedere la sezione Aumento del presupposto di correlazione per più predicati del documento Ottimizzazione dei piani di query con lo strumento di stima della cardinalità di SQL Server 2014. Il più recente modello CE tenta di presupporre una certa correlazione e una minore indipendenza per la congiunzione e la disgiunzione dei predicati. L'uso del modello di stima della cardinalità legacy può influire su come si calcola la selettività dei predicati in uno scenario di join multicolonna. Questa operazione del genere deve essere considerata solo per scenari specifici e non è consigliabile usare il modello di Cardinalità Stimata legacy per la maggior parte dei carichi di lavoro.

• L'ottimizzazione PSP attualmente compila ed esegue ogni variante di query come nuova istruzione preparata; questo è uno dei motivi per cui le varianti di query perdono l'associazione con qualsiasi modulo padre object_id se il piano del dispatcher è basato su un modulo, ovvero stored procedure, trigger, funzione, vista e così via. In quanto istruzione preparata, object_id non è qualcosa che possa essere mappato direttamente su un oggetto in sys.objects, ma è essenzialmente un valore calcolato basato su un hash interno del testo batch. Per ulteriori informazioni, consultare la sezione Tabella Restituita della documentazione sys.dm_exec_plan_attributes DMV.

  I piani varianti di query vengono inseriti nell'archivio oggetti della cache dei piani (CACHESTORE_OBJCP) mentre i piani dispatcher vengono inseriti nell'archivio cache dei piani di SQL (CACHESTORE_SQLCP). La funzionalità PSP archivierà tuttavia l'elemento object_id del genitore di una variante di query all'interno dell'attributo ObjectID, parte dell'hint PLAN PER VALUE, che la funzionalità PSP aggiunge al file XML ShowPlan se la query genitore fa parte di un modulo e non è T-SQL dinamico o ad hoc. Le statistiche sulle prestazioni aggregate per le procedure, le funzioni e i trigger memorizzati nella cache possono continuare a essere usate per i rispettivi scopi. Statistiche correlate all'esecuzione più granulari, ad esempio quelle presenti in viste simili alla DMV sys.dm_exec_query_stats, contengono comunque dati per le varianti di query. Tuttavia, l'associazione tra object_id per le varianti di query e gli oggetti all'interno della tabella sys.objects non è attualmente allineata, senza elaborare ulteriormente il codice XML ShowPlan per ognuna delle varianti di query che richiedono statistiche di runtime più granulari. Se Query Store è abilitato, le informazioni sulle statistiche di runtime e attesa per le varianti di query possono essere ottenute da Query Store senza ulteriori tecniche di analisi XML ShowPlan.

• Poiché le varianti di query dei piani parametrizzati vengono eseguite come una nuova istruzione preparata, object_id non viene automaticamente esposto nelle varie DMV relative alla cache dei piani senza analizzare l'XML ShowPlan e applicare tecniche di corrispondenza dei modelli di testo (ovvero, elaborazione XQuery aggiuntiva). Solo i dispatcher di ottimizzazione PSP generano attualmente l'ID oggetto padre appropriato. object_id viene esposto all'interno di Query Store, poiché Query Store consente un modello più relazionale rispetto alla gerarchia della cache dei piani. Per altre informazioni, vedere la vista del catalogo di sistema di Query Store sys.query_store_query_variant.

Considerations

• Per abilitare l'ottimizzazione PSP, abilitare il livello di compatibilità del database 160 per il database a cui si è connessi durante l’esecuzione della query.

• Per ulteriori informazioni dettagliate sulla funzionalità di ottimizzazione PSP, è consigliabile abilitare l'integrazione Query Store attivando il Query Store. L'esempio seguente attiva Query Store per un database preesistente denominato MyNewDatabase:

ALTER DATABASE [MyNewDatabase]
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO
);

• Per disabilitare l'ottimizzazione del piano sensibile ai parametri a livello di database, utilizzare la configurazione con ambito del database ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF.

• Per disabilitare l'ottimizzazione PSP a livello di query, utilizzare il suggerimento di query DISABLE_PARAMETER_SENSITIVE_PLAN.

• Se il rilevamento dei parametri è disabilitato dal flag di traccia 4136, dalla configurazione con ambito database o dall'hint di query PARAMETER_SNIFFING, l'ottimizzazione PSP è disabilitata per i carichi di lavoro e i contesti di esecuzione associati. Per ulteriori informazioni, vedere Suggerimenti di query e ALTER DATABASE SCOPED CONFIGURATION.

• Il numero di varianti uniche di piano per dispatcher archiviato nella cache dei piani è limitato per evitare il gonfiore della cache. La soglia interna non è documentata. Poiché ogni batch SQL ha il potenziale di creazione di più piani e ogni piano di variante di query ha una voce indipendente nella cache dei piani, è possibile raggiungere il numero massimo predefinito di voci di piano consentite. Se la frequenza di rimozione della cache dei piani è osservabile elevata o le dimensioni degli CACHESTORE_OBJCPCACHESTORE_SQLCP e sono eccessive, è consigliabile applicare il flag di traccia 174.

• Il numero di varianti di piano univoche archiviate per una query nel Query Store è limitato dall'opzione di configurazione max_plans_per_query. Poiché le varianti di query possono avere più di un piano, per ogni query all'interno di Query Store può essere presente un totale di 200 piani. Questo numero comprende tutti i piani di varianti di query per tutti i dispatcher che appartengono a una query principale. Considerare di aumentare l'opzione di configurazione max_plans_per_query di Query Store.

  • Un esempio di come il numero di piani univoci può superare il limite predefinito max_plans_per_query di Query Store sarebbe uno scenario con il seguente comportamento. Supponiamo di avere una query con un ID di 10, che possiede due piani di dispatcher e ogni piano di dispatcher ha ciascuno 20 varianti di query (40 varianti di query in totale). Il numero totale di piani per l'ID query 10 è di 40: questi comprendono le varianti della query e i due piani del dispatcher. È anche possibile che la query padre stessa (ID query 10) abbia 5 piani normali (non dispatcher). Questo crea 47 piani (40 da varianti di query, 2 dispatcher e 5 piani non correlati a PSP). Inoltre, se ogni variante di query ha una media di cinque piani ciascuna, in questo scenario è possibile avere più di 200 piani nel Query Store per una query padre. Ciò dipenderebbe anche da una marcata asimmetria dei dati nei set di dati a cui fa riferimento questa query padre d'esempio.

• Per ogni variante di query che mappa a un dispatcher specifico:

  • query_plan_hash è univoco. Questa colonna è disponibile in sys.dm_exec_query_stats e in altre viste a gestione dinamica e tabelle del catalogo.
  • plan_handle è univoco. Questa colonna è disponibile in sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_plans e in altre viste e funzioni a gestione dinamica e nelle tabelle del catalogo.
  • query_hash è comune ad altre varianti di mapping allo stesso dispatcher, quindi è possibile determinare l'utilizzo aggregato delle risorse per le query che differiscono solo per i valori dei parametri di input. Questa colonna è disponibile in sys.dm_exec_query_stats, sys.query_store_query e in altre viste a gestione dinamica e nelle tabelle del catalogo.
  • sql_handle è univoco dovuto a speciali identificatori di ottimizzazione PSP aggiunti al testo della query durante la compilazione. Questa colonna è disponibile in sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_plans e in altre viste e funzioni a gestione dinamica e nelle tabelle del catalogo. Le stesse informazioni dell'handle sono disponibili in Query Store come colonna last_compile_batch_sql_handle nella tabella di catalogo sys.query_store_query.
  • query_id è univoco nel Query Store. Questa colonna è disponibile in sys.query_store_query e in altre tabelle del catalogo di Query Store.

Forzatura del piano di esecuzione in Query Store

Usa le stesse stored procedure sp_query_store_force_plan e sp_query_store_unforce_plan per operare con piani dispatcher o varianti.

Se viene forzata una variante, il dispatcher padre non viene forzato. Se un dispatcher è soggetto a forzatura, solo le varianti di tale dispatcher sono considerate idonee per essere utilizzate.

• Le varianti forzate in precedenza da altri dispatcher diventano inattive, ma mantengono lo stato forzato fino a quando il dispatcher non viene forzato di nuovo
• Le varianti forzate in precedenza nello stesso dispatcher che erano diventate inattive vengono forzate di nuovo

Comportamento degli hint delle query in Query Store

• Quando un hint di Query Store viene aggiunto a una variante di query (child query), lo si applica nello stesso modo di una query non-PSP. Gli hint per le varianti di una query hanno una precedenza più alta se è stato applicato anche un hint alla query principale nel Query Store.

• Quando un hint di Query Store viene aggiunto alla query padre e la query figlio (variante di query) non ha un hint di Query Store esistente, la query figlio (variante di query) eredita l'hint dalla query padre.

• Se un hint della query del Query Store viene rimosso dalla query padre, viene rimosso anche dalle query figlie (varianti di query).

• Se un RECOMPILE hint viene aggiunto alla query principale, il sistema genererà piani non-PSP una volta rimossi dalla cache dei piani tutti i piani varianti di query esistenti, poiché la funzionalità PSP non funziona sulle query che contengono un RECOMPILE hint.

• È possibile osservare i risultati degli hint di Query Store usando gli eventi estesi query_store_hints_application_success e gli eventi query_store_hints_application_failed. La tabella sys.query_store_query_hints contiene informazioni relative agli hint per la query applicati. Se l'hint è stato applicato solo a una query padre, il catalogo di sistema contiene le informazioni di hint per la query padre, ma non per le query figlio, anche se le query figlio ereditano l'hint della query padre.

Il piano basato sui parametri con suggerimenti di query e il comportamento di forzatura del piano può essere riepilogato nella tabella seguente.

¹ Componente di correzione automatica dello schema della funzionalità di ottimizzazione automatica

Eventi estesi

• parameter_sensitive_plan_optimization_skipped_reason: si verifica quando viene ignorata la funzionalità del piano sensibile ai parametri. Utilizzare questo evento per monitorare il motivo per cui l'ottimizzazione PSP viene ignorata.

  La query seguente mostra tutti i possibili motivi per cui è stata saltata la PSP.

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

• parameter_sensitive_plan_optimization: si verifica quando una query usa la funzionalità di ottimizzazione PSP. Solo canale di debug. Alcuni campi di interesse potrebbero essere:

  • is_query_variant: indica se è un piano dispatcher (padre) o un piano di variante di query (figlio)
  • predicate_count: numero di predicati selezionati da PSP
  • query_variant_id: visualizza l'ID variante di query. Il valore 0 indica che l'oggetto è un piano di dispacciamento (padre).

• query_with_parameter_sensitivity: Questo evento, quando viene attivato, visualizzerà il numero di predicati ritenuti interessanti dalla funzionalità, più dettagli sui predicati interessanti in formato JSON, nonché se il supporto per PSPO è previsto per il predicato o i predicati.

• Output di esempio dell'evento query_with_parameter_sensitivity esteso

Comportamento di SQL Server Audit

L'ottimizzazione PSP fornisce dati di audit per la dichiarazione del piano dispatcher e qualsiasi variante di query associata al dispatcher. La colonna additional_information all'interno di SQL Server Audit fornisce anche le informazioni di stack T-SQL appropriate per le varianti di query. Usando il database MyNewDatabase come esempio, se questo include una tabella denominata T2 e una stored procedure denominata usp_test, dopo l'esecuzione della stored procedure usp_test, il log di controllo potrebbe contenere le voci seguenti:

Problemi noti

Resolved

L'eccezione di violazione di accesso può verificarsi su repliche secondarie leggibili in determinate condizioni

(Risolto nel gennaio 2026)

Le query che soddisfano le condizioni seguenti potrebbero causare una violazione di accesso quando una variante di query di un PSP (Piano Sensibile ai Parametri) non riesce a determinare lo stato persistente dell'istruzione dispatcher padre.

• Eseguito in una replica secondaria
• Sensibile all'analisi dei parametri
• Elegibile per l'ottimizzazione sensibile ai parametri del piano

L'eccezione di violazione di accesso si verifica in Query Store in SQL Server 2022 in determinate condizioni

(Risolto nel marzo 2023)

Questo problema si è verificato a causa di una race condition che può verificarsi quando le statistiche di runtime per una query eseguita vengono persistenti dalla rappresentazione in memoria del Query Store (disponibile nel MEMORYCLERK_QUERYDISKSTORE_HASHMAP clerk di memoria) alla versione su disco del Query Store. Le statistiche di runtime, visualizzate come Statistiche runtime, sono mantenute in memoria per un periodo di tempo definito dall'opzione DATA_FLUSH_INTERVAL_SECONDS dell'istruzione SET QUERY_STORE (il valore predefinito è 15 minuti). È possibile usare la finestra di dialogo Query Store di Management Studio per immettere un valore per Intervallo di scaricamento dati (minuti), che viene convertito internamente in secondi. In caso di uso intenso della memoria nel sistema, le statistiche di runtime possono essere scaricate su disco prima di quanto definito con l’opzione DATA_FLUSH_INTERVAL_SECONDS. Quando thread aggiuntivi in background di Query Store, relativi alla pulizia del piano di query di Query Store, alle opzioni di Query Store STALE_QUERY_THRESHOLD_DAYS e/o alle opzioni di Query Store MAX_STORAGE_SIZE_MB, eseguono query dal Query Store, esiste uno scenario in cui una variante della query e/o la sua istruzione dispatcher associata può essere dereferenziata prematuramente. Ciò può comportare una violazione di accesso durante le operazioni di inserimento o eliminazione di varianti di query in Query Store.

Per altre informazioni sulle operazioni di Query Store, vedere la sezione Osservazioni dell'articolo: Come Query Store raccoglie i dati.

Contenuti correlati

• Ricompilazione dei piani di esecuzione
• Parametri e riutilizzo del piano di esecuzione
• Parametrizzazione semplice
• Parametrizzazione forzata
• Suggerimenti per le query (Transact-SQL)
• Elaborazione di query intelligenti nei database SQL
• Sensibilità dei parametri
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) - Configurazione a livello di database (Transact-SQL)