Prestazioni intelligenti
SQL Server e Azure SQL includono l'automazione per fornire prestazioni coerenti per l'applicazione. Queste funzionalità di automazione sono denominate 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. Le applicazioni possono ottenere prestazioni 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.
database SQL di Azure e Istanza gestita di SQL di Azure supportano lo stesso livello di compatibilità del database necessario (150) per usare IQP come SQL Server 2019 e versioni successive.
Correzione automatica dei piani
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 sys.dm_db_tuning_recommendations dmv (Dynamic Management View). 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 del piano 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). Per i nuovi database, la correzione automatica del piano (FORCE_PLAN) è abilitata per impostazione predefinita per database SQL di Azure.
Ottimizzazione automatica per il database SQL di Azure
La correzione automatica dei piani è un esempio di ottimizzazione automatica in SQL di Azure e SQL Server, ma un aspetto univoco dell'ottimizzazione automatica per database SQL di Azure è 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 dai dati di telemetria di un database, inclusi Query Store e DMV, per consigliare indici da creare che possano 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 le raccomandazioni che potrebbero essere visualizzate in una DMV come sys.dm_db_missing_index_detailso in un piano di visualizzazione query, potrebbero non essere visualizzate immediatamente come raccomandazioni per l'ottimizzazione automatica. I servizi di ottimizzazione automatica monitorano le query nel tempo e usano algoritmi di Machine Learning per fornire raccomandazioni per influire effettivamente sulle prestazioni delle 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. In genere, i nuovi indici non cluster creati dalla funzionalità degli indici automatici hanno un impatto significativo sulle prestazioni.
Le query con parametri rappresentano uno scenario aggiuntivo per l'ottimizzazione automatica. Le query con valori non con parametri possono comportare un sovraccarico delle prestazioni, perché il piano di esecuzione viene ricompilato ogni volta che i valori non con 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 del piano sensibile ai parametri (PSP) per risolvere questo scenario. L'ottimizzazione PSP abilita automaticamente più piani attivi memorizzati nella cache per una singola istruzione con parametri. I piani di esecuzione memorizzati nella cache supportano dimensioni dei 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. L'attività non è ancora stata inviata alla sandbox database SQL di Azure per generare raccomandazioni simili. CREATE INDEX le raccomandazioni vengono generate nel tempo durante l'acquisizione del carico di lavoro e non nell'intervallo di tempo ridotto di questo esercizio.
Nella portale di Azure, in Panoramica delle prestazioni è possibile visualizzare le informazioni sulle prestazioni per le prime cinque query che utilizzano le risorse, come disponibile 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 in questo scenario è stata abilitata l'ottimizzazione automatica, l'indice verrà creato automaticamente.
È anche possibile visualizzare le opzioni di ottimizzazione automatica tramite la sys.database_automatic_tuning_options DMV.
Nota
Consigli e l'automazione per indici e 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 sull'indice, la tabella e lo spazio necessari. È 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.