Regolazione automatica

Si applica a: SQL Server 2017 (14.x) e versioni successive Azure SQL DatabaseIstanza gestita di SQL di Azuredatabase SQL in Microsoft Fabric

L'ottimizzazione automatica è una funzionalità di database che fornisce informazioni dettagliate sui potenziali problemi di prestazioni delle query, consiglia soluzioni e risolve automaticamente i problemi identificati.

L'ottimizzazione automatica, introdotta in SQL Server 2017 (14.x), invia una notifica ogni volta che viene rilevato un potenziale problema di prestazioni e consente di applicare azioni correttive o consentire al motore di database di risolvere automaticamente i problemi di prestazioni. L'ottimizzazione automatica di SQL Server identifica e corregge i problemi di prestazioni causati dalle regressioni di scelta del piano di esecuzione delle query. L'ottimizzazione automatica nel database SQL di Azure e nel database SQL in Microsoft Fabric crea anche gli indici necessari e elimina gli indici inutilizzati. Per altre informazioni sui piani di esecuzione delle query, vedere Piani di esecuzione.

Il motore di database di SQL Server monitora le query eseguite nel database e migliora automaticamente le prestazioni del carico di lavoro. Il motore di database dispone di un meccanismo di intelligence predefinito che consente di ottimizzare e migliorare automaticamente le prestazioni delle query adattando dinamicamente il database al carico di lavoro. Sono disponibili due funzionalità di ottimizzazione automatica:

• La correzione automatica dei piani identifica i piani di esecuzione delle query problematici, ad esempio la sensibilità dei parametri o i problemi di analisi dei parametri e corregge i problemi di prestazioni correlati al piano di esecuzione delle query forzando l'ultimo piano valido noto prima che si verifichi la regressione. Si applica a: SQL Server (a partire da SQL Server 2017 (14.x)), Azure SQL Database, e SQL Database in Microsoft Fabric e Istanza Gestita di Azure SQL

• La gestione automatica degli indici identifica gli indici da aggiungere nel database e gli indici da rimuovere. Si applica a: Database SQL di Azure e database SQL in Microsoft Fabric

Note

In questo articolo le funzionalità e i comportamenti del database SQL di Azure si applicano anche al database SQL in Microsoft Fabric.

Perché l'ottimizzazione automatica?

Tre delle attività principali nell'amministrazione classica del database sono il monitoraggio del carico di lavoro, l'identificazione di query critiche Transact-SQL e l'identificazione degli indici da aggiungere per migliorare le prestazioni o gli indici usati raramente e che possono essere rimossi per migliorare le prestazioni. Il motore di database di SQL Server fornisce informazioni dettagliate sulle query e sugli indici da monitorare. Tuttavia, il monitoraggio costante di un database è un'attività difficile e noiosa, soprattutto quando si gestiscono molti database. La gestione di un numero elevato di database potrebbe essere impossibile da eseguire in modo efficiente. Anziché monitorare e ottimizzare manualmente il database, è possibile delegare alcune delle azioni di monitoraggio e ottimizzazione al motore di database usando la funzionalità di ottimizzazione automatica.

Come funziona l'ottimizzazione automatica?

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

Questo processo consente al database di adattarsi dinamicamente al carico di lavoro individuando gli indici e i piani che potrebbero migliorare le prestazioni dei carichi di lavoro e gli indici che influiscono sui carichi di lavoro. 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. Questo processo di verifica è una funzionalità chiave che garantisce qualsiasi modifica apportata dall'ottimizzazione automatica non riduce le prestazioni complessive del carico di lavoro.

Correzione automatica dei piani

La correzione automatica dei piani è una funzionalità di ottimizzazione automatica che identifica la regressione scelta del piano di esecuzione e risolve automaticamente il problema forzando l'ultimo piano valido noto. Per altre informazioni sui piani di esecuzione delle query e Query Optimizer, vedere Guida all'architettura di elaborazione delle query.

Important

La correzione automatica dei piani dipende dall'abilitazione di Query Store nel database per il rilevamento del carico di lavoro.

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

Il motore di database di SQL Server può usare piani di esecuzione diversi per eseguire le query Transact-SQL. I piani di query dipendono dalle statistiche, dagli indici e da altri fattori. Il piano ottimale che deve essere usato per eseguire una query Transact-SQL potrebbe cambiare nel tempo a seconda delle modifiche apportate a questi fattori. In alcuni casi, il nuovo piano potrebbe non essere migliore di quello precedente e il nuovo piano potrebbe causare una regressione delle prestazioni, ad esempio un problema correlato alla sensibilità dei parametri o all'analisi dei parametri .

Ogni volta che si nota che si è verificata una regressione di scelta del piano, è necessario trovare un piano valido precedente e forzarlo per essere usato invece di quello corrente. Questa operazione può essere eseguita usando la sp_query_store_force_plan procedura . Il motore di database in SQL Server 2017 (14.x) fornisce informazioni sui piani regrediti e sulle azioni correttive consigliate. Inoltre, il motore di database consente di automatizzare completamente questo processo e consentire al motore di database di risolvere eventuali problemi rilevati relativi alla modifica del piano.

Important

La correzione automatica dei piani deve essere usata nell'ambito di un aggiornamento del livello di compatibilità del database, dopo l'acquisizione di una baseline, per ridurre 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 più recente.

Correzione automatica della scelta del piano

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

Il motore di database rileva automaticamente qualsiasi potenziale regressione di scelta del piano, incluso il piano che deve essere usato invece del piano errato. Il piano di esecuzione risultante forzato dalla correzione automatica del piano sarà lo stesso o simile all'ultimo piano valido noto. Poiché il piano risultante potrebbe non essere identico all'ultimo piano noto, le prestazioni del piano forzato possono variare. In rari casi, la differenza di prestazioni può essere significativa e negativa; in questo caso, la correzione automatica del piano smetterà automaticamente di tentare di forzare il piano di sostituzione.

Quando il motore di database applica l'ultimo piano valido noto prima dell'esecuzione della regressione, monitora automaticamente le prestazioni del piano forzato. Se il piano forzato non è migliore del piano regredito, il nuovo piano verrà rimosso e il motore di database compilerà un nuovo piano. Se il motore di database verifica che il piano forzato sia migliore del piano regredito, il piano forzato verrà conservato. Verrà conservato fino a quando non si verifica una ricompilazione, ad esempio nella successiva modifica dello schema o dell'aggiornamento delle statistiche. Per ulteriori informazioni sulle limitazioni di forzatura del piano e sui tipi di piani che possono essere forzati, vedere Limitazioni di forzatura del piano.

Note

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 l'ultimo piano valido deve essere forzato ogni volta che viene rilevata una regressione delle modifiche del piano. L'ottimizzazione automatica è abilitata usando il comando seguente:

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

Dopo aver abilitato questa opzione, il motore di database forza automaticamente qualsiasi raccomandazione in cui il guadagno stimato della CPU è superiore a 10 secondi o il numero di errori nel nuovo piano è superiore al numero di errori nel piano consigliato e verificare che il piano forzato sia migliore di quello corrente.

Per abilitare l'ottimizzazione automatica nel database SQL di Azure e in Istanza gestita di SQL di Azure, vedere Abilitare l'ottimizzazione automatica nel database SQL di Azure tramite il portale di Azure.

Alternativa : correzione manuale della scelta del piano

Senza l'ottimizzazione automatica, gli utenti devono monitorare periodicamente il sistema e individuare le query degradate. Se un piano è regredito, l'utente deve trovare un piano valido precedente e forzarlo anziché quello corrente usando la sp_query_store_force_plan procedura. 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 noto valido deve monitorare le prestazioni della query eseguita con il piano forzato e verificare che il piano forzato funzioni come previsto. A seconda dei risultati del monitoraggio e dell'analisi, il piano potrebbe essere forzato oppure l'utente potrebbe trovare un altro modo per ottimizzare la query, riscrivendola, ad esempio. I piani imposti 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 sp_query_store_unforce_plan la procedura e consentire al motore di database di trovare il piano ottimale.

