Uso forzato del piano ottimizzato con Query Store

  • Articolo

Si applica a:SQL Server 2022 (16.x)

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) determinerà 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 viene confermato 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. Viene confrontata una stima del tempo relativo per riprodurre lo script di ottimizzazione della riproduzione con il tempo trascorso durante l'esecuzione del normale processo di ottimizzazione, sulla base del numero di attività di ottimizzazione archiviate nello script di ottimizzazione e del 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:

  1. Solo i piani di query che superano l'ottimizzazione completa sono idonei, verificabile con la presenza della proprietà StatementOptmLevel="FULL".
  2. 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 uso forzato dal piano ottimizzato, verrà creato lo script di riproduzione dell'ottimizzazione per una seconda ricompilazione della stessa query, soggetta a eventi di ricompilazione predefiniti. Altre informazioni sulla ricompilazione in Ricompilazione dei piani di esecuzione.

Uno script di riproduzione dell'ottimizzazione generato potrebbe non essere mantenuto 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 non valido verrà 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 query DISABLE_OPTIMIZED_PLAN_FORCING. È 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. Nelle istanze aggiornate con database esistenti o ripristinati da una versione precedente di SQL Server, l'uso forzato ottimizzato del piano abilitato è di 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 DISABLE_OPTIMIZED_PLAN_FORCINGl'hint per la query.

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 stored procedure sp_query_store_force_plan. Il parametro aggiuntivo è denominato replica_group_id. Per impostazione predefinita, il parametro primario replica_group_id avrà un valore pari a uno (1) anche nel caso in cui non siano presenti repliche secondarie configurate.

Un caso di applicazione dei parametri appropriati alla stored procedure sp_query_store_force_plan è presente nell'esempio 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 su sys.query_store_plan (Transact-SQL).

Esempi

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. Per altre informazioni sulle opzioni di abilitazione Query Store, vedere Opzioni ALTER DATABASE SET (Transact-SQL).

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 stored procedure sp_query_store_force_plan prevede che il parametro @replica_group_id risulti come terzo valore di parametro quando si prova a 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. Valore 1 : @replica_group_id=1 verrà usato per disabilitare la funzionalità nella replica primaria.

EXEC sp_query_store_force_plan @query_id=148, @plan_id=4, @replica_group_id=1, @disable_optimized_plan_forcing=1;
GO

Altre informazioni in sp_query_store_force_plan (Transact-SQL).

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 è stato 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'DISABLE_OPTIMIZED_PLAN_FORCINGhint per la query. Questo esempio usa il database di esempio AdventureWorks.

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

Passaggi successivi

Altre informazioni su Query Store e sull'uso forzato del piano ottimizzato sono disponibili nei seguenti articoli: