Ottimizzazione automatica

  • Articolo

Si applica a: SQL Server 2016 (13.x) e versioni successive Database SQL diIstanza gestita di SQL di Azure

L'ottimizzazione automatica è una funzionalità di database che offre informazioni su potenziali problemi di prestazioni delle query, suggerisce soluzioni e corregge automaticamente i problemi rilevati.

L'ottimizzazione automatica, introdotta in SQL Server 2017 (14.x), notifica l'utente ogni volta che viene rilevato un potenziale problema di prestazioni e consente all'utente di applicare azioni correttive, oppure permette al motore di database di risolvere automaticamente i problemi di prestazioni. L'ottimizzazione automatica di SQL Server identifica e risolve i problemi di prestazioni causati da regressioni nella scelta del piano di esecuzione delle query. L'ottimizzazione automatica in Azure SQL Database crea anche gli indici necessari e elimina quelli inutilizzati. Per ulteriori informazioni sui piani di esecuzione delle query, vedere Piani di esecuzione.

Il motore di database di SQL Server monitora le query eseguite sul database e migliora automaticamente le prestazioni del carico di lavoro. Il motore di database include un meccanismo di intelligence incorporato che consente di ottimizzare automaticamente le query e migliorarne le prestazioni, adattando in modo dinamico il database al carico di lavoro. Sono disponibili due funzioni di ottimizzazione automatica:

  • La correzione automatica del piano identifica i piani di esecuzione delle query problematici, come la sensibilità dei parametri o i problemi di sniffing dei parametri, e risolve i problemi di prestazioni legati al piano di esecuzione delle query forzando l'ultimo piano buono conosciuto prima che si verificasse la regressione. Si applica a: SQL Server (a partire da SQL Server 2017 (14.x)) e Azure SQL Database e Azure SQL Managed Instance].

  • Gestione automatica degli indici: consente di identificare gli indici da aggiungere al database e quelli che è consigliabile rimuovere. Si applica a: Database SQL di Azure

Perché optare per l'ottimizzazione automatica?

Tre delle attività principali per l'amministrazione classica di un database sono il monitoraggio del carico di lavoro, l'identificazione delle query Transact-SQL critiche e l'identificazione degli indici che dovrebbero essere aggiunti per migliorare le prestazioni o degli indici che vengono usati raramente e che potrebbero essere rimossi per migliorare le prestazioni. Il motore di database di SQL Server offre informazioni dettagliate per le query e gli indici che occorre monitorare. Il monitoraggio costante dei database è tuttavia un'attività complessa e tediosa, in particolare quando sono coinvolti molti database. Gestire un numero enorme di database potrebbe essere impossibile da fare in modo efficiente. Invece di gestire manualmente il monitoraggio e l'ottimizzazione del database, è possibile prendere in considerazione la possibilità di delegare alcune delle azioni di monitoraggio e ottimizzazione al motore di database, tramite la funzionalità di ottimizzazione automatica.

Come funziona l'ottimizzazione automatica

L’ottimizzazione automatica è un processo di monitoraggio e analisi continua che apprende costantemente le caratteristiche del carico di lavoro e identifica potenziali problemi e miglioramenti.

Automatic tuning process.

Questo processo consente l'adattamento dinamico del database al carico di lavoro mediante l'individuazione degli indici e dei piani che potrebbero migliorare le prestazioni dei carichi di lavoro e degli indici con effetti sui carichi di lavori. In base ai risultati raccolti, l'ottimizzazione automatica applica le azioni di ottimizzazione che consentono di migliorare le prestazioni del carico di lavoro. Inoltre, l'ottimizzazione automatica monitora continuamente le prestazioni del database dopo l'implementazione di qualsiasi modifica, per garantire il miglioramento delle prestazioni del carico di lavoro. Qualsiasi azione che non introduce miglioramenti delle prestazioni viene annullata automaticamente. Il processo di verifica è una funzionalità chiave che assicura che qualsiasi modifica apportata dall'ottimizzazione automatica non diminuisca le prestazioni complessive del carico di lavoro.

Correzione automatica del piano

La correzione automatica del piano è una funzione di messa a punto automatica che identifica la regressione della scelta del piano di esecuzione e risolve automaticamente il problema forzando l'ultimo piano buono conosciuto. Per ulteriori informazioni sui piani di esecuzione delle query e sul Query Optimizer, consultare la Guida sull'architettura di elaborazione delle query.

Importante

La correzione automatica del piano dipende dall'abilitazione di Query Store nel database per il monitoraggio del carico di lavoro.

Che cos'è la regressione della scelta del piano di esecuzione?

Il motore di database di SQL Server può utilizzare diversi piani di esecuzione per eseguire le query Transact-SQL. I piani di query dipendono dalle statistiche, dagli indici e da altri fattori. Il piano ottimale da utilizzare per l'esecuzione di una query Transact-SQL può cambiare nel tempo in base alle variazioni di questi fattori. In alcuni casi, il nuovo piano potrebbe non essere migliore di quello precedente e potrebbe causare una regressione delle prestazioni, ad esempio un problema di sensibilità dei parametri o di sniffing dei parametri.

Query execution plan choice regression.

Ogni volta che si nota una regressione nella scelta del piano, è necessario trovare un piano precedente valido e forzarlo al posto di quello attuale. Questa operazione può essere eseguita usando la procedura sp_query_store_force_plan. Il motore di database in SQL Server 2017 (14.x) fornisce informazioni sui piani regressi e sulle azioni correttive consigliate. Inoltre, il motore di database consente di automatizzare completamente questo processo e di lasciare che il motore di database risolva qualsiasi problema riscontrato in relazione alla modifica del piano.

Importante

La correzione automatica del piano dovrebbe essere utilizzata nell'ambito di un aggiornamento del livello di compatibilità del database, dopo l'acquisizione di una linea di base, per mitigare automaticamente i rischi di aggiornamento del carico di lavoro. Per altre informazioni su questo caso d’uso, vedere Mantenere la stabilità delle prestazioni durante l'aggiornamento a SQL Server.

Correzione scelta del piano automatica

Il motore di database può passare automaticamente all'ultimo piano valido conosciuto ogni volta che viene rilevata una regressione nella scelta del piano.

Query execution plan choice correction.

Il motore di database rileva automaticamente qualsiasi potenziale regressione nella scelta del piano, compreso il piano che dovrebbe essere utilizzato al posto di quello sbagliato. Il piano di esecuzione risultante, forzato dalla correzione automatica del piano, sarà uguale o simile all'ultimo piano valido conosciuto. Poiché il piano risultante può non essere identico all'ultimo piano noto, la performance del piano forzato può variare. In rari casi, la differenza di prestazioni può essere significativa e negativa; in questo caso, la correzione automatica del piano interromperà automaticamente il tentativo di forzare il piano sostitutivo.

Quando il motore di database applica l'ultimo piano valido conosciuto prima che si verificasse la regressione, monitora automaticamente le prestazioni del piano forzato. Se il piano forzato non è migliore di quello regredito, il nuovo piano non sarà forzato e il motore di database compilerà un nuovo piano. Se il motore di database verifica che il piano forzato è migliore di quello regredito, il piano forzato viene mantenuto. Verrà conservato fino a quando non verrà ricompilato (ad esempio, al successivo aggiornamento delle statistiche o alla modifica dello schema). Per ulteriori informazioni sull’uso forzato dei piani e sui tipi di piani che possono essere forzati, vedere Limitazioni per l’uso forzato dei piani.

Nota

Se l'istanza di SQL Server viene riavviata prima che venga verificata un'azione forzata del piano, tale piano verrà automaticamente rimosso. In caso contrario, l'uso forzato del piano viene salvato in modo permanente nei riavvii di SQL Server.

Abilitare la correzione automatica della scelta del piano