Tip

In alternativa, usare la visualizzazione Query con Piani Forzati per individuare e disabilitare la forzatura dei piani.

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 di scelta del piano usando le viste di sistema di Query Store. A partire da SQL Server 2017 (14.x), il motore di database rileva e mostra le potenziali regressioni di scelta del piano e le azioni consigliate da applicare nella DMV sys.dm_db_tuning_recommendations (Transact-SQL). La DMV mostra informazioni sul problema, sull'importanza del problema e dettagli, ad esempio la query identificata, l'ID del piano regredito, l'ID del piano usato come baseline per il confronto e l'istruzione Transact-SQL che può essere eseguita per risolvere il problema.

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

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

• Tipo dell'azione FORCE_LAST_GOOD_PLANconsigliata.
• Descrizione che contiene informazioni sul motivo per cui il motore di database ritiene che questa modifica del piano sia una potenziale regressione delle prestazioni.
• Data e ora quando viene rilevata la potenziale regressione.
• Punteggio di questa raccomandazione.
• Dettagli sui problemi, ad esempio ID del piano rilevato, ID del piano regredito, ID del piano che deve essere forzato a risolvere il problema, Transact-SQL script che potrebbe essere applicato per risolvere il problema e così via. I dettagli vengono archiviati in formato JSON.

Usare la query seguente per ottenere uno script che risolve il problema e informazioni aggiuntive 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;

Il set di risultati è il seguente.

reason	score	script	query_id	plan_id corrente	plan_id consigliato	stima_guadagno	soggetto a errori
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 salvati se il piano consigliato verrebbe usato per l'esecuzione di query anziché per il piano corrente. Il piano consigliato dovrebbe essere forzato anziché il piano corrente se l'incremento è maggiore di 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 noioso. L'ottimizzazione automatica semplifica notevolmente questo processo.

Note

I dati nella sys.dm_db_tuning_recommendations DMV 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 apprende il carico di lavoro e garantisce che i dati siano sempre indicizzati in modo ottimale. La progettazione corretta degli indici è fondamentale per ottenere prestazioni ottimali del carico di lavoro e la gestione automatica degli indici consente di ottimizzare gli indici. La gestione automatica degli indici può risolvere i problemi di prestazioni nei database indicizzati in modo non corretto oppure 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 i dati dalle tabelle.
• Identifica gli indici o gli indici ridondanti che non sono stati usati in un periodo di tempo più lungo che potrebbe essere rimosso. La rimozione di indici non necessari migliora le prestazioni delle query che aggiornano i dati nelle tabelle.

Perché è necessaria la gestione degli indici?

Gli indici velocizzano alcune query che leggono dati dalle tabelle, ma possono rallentare le query che aggiornano i dati. È necessario analizzare attentamente quando creare un indice e le colonne da includere nell'indice. Alcuni indici potrebbero non essere necessari dopo un certo periodo di tempo. Pertanto, è necessario identificare ed eliminare periodicamente questi indici che non offrono alcun vantaggio. Se si ignorano gli indici inutilizzati, le prestazioni delle query che aggiornano i dati verrebbero ridotte senza alcun vantaggio per le query che leggono i dati. Gli indici inutilizzati influiscono anche sulle prestazioni complessive del sistema perché gli aggiornamenti aggiuntivi richiedono la registrazione non necessaria.

Trovare il set ottimale di indici che migliorano le prestazioni delle query che leggono dati dalle tabelle e hanno un impatto minimo sugli aggiornamenti potrebbero richiedere un'analisi continua e complessa.

Il database SQL di Azure usa regole avanzate e di intelligence predefinite che analizzano le query, identificano gli indici che sarebbero ottimali per i carichi di lavoro correnti e identificano gli indici che potrebbero essere necessari per essere rimossi. Il database SQL di Azure garantisce un set minimo necessario di indici che ottimizzano le query che leggono i dati, con un impatto ridotto sulle altre query.

