Prestazioni intelligenti
SQL Server e Azure SQL includono l'automazione per fornire prestazioni coerenti per l'applicazione. Queste funzionalità di automazione sono dette collettivamente prestazioni intelligenti.
Le prestazioni intelligenti per Azure SQL includono l'elaborazione intelligente delle query, la correzione automatica dei piani e l'ottimizzazione automatica.
Elaborazione di query intelligenti
L'elaborazione intelligente delle query (IQP, Intelligent Query Processing) è una suite di nuove funzionalità integrate in Query Processor. È possibile abilitare questa suite usando il livello di compatibilità del database più recente. È possibile migliorare le prestazioni delle applicazioni usando il livello di compatibilità del database più recente. Non sono richieste modifiche al codice. Un esempio di elaborazione intelligente delle query è la compilazione posticipata delle variabili di tabella per accelerare l'esecuzione delle query che usano le variabili di tabella.
Il database SQL di Azure e Istanza gestita di SQL di Azure supportano lo stesso livello di compatibilità del database necessario per usare l'elaborazione intelligente delle query (150), come SQL Server 2019 e versioni successive.
Correzione automatica del piano
Le regressioni dei piani di query rappresentano uno dei problemi di prestazioni più difficili da risolvere con SQL Server. Una regressione del piano di query si verifica quando la stessa query viene ricompilata e un nuovo piano comporta un peggioramento delle prestazioni.
In SQL Server 2017 e nel database SQL di Azure è stato introdotto il concetto di correzione automatica dei piani tramite l'analisi dei dati in Query Store. Quando si abilita Query Store con un database in SQL Server 2017 (o versione successiva) e nel database SQL di Azure, il motore di SQL Server cerca le regressioni dei piani di query e fornisce raccomandazioni. È possibile visualizzare queste raccomandazioni nella DMV sys.dm_db_tuning_recommendations. Queste raccomandazioni includono istruzioni T-SQL per forzare manualmente un piano di query quando le prestazioni sono in buono stato.
Se si acquisisce fiducia in questi consigli, è possibile abilitare SQL Server per forzare automaticamente i piani quando vengono rilevate regressioni. Abilitare la correzione automatica dei piani usando ALTER DATABASE e l'argomento AUTOMATIC_TUNING.
Per il database SQL di Azure, è anche possibile abilitare la correzione automatica dei piani tramite le opzioni di ottimizzazione automatica nel portale di Azure o le API REST. Le raccomandazioni per la correzione automatica dei piani sono sempre abilitate per qualsiasi database in cui è abilitato Query Store (che è l'impostazione predefinita per il database SQL di Azure e Istanza gestita di SQL di Azure). La correzione automatica dei piani (FORCE_PLAN) è abilitata per impostazione predefinita per i nuovi database per il database SQL di Azure.
Ottimizzazione automatica per il database SQL di Azure
La correzione automatica dei piani è un esempio di ottimizzazione automatica in Azure SQL e SQL Server, ma un aspetto univoco dell'ottimizzazione automatica per il database Azure SQL è l'indicizzazione automatica.
Nota
L'indicizzazione automatica non è attualmente disponibile in Istanza gestita di SQL di Azure.
Il cloud offre a Microsoft un metodo per fornire servizi aggiuntivi sotto forma di raccomandazioni sulle prestazioni e automazione al di fuori delle raccomandazioni del piano. Questa funzionalità è nota come ottimizzazione automatica per il database SQL di Azure. Questi servizi vengono eseguiti come programmi in background che analizzano i dati sulle prestazioni di un'istanza del database SQL di Azure. Questi servizi sono inclusi nel prezzo di qualsiasi sottoscrizione di database.
Lo scenario principale di ottimizzazione automatica è progettato per gestire gli indici. L'ottimizzazione automatica analizza i dati di telemetria di un database, inclusi Query Store e le DMV, per consigliare gli indici da creare per migliorare le prestazioni dell'applicazione. È anche possibile abilitare l'ottimizzazione automatica per creare automaticamente gli indici che possono migliorare le prestazioni di query. L'ottimizzazione automatica monitora anche le modifiche agli indici e consiglia di eliminare oppure elimina automaticamente gli indici che non migliorano le prestazioni di query.
L'ottimizzazione automatica per il database SQL di Azure richiede un approccio conservativo per consigliare gli indici. Ciò significa che gli elementi consigliati che possono venire visualizzati in una DMV come sys.dm_db_missing_index_details o in uno showplan di query, possono non venire visualizzati immediatamente come elementi consigliati per l'ottimizzazione automatica. I servizi di ottimizzazione automatica monitorano le query nel tempo e usano algoritmi di Machine Learning per fornire elementi consigliati in grado di influire effettivamente sulle prestazioni di query.
Tenere presente che l'ottimizzazione automatica per le raccomandazioni relative agli indici non tiene conto del sovraccarico sulle prestazioni che un indice può causare per le operazioni, ad esempio inserimenti, aggiornamenti o eliminazioni. Di solito, i nuovi indici non cluster creati dalla funzionalità degli indici automatici hanno un notevole impatto positivo sulle prestazioni.
Le query con parametri rappresentano uno scenario aggiuntivo per l'ottimizzazione automatica. Le query con valori senza parametri possono causare un sovraccarico delle prestazioni perché il piano di esecuzione viene ricompilato ogni volta che i valori senza parametri sono diversi. In molti casi, le stesse query con valori di parametro diversi generano gli stessi piani di esecuzione. Questi piani, tuttavia, vengono comunque aggiunti separatamente alla cache dei piani. Il processo di ricompilazione dei piani di esecuzione usa le risorse del database, aumenta il tempo di durata della query e si verifica un overflow della cache dei piani. Questi eventi, a loro volta, determinano la rimozione dei piani dalla cache.
È possibile usare l’ottimizzazione Parameter Sensitive Plan (PSP) per indirizzare questo scenario. 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 accettano dimensioni di dati diverse in base ai valori dei parametri di runtime forniti dal cliente.
Esempi di indici con ottimizzazione automatica nel database SQL di Azure
Di seguito è riportato un esempio del portale di Azure in cui vengono consigliati gli indici per un database in base all'analisi del carico di lavoro nel tempo. Non è ancora stata inviata un'attività sufficiente al database SQL di Azure sandbox per generare elementi consigliati simili. Gli elementi consigliati CREATE INDEX vengono generati nel tempo man mano che viene acquisito il carico di lavoro e non nel breve lasso di tempo di questo esercizio.
Nel portale di Azure, in Informazioni generali sulle prestazioni è possibile visualizzare le informazioni sulle prestazioni per le prime cinque query per utilizzo di risorse, come indicato in Query Store. C'è anche una raccomandazione.
Il portale di Azure offre anche Informazioni dettagliate prestazioni query, uno strumento per la creazione di report visivi basato sul Query Store. In questo esempio, Informazioni dettagliate prestazioni query mostra la query specifica che utilizza la maggior parte delle risorse e consiglia come migliorare le prestazioni di query.
Il portale di Azure fornisce anche un modo per visualizzare direttamente le raccomandazioni sulle prestazioni.
In questa vista sono presenti le raccomandazioni specifiche e la cronologia di tutte le azioni di ottimizzazione automatica. Nel caso di un indice, vengono visualizzati i dettagli dell'indice e della tabella. L'opzione Automatizza consente di abilitare l'ottimizzazione automatica.
È possibile impostare le opzioni di ottimizzazione automatica a livello di database o di server di database. Se è stata abilitata l'ottimizzazione automatica in questo scenario, l'indice verrà creato automaticamente.
È anche possibile visualizzare le opzioni di ottimizzazione automatica tramite la DMV sys.database_automatic_tuning_options.
Nota
Gli elementi consigliati e l'automazione per gli indici e i piani sensibili ai parametri non sono disponibili per Istanza gestita di SQL di Azure o SQL Server. È disponibile la correzione automatica dei piani.
Se si seleziona l'indice consigliato, verranno visualizzati maggiori dettagli sull'indice specifico.
Verranno visualizzati i dettagli relativi a indice, tabella e spazio necessario. È possibile scegliere di applicare l'indice consigliato oppure visualizzare uno script T-SQL che applica l'indice.
Si noti che l'indice è un indice non cluster applicato come indice online. Quando un indice è stato applicato in base a una raccomandazione, manualmente o tramite l'ottimizzazione automatica, il motore di raccomandazione monitora anche le prestazioni di query per un periodo di tempo con l'indice applicato. Se le prestazioni di query diminuiscono rispetto a prima dell'applicazione dell'indice, è possibile eliminare l'indice.