È possibile abilitare l'ottimizzazione automatica per ogni database e specificare che il miglior piano più recente deve essere forzato ogni volta in cui viene rilevata una regressione della modifica del piano. L'ottimizzazione automatica viene abilitata con il comando seguente:

ALTER DATABASE <yourDatabase>
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

Una volta attivata questa opzione, il motore di database forzerà automaticamente le indicazioni se il miglioramento della CPU stimato è superiore a 10 secondi o il numero di errori nel nuovo piano è maggiore del numero di errori nel piano consigliato e verifica che il piano forzato sia migliore rispetto a quello corrente.

Per abilitare l'ottimizzazione automatica nel database SQL di Azure e nell’Istanza gestita di SQL di Azure, vedere Abilitazione dell'ottimizzazione automatica nel database SQL di Azure tramite il portale di Azure.

Opzione alternativa: correzione manuale della scelta del piano

Senza l'ottimizzazione automatica, gli utenti devono monitorare il sistema periodicamente e cercare le query che sono regredite. Se un piano è regredito, l'utente deve trovare un piano buono precedente e forzarlo al posto di quello attuale, usando la procedura sp_query_store_force_plan. La procedura consigliata consiste nel forzare l'ultimo piano valido noto perché i piani meno recenti potrebbero non essere validi a causa di statistiche o modifiche all'indice. L'utente che forza l'ultimo piano valido noto deve monitorare le prestazioni della query eseguita tramite l’uso forzato del piano e verificare che il piano forzato funzioni come previsto. In base ai risultati del monitoraggio e dell'analisi, il piano deve essere forzato o l'utente deve trovare un altro modo per ottimizzare la query, ad esempio riscrivendola. I piani forzati manualmente non devono essere forzati per sempre, perché il motore di database dovrebbe essere in grado di applicare piani ottimali. L'utente o l'amministratore del database deve infine rimuovere il piano usando la procedura sp_query_store_unforce_plan e lasciare che il motore di database trovi il piano ottimale.

Suggerimento

In alternativa, usare la visualizzazione del Query Store Query con piani forzati per individuare e rimuovere i piani forzati.

SQL Server fornisce tutte le viste e le procedure necessarie per monitorare le prestazioni e risolvere i problemi in Query Store.

In SQL Server 2016 (13.x), è possibile trovare regressioni nella scelta del piano utilizzando le viste di sistema di Query Store. A partire da SQL Server 2017 (14.x), il Database Engine rileva e mostra potenziali regressioni nella scelta del piano e le azioni consigliate da applicare nella DMV sys.dm_db_tuning_recommendations (Transact-SQL). La DMV mostra informazioni sul problema, l'importanza del problema e dettagli quali la query identificata, l'ID del piano regredito, l'ID del piano usato come linea di base per il confronto e l'istruzione Transact-SQL che può essere eseguita per risolvere il problema.

type description datetime score dettagli ...
FORCE_LAST_GOOD_PLAN Tempo CPU modificato da 4 ms a 14 ms 17/03/2017 83 queryId recommendedPlanId regressedPlanId T-SQL
FORCE_LAST_GOOD_PLAN Tempo CPU modificato da 37 ms a 84 ms 16/03/2017 26 queryId recommendedPlanId regressedPlanId T-SQL

Alcune colonne di questa vista sono descritte nell'elenco seguente:

  • Tipo dell'azione consigliata FORCE_LAST_GOOD_PLAN.
  • Descrizione che contiene informazioni sul motivo per cui il motore di database ritiene che questa modifica del piano sia una potenziale regressione delle prestazioni.
  • Datetime in cui è stata rilevata la regressione potenziale.
  • Punteggio di questa raccomandazione.
  • Dettagli sui problemi, come l'ID del piano rilevato, l'ID del piano regredito, l'ID del piano che dovrebbe essere forzato per risolvere il problema, lo script Transact-SQL che potrebbe essere applicato per risolvere il problema, ecc. I dettagli sono memorizzati in formato JSON.