Gestione automatica degli indici

Oltre al rilevamento, il database SQL di Azure può applicare automaticamente raccomandazioni identificate. Se si ritiene che le regole predefinite migliorino le prestazioni del database, è possibile consentire al database SQL di Azure di gestire automaticamente gli 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 verrà conservato solo se vengono migliorate le prestazioni delle query interessate. L'indice eliminato verrà ricreato automaticamente se sono presenti alcune query che vengono eseguite più lentamente a causa dell'assenza dell'indice.

Considerazioni sulla gestione automatica degli indici

Le azioni necessarie per creare gli indici necessari nel database SQL di Azure potrebbero usare le risorse e influire in modo temporale sulle prestazioni del carico di lavoro. Per ridurre al minimo l'impatto della creazione dell'indice sulle prestazioni del carico di lavoro, il database SQL di Azure trova un intervallo di tempo appropriato per qualsiasi operazione di gestione degli indici. L'azione di ottimizzazione viene posticipata se il database necessita di risorse per eseguire il carico di lavoro e viene riavviato quando il database dispone di risorse inutilizzate sufficienti che possono essere usate per l'attività di manutenzione. Una funzionalità importante nella gestione automatica degli indici è una 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 abbia migliorato le prestazioni complessive. Se non ha apportato miglioramenti significativi, l'azione viene immediatamente ripristinata. In questo modo, il database SQL di Azure garantisce che le azioni di ottimizzazione automatica non influiscono negativamente sulle prestazioni del carico di lavoro. Gli indici creati dall'ottimizzazione automatica sono trasparenti per l'operazione di manutenzione nello schema sottostante. Le modifiche dello schema, ad esempio l'eliminazione o la ridenominazione delle colonne, non vengono bloccate dalla presenza di indici creati automaticamente. Gli indici creati automaticamente dal database SQL di Azure vengono eliminati immediatamente quando vengono eliminati la tabella o le colonne correlate.

Alternativa: gestione manuale degli indici

Senza la gestione automatica degli indici, un utente o un amministratore del database deve eseguire manualmente una query sulla vista sys.dm_db_missing_index_details (Transact-SQL) o usare il report Dashboard prestazioni in Management Studio per trovare indici che potrebbero migliorare le prestazioni, creare indici usando i dettagli forniti in questa visualizzazione e monitorare manualmente le prestazioni della query. Per trovare gli indici da eliminare, gli utenti devono monitorare le statistiche di utilizzo operativo degli indici per trovare indici usati raramente.

Il database SQL di Azure semplifica questo processo. Il database SQL di Azure analizza il carico di lavoro, identifica le query che possono essere eseguite più velocemente con un nuovo indice e identifica gli indici inutilizzati o duplicati. Per altre informazioni sull'identificazione degli indici da modificare, vedere Trovare raccomandazioni sugli indici nel portale di Azure.

Passaggi successivi

• Ottimizzazione automatica nel database SQL di Azure e nell'istanza gestita di SQL di Azure
• ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL)
• sys.database_automatic_tuning_options (Transact-SQL)
• sys.dm_db_tuning_recommendations (Transact-SQL)
• sys.dm_db_missing_index_details (Transact-SQL)
• sp_query_store_force_plan (Transact-SQL)
• sys.query_store_plan_forcing_locations (Transact-SQL)
• sp_query_store_unforce_plan (Transact-SQL)
• sys.database_query_store_options (Transact-SQL)
• sys.dm_os_sys_info (Transact-SQL)
• Funzioni JSON
• Piani di esecuzione
• Monitoraggio e ottimizzazione delle prestazioni
• Strumenti per il monitoraggio e l'ottimizzazione delle prestazioni
• Monitoraggio delle prestazioni tramite Query Store
• Assistente dell'ottimizzazione delle query