Decidere se l'Assistente ottimizzazione query (QTA) è adatto
Si è a conoscenza della potenziale regressione del piano di query e della perdita di prestazioni dopo gli aggiornamenti delle versioni del database. Per mantenere le prestazioni dopo gli aggiornamenti del database, è necessario trovare il metodo migliore per identificare e ridurre le query regredite. Questa unità descrive come usare Query Store e Assistente ottimizzazione query (QTA) per garantire che le prestazioni ridotte non siano un problema dopo gli aggiornamenti.
Introduzione all'Archivio query e all'Assistente ottimizzazione query
L'assistente ottimizzazione query dipende dai dati di Query Store per trovare query regredite dopo un aggiornamento. Query Store consente di raccogliere le metriche nella versione precedente del database prima dell'aggiornamento.
Query Store è stato introdotto in SQL Server 2016 e l'assistente ottimizzazione query è stato introdotto in SQL Server 2017. Qualsiasi versione del database eseguita in un'istanza di SQL Server 2022 può usare entrambe queste funzionalità. Questi strumenti sono integrati in SQL Server Management Studio (SSMS) e operano a livello di database.
Il livello di compatibilità del database determina la versione, che a sua volta determina la versione dello strumento di stima della cardinalità usata. Lo strumento di stima della cardinalità stima il numero di righe restituite da una query, in modo che Query Optimizer possa selezionare il piano di costo più basso. SQL Server 2014 ha introdotto un algoritmo di stima della cardinalità aggiornato che offre vantaggi alla maggior parte delle query, ma raramente può avere un impatto negativo sulle prestazioni.
Per misurare l'impatto sulle prestazioni, Query Store segnala query e query regredite che utilizzano la maggior parte delle risorse di sistema. L'assistente ottimizzazione query confronta i dati sulle prestazioni delle query di Query Store prima e dopo l'aggiornamento del database e sperimenta le query per migliorare le prestazioni.
Nota
L'assistente ottimizzazione query non è disponibile per i database database SQL di Azure o Istanza gestita di SQL. Per questi database, è consigliabile usare l'estensione di migrazione SQL di Azure per Azure Data Studio.
QTA e correzione automatica dei piani
Quando SQL Server esegue una query Transact-SQL (T-SQL), analizza i possibili piani che possono eseguire la query. SQL Server memorizza nella cache i piani per le query eseguite correttamente e le riutilizza quando le query vengono eseguite di nuovo.
SQL Server seleziona il piano ottimale per una query e lo usa fino a quando una circostanza lo forza a selezionarne uno nuovo. Queste circostanze possono includere la ricompilazione del piano da parte del motore di database, l'aggiunta o la rimozione di un indice o la modifica delle statistiche.
Il nuovo piano non è sempre migliore di quello precedente. È possibile eseguire il comando seguente per cercare le query con un piano regredito.
SELECT * FROM sys.dm_db_tuning_recommendations
È quindi possibile usare la sp_force_plan stored procedure per forzare SQL Server a usare un piano specifico consigliato.
EXEC sp_force_plan @query_id = 1187, @plan_id = 1975
La sp_force_plan procedura è un processo manuale potenzialmente noioso se molte query stanno regredindo in un database aggiornato. SQL Server 2017 ha introdotto una nuova funzionalità denominata correzione automatica dei piani per ottimizzare automaticamente le query e rimuovere la necessità di intervento manuale. È possibile abilitare la correzione automatica dei piani in un database eseguendo l'istruzione seguente:
ALTER DATABASE <database-name> SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)
Se impostata a livello di database, la correzione automatica del piano indica a SQL Server di usare l'ultimo piano di query valido. SQL Server continua a monitorare il piano per trovare le regressioni durante l'esecuzione del piano e per garantire prestazioni ottimali.
La correzione automatica del piano funziona in modo diverso rispetto all'assistente ottimizzazione query. L'uso dell'ultimo piano valido può significare il rollback a uno strumento di stima della cardinalità precedente. Al contrario, l'assistente ottimizzazione query esegue gli esperimenti usando la versione dello strumento di stima della cardinalità mappato al livello di compatibilità del database di destinazione.
Riepilogo
L'assistente ottimizzazione query è disponibile in SQL Server 2022 e dipende dall'archivio query da usare. L'assistente ottimizzazione query deve disporre di dati di base di Query Store per un database a livello di compatibilità precedente, in modo da poter osservare le query ed effettuare confronti dopo un aggiornamento.
La correzione automatica dei piani, introdotta in SQL Server 2017, rimuove la necessità di identificare e forzare manualmente un piano di query. La correzione automatica del piano può essere abilitata a livello di database, ma potrebbe eseguire il rollback della versione dello strumento di stima della cardinalità. L'assistente ottimizzazione query usa la versione dello strumento di stima della cardinalità mappato al livello di compatibilità di destinazione.