Usare la seguente query per ottenere uno script che risolve il problema e ulteriori informazioni sul guadagno stimato:

SELECT reason, score,
      script = JSON_VALUE(details, '$.implementationDetails.script'),
      planForceDetails.*,
      estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount)
                  * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000,
      error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES','NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            regressedPlanId int '$.regressedPlanId',
            recommendedPlanId int '$.recommendedPlanId',
            regressedPlanErrorCount int,
            recommendedPlanErrorCount int,
            regressedPlanExecutionCount int,
            regressedPlanCpuTimeAverage float,
            recommendedPlanExecutionCount int,
            recommendedPlanCpuTimeAverage float
          ) AS planForceDetails;

Questo è il set di risultati.

reason score script query_id plan_id corrente plan_id consigliato estimated_gain error_prone
Tempo CPU modificato da 3 ms a 46 ms 36 EXEC sp_query_store_force_plan 12, 17; 12 28 17 11.59 0

La colonna estimated_gain rappresenta il numero stimato di secondi che verrebbero risparmiati se il piano consigliato venisse utilizzato per l'esecuzione della query al posto del piano corrente. Il piano consigliato deve essere forzato al posto del piano corrente se il guadagno è superiore a 10 secondi. Se sono presenti più errori , ad esempio timeout o esecuzioni interrotte, nel piano corrente rispetto al piano consigliato, la colonna error_prone verrà impostata sul valore YES. Un piano soggetto a errori è un altro motivo per cui il piano consigliato deve essere forzato anziché quello corrente.

Anche se il motore di database fornisce tutte le informazioni necessarie per identificare le regressioni di scelta del piano, il monitoraggio continuo e la risoluzione dei problemi di prestazioni potrebbero diventare un processo tedioso. L'ottimizzazione automatica semplifica notevolmente questo processo.

Nota

I dati nella DMV sys.dm_db_tuning_recommendations non vengono salvati in modo permanente dopo il riavvio del motore di database. Usare la colonna sqlserver_start_time in sys.dm_os_sys_info per trovare l'ora di avvio dell'ultimo motore di database.

Gestione automatica degli indici

Nel database SQL di Azure la gestione degli indici è semplice perché il database SQL di Azure raccoglie informazioni sul carico di lavoro e assicura che i dati vengano indicizzati sempre in modo ottimale. La progettazione appropriata degli indici è fondamentale per ottenere prestazioni ottimali del carico di lavoro e la gestione automatica degli indici risulta utile per ottimizzare gli indici. La gestione automatica degli indici consente di correggere i problemi di prestazioni nei database indicizzati in modo non corretto oppure di gestire e migliorare gli indici nello schema del database esistente. L'ottimizzazione automatica nel database SQL di Azure esegue le azioni seguenti:

  • Identifica gli indici che potrebbero migliorare le prestazioni delle query Transact-SQL che leggono dati dalle tabelle.
  • Identifica gli indici ridondanti o quelli che non sono stati utilizzati per un lungo periodo di tempo e che potrebbero essere rimossi. La rimozione degli indici non necessari migliora le prestazioni delle query che aggiornano i dati nelle tabelle.

Utilità della gestione degli indici

Gli indici consentono di velocizzare alcune delle query che leggono dati dalle tabelle, ma possono rallentare le query che aggiornano i dati. È necessario valutare con attenzione quando creare un indice e quali colonne includervi. Alcuni indici potrebbero non essere più necessari dopo un certo periodo di tempo. Per questo motivo, è consigliabile individuare ed eliminare periodicamente questi indici che non portano alcun vantaggio. Se si ignorano gli indici inutilizzati, le prestazioni delle query di aggiornamento dei dati potrebbero risultare ridotte, senza alcun vantaggio per le query di lettura dei dati. Gli indici inutilizzati influiscono anche sulle prestazioni complessive del sistema perché risultano necessarie operazioni di registrazione superflue per aggiornamenti aggiuntivi.

