Uso forzato del piano ottimizzato con Query Store
Si applica a: 
SQL Server 2022 (16.x) Database SQL di Azure
L'ottimizzazione delle query è un processo a più fasi per generare un piano di esecuzione di query "sufficientemente valido". In alcuni casi, la compilazione di query (una parte dell'ottimizzazione delle query) può rappresentare una percentuale elevata del tempo di esecuzione complessivo delle query e impiegare notevoli risorse di sistema. L'uso forzato del piano ottimizzato è parte della famiglia di funzionalità di elaborazione di query intelligenti. L'uso forzato del piano ottimizzato riduce il sovraccarico di compilazione per le query forzate ripetute e richiede che Query Store sia abilitato e in modalità "lettura/scrittura". Al completamento della generazione del piano di esecuzione delle query, alcuni specifici passaggi di compilazione vengono archiviati in modo che sia possibile riusarli come script per l'ottimizzazione della riproduzione. Uno script di ottimizzazione della riproduzione viene archiviato come parte del file XML dello showplan compresso in Query Store, in un attributo OptimizationReplay nascosto.
Implementazione uso forzato del piano ottimizzata
Quando una query passa per la prima volta attraverso il processo di compilazione, una soglia basata sulla stima del tempo dedicato all'ottimizzazione (in base all'albero di input di Query Optimizer) determina se viene creato uno script di ottimizzazione della riproduzione.
Al termine della compilazione, diventano disponibili diverse metriche di runtime per valutare se la stima precedente è stata corretta. Se il motore di database conferma che la soglia è stata superata, lo script di ottimizzazione di riesecuzione è idoneo per la persistenza. Queste metriche di runtime includono il numero di oggetti a cui si accede, il numero di join, il numero di attività di ottimizzazione eseguite durante l'ottimizzazione e il tempo di ottimizzazione effettivo.
Il potenziale vantaggio dell'uso di uno script di riproduzione dell'ottimizzazione è anche relativo all'overhead di archiviazione dello script di ottimizzazione della riproduzione. Una stima del tempo relativo per riprodurre lo script di riproduzione dell'ottimizzazione viene confrontata con il tempo trascorso durante l'esecuzione del normale processo di ottimizzazione. Questa stima è basata sul numero di attività di ottimizzazione archiviate nello script di riproduzione dell'ottimizzazione e sul numero di attività di ottimizzazione eseguite durante la normale compilazione. Lo script di riproduzione dell'ottimizzazione viene mantenuto se la riproduzione mostra un notevole vantaggio nella riduzione del tempo di compilazione.
Considerazioni
Quando è abilitata la funzionalità di uso forzato del piano ottimizzata, i relativi criteri di idoneità sono:
Solo i piani di query che superano l'ottimizzazione completa sono idonei, verificabile con la presenza della proprietà
StatementOptmLevel="FULL".Le istruzioni con hint RECOMPILE e le query distribuite non sono idonee.
Tuttavia, se Query Store acquisisce in modo indipendente un piano di query con ambito forzato dal piano ottimizzato, lo script di riproduzione dell'ottimizzazione viene creato per una seconda ricompilazione della stessa query, soggetta a eventi di ricompilazione predefiniti. Altre informazioni sulla ricompilazione in Ricompilazione dei piani di esecuzione.
Anche se è stato generato uno script di riproduzione dell'ottimizzazione, potrebbe non essere persistente in Query Store se i criteri di acquisizione configurati da Query Store non vengono soddisfatti, in particolare il numero di esecuzioni di tale istruzione e i relativi tempi di compilazione ed esecuzione replicati. In questo caso, lo script di ottimizzazione ottimizzazione non valido viene rimosso dalla memoria in modo asincrono.
Abilitare e disabilitare l’uso forzato del piano ottimizzato
È possibile abilitare o disabilitare la forzatura del piano ottimizzato per un database. Quando l'uso forzato del piano ottimizzato è abilitato per un database, è possibile disabilitarlo per le singole query usando l'hint per la DISABLE_OPTIMIZED_PLAN_FORCING query. È anche possibile disabilitare l'uso forzato del piano ottimizzato per un piano di query forzato in Query Store.
Abilitare o disabilitare l'uso forzato del piano ottimizzato per un database
L'uso forzato del piano ottimizzato è abilitato per impostazione predefinita per i nuovi database creati in SQL Server 2022 (16.x) e versioni successive. Per ogni database in cui viene impiegata la funzione uso forzato del piano ottimizzato è necessario abilitare Query Store. Le istanze aggiornate con i database esistenti o i database ripristinati da una versione precedente di SQL Server hanno ottimizzato l'uso forzato del piano abilitato per impostazione predefinita.
Per abilitare l'uso forzato del piano ottimizzato a livello di database, usare la configurazione con ambito database ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON. Se disabilitato, è necessario abilitare Query Store. Trovare il codice di esempio in esempio A o altre informazioni su Query Store in Monitorare le prestazioni con Query Store.
Per disabilitare l'uso forzato del piano ottimizzato a livello di database, usare la configurazione con ambito database ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF.
Disabilitare l'uso forzato del piano ottimizzato con un hint per la query
Quando la funzionalità uso forzato del piano ottimizzato è abilitata in un database, è possibile disabilitarne il funzionamento per una singola query usando l'hint per la query DISABLE_OPTIMIZED_PLAN_FORCING.
Un caso di applicazione di questo hint per la query è nell'esempio E.
Forzare un piano con Query Store, ma disabilitare l'uso forzato del piano ottimizzato
La procedura sp_query_store_force_plan include un parametro disable_optimized_plan_forcing. Per usare questo parametro, è necessario un parametro aggiuntivo dalla sp_query_store_force_plan stored procedure. Il parametro aggiuntivo è denominato @replica_group_id. Per impostazione predefinita, il database primario @replica_group_id ha un valore pari a uno (1) anche nel caso in cui non siano presenti repliche secondarie configurate.
Trovare un esempio di applicazione dei parametri appropriati alla stored procedure nell'esempio sp_query_store_force_plan C.
La vista del catalogo sys.query_store_plan include colonne che indicano se il piano ha uno script di riproduzione dell'ottimizzazione associato e aggiunge un nuovo stato alla colonna motivo degli errori esistenti relativa allo script di riproduzione dell'ottimizzazione associato. Altre informazioni sono disponibili in sys.query_store_plan.
Esempi
Gli esempi di codice Transact-SQL in questo articolo utilizzano il database campione AdventureWorks2022 o AdventureWorksDW2022, che è possibile scaricare dalla home page di Esempi di Microsoft SQL Server e progetti collettivi.
R. Abilitare Query Store e l'uso forzato del piano ottimizzato per un database
Il codice seguente abilita Query Store in un database, quindi abilita l'uso forzato del piano ottimizzato nel database. Altre informazioni sulle opzioni per l'abilitazione di Query Store nelle opzioni ALTER DATABASE SET.
Prima di eseguire il codice, connettersi al database utente appropriato.
ALTER DATABASE CURRENT SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
DATA_FLUSH_INTERVAL_SECONDS = 900,
QUERY_CAPTURE_MODE = AUTO,
MAX_STORAGE_SIZE_MB = 1024,
INTERVAL_LENGTH_MINUTES = 60
);
GO
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;
GO
B. Selezionare tutte le query con uno script di ottimizzazione della riproduzione
Il codice di esempio seguente seleziona tutte le query_ids con uno script di ottimizzazione riesecuzione in Query Store. Connettersi al database utente appropriato prima di eseguire il codice di esempio.
SELECT q.query_id,
t.query_sql_text,
p.plan_id,
TRY_CAST (p.query_plan AS XML) AS query_plan,
p.is_forced_plan,
p.count_compiles
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_query AS q
ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text AS t
ON q.query_text_id = t.query_text_id
WHERE p.has_compile_replay_script = 1;
GO
C. Forzare un piano e disabilitare l'uso forzato del piano ottimizzato in Query Store
Il codice seguente forza un piano in Query Store, ma disabilita l'uso forzato del piano ottimizzato. Prima di eseguire il codice seguente, sostituire @query_id e @plan_id con una combinazione appropriata per l'istanza. La sp_query_store_force_plan stored procedure prevede che il @replica_group_id parametro venga passato come terzo valore di parametro quando si tenta di disabilitare l'uso forzato del piano ottimizzato in Query Store. Può essere usato per disabilitare l'uso forzato del piano ottimizzato per un determinato piano forzato in una replica specifica. Viene usato un valore di @replica_group_id = 1 per disabilitare la funzionalità nella replica primaria.
EXECUTE sp_query_store_force_plan
@query_id = 148,
@plan_id = 4,
@replica_group_id = 1,
@disable_optimized_plan_forcing = 1;
GO
Altre informazioni sono disponibili in sp_query_store_force_plan.
D. Selezionare tutte le query con uso forzato del piano ottimizzato disabilitato da Query Store
Nell'esempio seguente vengono eseguite query su tutti i piani forzati in Query Store in cui is_optimized_plan_forcing_disabled è impostato su 1. Prima di eseguire il codice, connettersi al database utente appropriato.
SELECT q.query_id,
t.query_sql_text,
p.plan_id,
TRY_CAST (p.query_plan AS XML) AS query_plan,
p.is_forced_plan,
p.count_compiles
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_query AS q
ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text AS t
ON q.query_text_id = t.query_text_id
WHERE p.is_optimized_plan_forcing_disabled = 1;
GO
E. Disabilita l’uso forzato del piano ottimizzato per una query
Nell'esempio seguente viene disabilitata la funzione uso forzato del piano ottimizzato per una query usando l'hint per la query DISABLE_OPTIMIZED_PLAN_FORCING.
SELECT ProductID,
OrderQty,
SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (USE HINT('DISABLE_OPTIMIZED_PLAN_FORCING'));
GO