Ottimizzazione dei piani sensibili ai parametri

Si applica a: SQL Server 2022 (16.x) e versioni successive, database SQL di Azure e Istanza gestita di SQL di Azure

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à parametri e Riutilizzo di piano di esecuzione e parametri.

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 del piano sensibile ai parametri 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.

Informazioni sulla 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 del piano sensibile ai parametri (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 piano dispatcher esegue il mapping alle varianti di query in base ai predicati dei valori limite dell'intervallo di cardinalità.

Terminologia

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 dispatcher

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

Variante di query

Come un piano dispatcher valuta la cardinalità dei predicati in base ai valori dei parametri di runtime, inserisce in contenitori tali valori e genera query figlio separate per la compilazione e l’esecuzione. Queste query figlio sono chiamate varianti di query. Le varianti di query hanno piani personalizzati 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 inserisce in contenitori i valori di cardinalità in tre intervalli di cardinalità dei predicati in 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 dispatcher dispone della logica che inserisce in contenitori 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 esegue il mapping di questi intervalli a varianti di query specifiche e compila ed esegue le varianti. 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 per l'ottimizzazione PSP viene aggiunta all'istruzione SQL nel file XML ShowPlan 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:

optzione ( PLAN PER VALUE ( 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 di piano sensibile ai parametri. Ad esempio, se una query ha due predicati idonei per piano sensibile ai parametri e ogni predicato ha tre intervalli, saranno presenti nove intervalli di varianti di query numerati da 1 a 9.
• intervallo di predicati è l'informazione dell’intervallo di cardinalità predicato 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>

Osservazioni:

• La funzionalità di ottimizzazione piano sensibile ai parametri 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 vista del catalogo di sistema di Query Store sys.query_store_plan (Transact-SQL) è 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 (Transact-SQL) 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 piano sensibile ai parametri seleziona il predicato con la maggior asimmetria 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, piano sensibile ai 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 piano sensibile ai parametri seleziona al massimo due predicati in questo caso, perché il sistema le considera come due tabelle diverse. Lo stesso comportamento può essere osservato dalle query che eseguono l’auto join tramite alias di tabella.

• Il codice XML ShowPlan per una variante di query sarà simile all'esempio seguente, in entrambi i predicati selezionati vengono aggiunte le rispettive informazioni 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 asimmetria correnti utilizzate dalla funzionalità di ottimizzazione di piano sensibile ai parametri, con uno o più dei metodi seguenti:

  • Flag di traccia di stima della cardinalità (CE), ad esempio Flag di traccia 9481 (globale, sessione o livello di query)

  • Opzioni di configurazione con ambito database che tentano di ridurre il modello stima di cardinalità in uso o influenzare i presupposti del modello stima di cardinalità relativi all'indipendenza di più predicati. Ciò è particolarmente utile quando non esistono le statistiche su più colonne, che influiscono sulla capacità di ottimizzazione del piano sensibile ai parametri di valutare la candidatura 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 modello stima di cardinalità più recente tenta di assumere una certa correlazione e meno indipendenza per la congiunzione e la disgiunzione dei predicati. L'uso del modello stima di cardinalità legacy può influire sulla modalità di calcolo della selettività dei predicati in uno scenario di join a più colonne. Questa azione deve essere considerata solo per scenari specifici e non è consigliabile usare il modello stima di cardinalità legacy per la maggior parte dei carichi di lavoro.

• L'ottimizzazione del piano sensibile ai parametri 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 dispatcher è basato su un modulo, ovvero stored procedure, trigger, funzione, visualizzazione e così via. Come istruzione preparata, non è possibile eseguire il mapping diretto di object_id a un oggetto in sys.objects ma è essenzialmente un valore calcolato basato su un hash interno del testo batch. Per altre informazioni, vedere la sezione Tabella restituita della documentazione DMV di sys.dm_exec_plan_attributes.

  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à piano sensibile ai parametri archivierà tuttavia l'elemento object_id del padre di una variante di query all'interno dell'attributo ObjectID, parte dell'hint PLAN PER VALUE, che piano sensibile ai parametri aggiunge al file XML ShowPlan se la query padre fa parte di un modulo T-SQL non 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 piano sensibile ai parametri vengono eseguite come nuova istruzione preparata, object_id non viene esposto automaticamente nelle varie DMV sys.dm_exec_* correlate alla cache dei piani senza distruggere il codice XML ShowPlan e applicare tecniche di ricerca di criteri di testo (ovvero l'elaborazione XQuery aggiuntiva). Solo i piani dispatcher di ottimizzazione del piano sensibile ai parametri generano attualmente l'ID oggetto padre appropriato. object_id viene esposto all'interno di Query Store che 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 (Transact-SQL).

Considerazioni

• 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
);

Nota

A partire da SQL Server 2022 (16.x), Query Store è abilitato per impostazione predefinita per tutti i database appena creati.

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

• Per disabilitare l’ottimizzazione del piano sensibile ai parametri a livello di query, usare l'hint di query DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION.

• Se l'analisi dei parametri è disabilitata dal flag di traccia 4136, dalla configurazione con ambito database PARAMETER_SNIFFING o dall'hint per la query USE HINT('DISABLE_PARAMETER_SNIFFING'), l'ottimizzazione del piano sensibile ai parametri è disabilitata per i carichi di lavoro e i contesti di esecuzione associati. Per altre informazioni, vedere Hint (Transact-SQL) - Query e ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

• Il numero di varianti di piano univoche per dispatcher archiviato nella cache dei piani è limitato per evitare il bloating della cache. La soglia interna non è documentata. Poiché ogni batch SQL è in grado di creare molteplici piani e ogni piano 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 è palesemente elevata o le dimensioni degli archivi della cache CACHESTORE_OBJCP e CACHESTORE_SQLCP sono eccessive, è consigliabile applicare il flag di traccia 174.

• Il numero di varianti di piano univoche archiviate per una query in 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 include tutti i piani varianti di query per tutti i dispatcher appartenenti a una query padre. Considerare di aumentare l'opzione di configurazione di Query Store max_plans_per_query.

  • 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. Supponendo di avere una query con ID query pari a 10, che ha due piani dispatcher e ogni piano dispatcher ha 20 varianti di query ognuna (40 varianti di query in totale), il numero totale di piani per l'ID query 10 è 40 piani per le varianti di query e i due piani dispatcher. È anche possibile che la query padre stessa (ID query 10) disponga di 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 anche una media di cinque piani, in questo scenario è possibile avere oltre 200 piani in Query Store per una query padre. Ciò dipenderebbe anche dall'asimmetria dei dati nei set di dati a cui potrebbe fare riferimento questa query padre di esempio.

• Per ogni variante di query di mapping 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 di gestione sono disponibili in Query Store della colonna last_compile_batch_sql_handle nella tabella del catalogo sys.query_store_query.
  • query_id è univoco in Query Store. Questa colonna è disponibile in sys.query_store_query e in altre tabelle del catalogo di Query Store.

Forzatura del piano in Query Store

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

Se viene forzata una variante, il dispatcher padre non viene forzato. Se un dispatcher viene forzato, solo le varianti di tale dispatcher sono considerate idonee per l'uso:

• 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 era diventato inattive vengono forzate di nuovo

Comportamento dell'hint per la query di Query Store

• Quando un hint di Query Store viene aggiunto a una variante di query (query figlio), questo viene applicato nello stesso modo di una query non PSP. Gli hint per varianti di query hanno una precedenza superiore se è stato applicato anche un hint alla query padre in 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 per la query di Query Store viene rimosso dalla query padre, viene rimosso anche per le query figlio (varianti di query).

• Se viene aggiunto un hint RECOMPILE alla query padre, il sistema genererà piani non PSP dopo aver rimosso tutti i piani varianti di query esistenti dalla cache dei piani, poiché la funzionalità PSP non opera sulle query con hint RECOMPILE.

• È 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 all'hint per la query applicato. 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.

Piano sensibile ai parametri con hint di query e comportamento forzato del piano può essere riepilogato nella tabella seguente:

Hint per la variante di query o piano	L'elemento padre ha l'hint applicato dall'utente	L'elemento padre ha l’hint applicato ai feedback	L'elemento padre ha un piano forzato manualmente	L'elemento padre ha un piano forzato APC 1
Hint tramite utente	Hint variante query	Hint variante query	Hint variante query	N/D
Hint tramite feedback	Hint variante query	Hint variante query	Hint variante query	N/D
Piano forzato dall'utente	Variante di query Piano forzato	Variante di query Piano forzato	Variante di query Piano forzato	Variante di query Piano forzato
Piano forzato da APC	Variante di query Piano forzato	Variante di query Piano forzato	Variante di query Piano forzato	Variante di query Piano forzato
Nessun hint o piano forzato	Hint dell'utente padre	Nessun hint	Nessuna azione	Nessuna azione

¹ Componente di correzione automatica del piano 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 viene ignorata l'ottimizzazione piano sensibile ai parametri.

  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] ='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: descrive se si tratta di un piano dispatcher (padre) o di 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 dispatcher (padre).

Comportamento di SQL Server Audit

L'ottimizzazione PSP fornisce dati di controllo per l'istruzione del piano dispatcher e tutte le varianti di query associate 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:

action_id	object_name	statement	additional_information
AUSC			<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><session><![CDATA[Audit_Testing_Psp$A]]></session><action>event enabled</action><startup_type>manual</startup_type><object><![CDATA[audit_event]]></object></action_info>
EX	usp_test	exec usp_test 300
SL	T2	selezionare * da dbo.t2 dove ID=@id	<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>
SL	T2	selezionare * da dbo.t2 dove l’opzione ID=@id (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 1, predicate_range([MyNewDatabase].[ dbo]. [T2]. [ID] = @id, 100.0, 100000.0))	tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>
EX	usp_test	exec usp_test 60000
SL	T2	selezionare * da dbo.t2 dove ID=@id	<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>
SL	T2	selezionare * da dbo.t2 dove l’opzione ID=@id (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 3, predicate_range([TestDB_FOR_PSP].[ dbo]. [T2]. [ID] = @id, 100.0, 100000.0))	<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>

Problemi noti

Problema	Data individuata	Status	Data risolta
L'eccezione di violazione di accesso si verifica in Query Store in SQL Server 2022 (16.x) in determinate condizioni, ad esempio quando è abilitata l'integrazione di Query Store di ottimizzazione del piano sensibile ai parametri. Per altre informazioni, vedere gli aggiornamenti in Ottimizzazione del piano sensibile ai parametri, perché?.	Marzo 2023	Risolto	Agosto 2023 (CU 7)

Risolto

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

Nota

A partire da SQL Server 2022 (16.x), con l’aggiornamento cumulativo 7 sono state rilasciate diverse correzioni per una race condition che possono causare una violazione di accesso. Se si verificano violazioni di accesso correlate all'ottimizzazione PSP con l'integrazione di Query Store dopo l'applicazione dell'aggiornamento cumulativo 7 per SQL Server 2022 (16.x), prendere in considerazione la sezione seguente.

Questo problema si verifica a causa di una race condition che può essere causata quando le statistiche di runtime per una query eseguita vengono rese persistenti dalla rappresentazione in memoria di Query Store (disponibile nel clerk di memoria MEMORYCLERK_QUERYDISKSTORE_HASHMAP) alla versione su disco di 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 altri thread in background di Query Store correlati alla pulizia del piano di query di Query Store (ovvero le opzioni di Query Store STALE_QUERY_THRESHOLD_DAYS e/o MAX_STORAGE_SIZE_MB), le query di Query Store sono in una condizione in cui una variante di query e/o la relativa istruzione dispatcher associata può diventare prematuramente dereferenziata. 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.

Soluzione alternativa: se il sistema riscontra ancora violazioni di accesso in Query Store con l'integrazione PSP abilitata dopo l'applicazione dell'aggiornamento cumulativo 7 per SQL Server 2022 (16.x), rimuovere le varianti di query presenti in Query Store oppure disabilitare temporaneamente la funzionalità PSP a livello di query o database fino a quando non sono disponibili correzioni aggiuntive.

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

Per rimuovere tutte le varianti di query da Query Store, oltre alle varianti di query visualizzate nella vista del catalogo sys.query_store_query_variant (Transact-SQL), è possibile usare una query simile alla seguente. Sostituire [<database>] con il database appropriato in cui si sono verificati problemi:

USE master;
GO

--Temporarily turn Query Store off in order to remove query variant plans as well as to
--clear the Query Store in-memory representation of Query Store (HashMap) for a particular database
ALTER DATABASE [<database>] SET QUERY_STORE = OFF;
GO

USE [<database>];
GO

DECLARE @QueryIDsCursor CURSOR;
DECLARE @QueryID BIGINT;
BEGIN
 -- Getting the cursor for query IDs for query variant plans
    SET @QueryIDsCursor = CURSOR FAST_FORWARD FOR
    SELECT query_id
        FROM sys.query_store_plan
    WHERE plan_type = 2 --query variant plans
    ORDER BY query_id;
 
 -- Using a non-set based method for this example query
    OPEN @QueryIDsCursor
        FETCH NEXT FROM @QueryIDsCursor
        INTO @QueryID
        WHILE @@FETCH_STATUS = 0
    BEGIN

 -- Deleting query variant(s) from the Query Store
        EXEC sp_query_store_remove_query @query_id = @QueryID;
        FETCH NEXT FROM @QueryIDsCursor
        INTO @QueryID
    END;
    CLOSE @QueryIDsCursor ;
    DEALLOCATE @QueryIDsCursor;
END;

--Turn Query Store back on
ALTER DATABASE [<database>] SET QUERY_STORE = ON;
GO

La disattivazione di Query Store potrebbe richiedere del tempo se Query Store è di grandi dimensioni o se il sistema ha un carico di lavoro sostanziale e/o un numero elevato di query senza parametri ad hoc che sono idonee per l'acquisizione da Query Store. Per disattivare forzatamente Query Store in questi scenari, usare piuttosto il comando ALTER DATABASE [<database>] SET QUERY_STORE = OFF (FORCED), nella T-SQL dell’esempio precedente. Per trovare query senza parametri, vedere Trovare query senza parametri in Query Store.

Contenuto correlato

• Ricompilazione dei piani di esecuzione
• Parametri e riutilizzo del piano di esecuzione
• Parametrizzazione semplice
• Parametrizzazione forzata
• Hints (Transact-SQL) - Query
• Elaborazione di query intelligenti nei database SQL
• Sensibilità ai parametri
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)