Per individuare il set ottimale di indici che consentono di migliorare le prestazioni delle query di lettura dei dati dalle tabelle con un impatto minimo per gli aggiornamenti, possono essere necessarie operazioni di analisi continue e complesse.

Il database SQL di Azure usa i meccanismi di intelligence incorporati e regole avanzate per analizzare le query, identificare gli indici che sarebbero ottimali per i carichi di lavoro correnti e identifica gli indici che potrebbero essere rimossi. Il database SQL di Azure assicura di avere a disposizione il set minimo necessario di indici per l'ottimizzazione delle query di lettura dei dati, con un impatto minimo sulle altre query.

Gestione automatica degli indici

Oltre al rilevamento, il database SQL di Azure può applicare automaticamente le raccomandazioni individuate. Se si appura che le regole predefinite consentono di migliorare le prestazioni del database, è possibile delegare al database SQL di Azure la gestione automatica degli indici.

Quando il database SQL di Azure applica una raccomandazione CREATE INDEX o DROP INDEX, monitora automaticamente le prestazioni delle query interessate dall'indice. Il nuovo indice sarà mantenuto solo se le prestazioni delle query interessate saranno migliorate. L'indice eliminato verrà ricreato automaticamente se alcune query vengono eseguite più lentamente a causa dell'assenza dell'indice.

Considerazioni sulla gestione automatica degli indici

Le azioni richieste per creare gli indici necessari nei database SQL di Azure potrebbero usare risorse e influire temporaneamente sulle prestazioni del carico di lavoro. Per ridurre al minimo l'impatto della creazione degli indici sulle prestazioni del carico di lavoro, il database SQL di Azure individua una finestra temporale appropriata per qualsiasi operazione di gestione degli indici. L'azione di ottimizzazione viene posticipata se il database ha bisogno di risorse per l'esecuzione del carico di lavoro e viene riavviata quando per il database è disponibile una quantità di risorse inutilizzate sufficiente per essere usata per l'attività di manutenzione. Una funzionalità importante nella gestione automatica degli indici e la verifica delle azioni. Quando il database SQL di Azure crea o elimina un indice, un processo di monitoraggio analizza le prestazioni del carico di lavoro per verificare che l'azione migliori effettivamente le prestazioni complessive. Se non introduce miglioramenti significativi, l'azione viene annullata immediatamente. In questo modo, il database SQL di Azure garantisce che le azioni di ottimizzazione automatica non influiscano negativamente sulle prestazioni del carico di lavoro. Gli indici creati tramite l'ottimizzazione automatica sono trasparenti per l'operazione di manutenzione sullo schema sottostante. Le modifiche dello schema, ad esempio l'eliminazione o ridenominazione delle colonne, non vengono impedite dalla presenza di indici creati automaticamente. Gli indici creati automaticamente dal database SQL di Azure vengono eliminati immediatamente in seguito all'eliminazione di tabelle o colonne correlate.

Alternativa: gestione manuale degli indici

Senza la gestione automatica degli indici, un utente o un DBA dovrebbe interrogare manualmente la vista sys.dm_db_missing_index_details (Transact-SQL) o utilizzare il report Performance Dashboard in Management Studio per trovare gli indici che potrebbero migliorare le prestazioni, creare indici utilizzando i dettagli forniti in questa vista e monitorare manualmente le prestazioni della query. Per individuare gli indici che dovrebbero essere eliminati, gli utenti dovrebbero monitorare le statistiche di utilizzo operativo degli indici per individuare quelli usati raramente.

Il database SQL di Azure semplifica questo processo. Il database SQL di Azure consente di analizzare il carico di lavoro, identificare le query che potrebbero essere eseguite più rapidamente con un nuovo indice e identificare gli indici inutilizzati o duplicati. Per altre informazioni sull'identificazione degli indici che devono essere modificati, vedere Find index recommendations in Azure portal (Trovare raccomandazioni per gli indici nel portale di Azure).

Passaggi successivi