Tipi rilevabili di colli di bottiglia delle prestazioni delle query in SQL Server e Istanza gestita di SQL di Azure

Articolo
03/18/2024

Si applica a:SQL Server Istanza gestita di SQL di Azure

Per provare a risolvere un collo di bottiglia delle prestazioni, iniziare determinando se il problema si verifica mentre la query si trova in esecuzione o in attesa. A seconda di questa informazione, si applicano risoluzioni diverse. I problemi e le risoluzioni relativi a ogni tipo di problema sono illustrati in questo articolo.

È possibile usare le DMV di SQL Server per rilevare questi tipi di colli di bottiglia delle prestazioni.

Problemi relativi all’esecuzione: I problemi relativi all'esecuzione sono in genere correlati a problemi di compilazione, con un conseguente piano di query non ottimale, o a risorse insufficienti o sovrautilizzate. Problemi correlati all'attesa: i problemi correlati all'attesa sono in genere correlati a:

Blocchi (blocco)
I/O
Contesa relativa all'utilizzo di tempdb
Attese di concessione di memoria

Questo articolo riguarda SQL Server e Istanza gestita di SQL di Azure, vedere anche Tipi rilevabili di colli di bottiglia delle prestazioni delle query in database SQL di Azure.

Problemi di compilazione che causano un piano di query non ottimale

Un piano non ottimale generato da SQL Query Optimizer può essere la causa di un rallentamento delle prestazioni delle query. Il Query Optimizer di SQL potrebbe produrre un piano di esecuzione non ottimale a causa di un indice mancante, di statistiche non aggiornate, di una stima errata del numero di righe da elaborare o di una stima errata della memoria necessaria. Se si sa che la query è stata eseguita più velocemente in passato o in un'altra istanza, confrontare i piani di esecuzione effettivi per verificare se sono diversi.

Identificare gli indici mancanti usando gli indici mancanti nelle DMV e nei piani di esecuzione delle query. Questo articolo illustra come rilevare e ottimizzare gli indici non cluster usando richieste di indice mancanti.
Provare ad aggiornare le statistiche o ricompilare gli indici per ottenere un piano migliore. Abilitare la correzione automatica del piano del database per attenuare automaticamente questi problemi.
Come passaggio avanzato per la risoluzione dei problemi, usare gli hint di Query Store per applicare hint di query usando Query Store, senza apportare modifiche al codice.
Provare a modificare il livello di compatibilità del database e implementare l'elaborazione intelligente delle query. Il Query Optimizer di SQL può generare un piano di query diverso a seconda del livello di compatibilità per il database. I livelli di compatibilità più elevati offrono più funzionalità di elaborazione delle query intelligenti.

L'esempio sull’ottimizzazione e hint delle query nell’articolo Ottimizzare applicazioni e database per le prestazioni mostra l'impatto di un piano di query non ottimale a causa di una query con parametri, come rilevare questa condizione e come usare un hint per la query per risolvere.

Per altre informazioni sull'elaborazione delle query, vedere Guida sull'architettura di elaborazione delle query.
Per modificare i livelli di compatibilità del database e altre informazioni sulle differenze tra i livelli di compatibilità, vedere ALTER DATABASE.
Per altre informazioni sulla stima della cardinalità, vedere Stima della cardinalità

Risoluzione di query con piani di esecuzione di query non ottimali

Le sezioni seguenti illustrano come risolvere le query con un piano di esecuzione delle query non ottimale.

Query con problemi relativi al piano sensibile ai parametri (PSP)

Un problema del piano sensibile ai parametri si verifica quando Query Optimizer genera un piano di esecuzione di query ottimale solo per un valore di parametro specifico (un o set di valori) e il piano memorizzato nella cache non è quindi ottimale per i valori dei parametri usati in esecuzioni consecutive. I piani non ottimali possono quindi causare problemi di prestazioni delle query e ridurre la velocità effettiva complessiva del carico di lavoro.

Per altre informazioni sull'analisi dei parametri e l’elaborazione delle query, vedere Guida sull'architettura di elaborazione delle query.

Diverse soluzioni alternative possono attenuare i problemi di PSP. Ogni soluzione alternativa presenta i propri compromessi e svantaggi:

Una nuova funzionalità introdotta con SQL Server 2022 (16.x) è l'ottimizzazione del piano sensibile ai parametri, che tenta di attenuare la maggior parte dei piani di query non ottimali causati dalla riservatezza dei parametri. Questa opzione è abilitata con il livello di compatibilità del database 160.
Usare l'hint per la query RECOMPILE a ogni esecuzione di query. Questa soluzione alternativa offre tempi di compilazione ridotti e una maggiore quantità di CPU per una migliore qualità del piano. L'opzione RECOMPILE spesso non è attuabile per i carichi di lavoro che richiedono una velocità effettiva elevata.
Usare l'hint per la query OPTION (OPTIMIZE FOR…) per eseguire l'override del valore del parametro effettivo con un valore di parametro tipico che genera un piano adeguato per la maggior parte dei valori di parametro possibili. Questa opzione richiede una buona conoscenza dei valori di parametro ottimali e delle caratteristiche del piano associate.
Usare l'hint per la query OPTION (OPTIMIZE FOR UNKNOWN) per eseguire l'override del valore del parametro effettivo invece di usare la media del vettore di densità. A questo scopo è anche possibile acquisire i valori dei parametri in entrata in variabili locali e quindi usare le variabili locali all'interno dei predicati invece di usare i parametri stessi. Per risolvere il problema, la densità media deve essere adeguata.
Disabilitare completamente l'analisi dei parametri usando l'hint per la query DISABLE_PARAMETER_SNIFFING.
Usare l'hint per la query KEEPFIXEDPLAN per evitare la ricompilazione nella cache. Questa soluzione alternativa presuppone che il piano comune adeguato sia quello già nella cache. È anche possibile disabilitare gli aggiornamenti automatici alle statistiche per ridurre le probabilità che il piano valido venga rimosso e che venga compilato un nuovo piano non valido.
Forzare il piano usando in modo esplicito l'hint per la query USE PLAN riscrivendo la query e aggiungendo l'hint nel testo della query. In alternativa, impostare un piano specifico usando Query Store o abilitando l'ottimizzazione automatica.
Sostituire la procedura singola con un set annidato di procedure, ognuna delle quali può essere usata in base alla logica condizionale e ai valori dei parametri associati.
Creare alternative basate sull'esecuzione di stringhe dinamiche per una definizione di procedura statica.

Per applicare gli hint per la query, modificare la query o usare hint di Query Store per applicare l'hint senza apportare modifiche al codice. Nelle versioni di SQL Server precedenti a SQL Server 2022 usare le guide di piano.

Per altre informazioni sulla risoluzione dei problemi di PSP, vedere questi post di blog:

Attività di compilazione causata da una parametrizzazione non corretta

Quando una query usa valori letterali, il motore di database imposta automaticamente i parametri dell'istruzione oppure un utente imposta i parametri dell'istruzione in modo esplicito per ridurre il numero di compilazioni. Un numero elevato di compilazioni di una query con lo stesso modello, ma valori letterali diversi può causare un utilizzo elevato della CPU. Analogamente, se si impostano solo parzialmente i parametri di una query che continua a includere valori letterali, il motore di database non imposta gli altri parametri della query.

Di seguito è riportato un esempio di una query con solo alcuni parametri impostati:

SELECT *
FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c1 = @p1 AND t2.c2 = '961C3970-0E54-4E8E-82B6-5545BE897F8F';

Nell'esempio, t1.c1 usa @p1, ma t2.c2 continua a usare il GUID come valore letterale. In questo caso, se si modifica il valore per c2, la query verrà considerata come una query diversa e verrà eseguita una nuova compilazione. Per ridurre le compilazioni in questo esempio, è necessario parametrizzare anche il GUID.

La query seguente illustra il conteggio delle query per hash di query per determinare se i parametri di una query sono impostati correttamente o meno:

SELECT TOP 10
  q.query_hash
  , count (distinct p.query_id ) AS number_of_distinct_query_ids
  , min(qt.query_sql_text) AS sampled_query_text
FROM sys.query_store_query_text AS qt
  JOIN sys.query_store_query AS q
     ON qt.query_text_id = q.query_text_id
  JOIN sys.query_store_plan AS p
     ON q.query_id = p.query_id
  JOIN sys.query_store_runtime_stats AS rs
     ON rs.plan_id = p.plan_id
  JOIN sys.query_store_runtime_stats_interval AS rsi
     ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
  rsi.start_time >= DATEADD(hour, -2, GETUTCDATE())
  AND query_parameterization_type_desc IN ('User', 'None')
GROUP BY q.query_hash
ORDER BY count (distinct p.query_id) DESC;

Fattori che influiscono sulle modifiche del piano di query

Una ricompilazione del piano di esecuzione delle query può portare alla generazione di un piano di query diverso dal piano memorizzato nella cache originale. Un piano originale esistente potrebbe essere ricompilato automaticamente per diversi motivi:

Modifiche apportate allo schema vengono a cui fa riferimento la query
Modifiche ai dati alle tabelle fanno riferimento alla query
Modifica alle opzioni del contesto della query

Un piano compilato potrebbe essere espulso dalla cache per diversi motivi, ad esempio:

Riavvi dell'istanza
Modifica configurazione in ambito database
Utilizzo elevato della memoria
Richieste esplicite per cancellare la cache

Se si usa un hint RECOMPILE, un piano non verrà memorizzato nella cache.

Una ricompilazione (o una nuova compilazione dopo la rimozione della cache) può comunque comportare la generazione di un piano di esecuzione della query identico all'originale. Quando il piano cambia rispetto al piano precedente o originale, è probabile che queste spiegazioni siano:

Progettazione fisica modificata: ad esempio, gli indici appena creati coprono in modo più efficace i requisiti di una query. È possibile usare i nuovi indici in una nuova compilazione se Query Optimizer decide che l'uso di tale nuovo indice è più ottimale rispetto all'uso della struttura di dati selezionata originariamente per la prima versione dell'esecuzione della query. Qualsiasi modifica fisica agli oggetti a cui si fa riferimento potrebbe comportare una nuova scelta di piano in fase di compilazione.
Differenze tra le risorse del server: quando un piano in un sistema differisce dal piano in un altro sistema, la disponibilità delle risorse, ad esempio il numero di processori disponibili, può influenzare il piano generato. Ad esempio, se un sistema dispone di più processori, potrebbe essere scelto un piano parallelo. Per altre informazioni sull'opzione relativa al parallelismo, vedere Configurare l'opzione di configurazione del server massimo grado di parallelismo.
Statistiche diverse: le statistiche associate agli oggetti a cui si fa riferimento potrebbero essere state modificate o potrebbero essere materialmente diverse dalle statistiche del sistema originale. Se le statistiche cambiano e si verifica una ricompilazione, Query Optimizer usa le statistiche a partire da quando vengono modificate. Le distribuzioni e le frequenze dei dati delle statistiche modificate potrebbero differire da quelle della compilazione originale. Queste modifiche vengono usate per creare stime della cardinalità. (Le stime della cardinalità sono il numero di righe che devono essere propagate attraverso l'albero delle query logiche). Le modifiche apportate alle stime della cardinalità possono portare a scegliere diversi operatori fisici e ordini di operazioni associati. Anche le modifiche minime apportate alle statistiche possono comportare una modifica del piano di esecuzione delle query.
Modifica del livello di compatibilità del database o versione dello strumento di stima della cardinalità: le modifiche apportate al livello di compatibilità del database possono abilitare nuove strategie e funzionalità che potrebbero comportare un piano di esecuzione di query diverso. Oltre il livello di compatibilità del database, un flag di traccia disabilitato o abilitato 4199 o uno stato modificato della configurazione con ambito database QUERY_OPTIMIZER_HOTFIXES può anche influenzare le scelte del piano di esecuzione delle query in fase di compilazione. Anche i flag di traccia 9481 (forzare piano CE precedente) e 2312 (forzare piano CE predefinito) influiscono sul piano.

Problemi relativi ai limiti delle risorse in Istanza gestita di SQL di Azure

Le prestazioni delle query lente non correlate ai piani di query non ottimali e gli indici mancanti sono in genere correlati a risorse insufficienti o sovrautilizzate. Se il piano di query è ottimale, la query (e il database) potrebbero raggiungere i limiti delle risorse per l'istanza gestita. Un esempio potrebbe essere l'eccesso della velocità effettiva di scrittura del log per il livello di servizio.

La DMV sys.dm_db_resource_stats restituisce il consumo di CPU, I/O e memoria per il database. È presente una riga per ogni 15 secondi, anche se non esiste alcuna attività nel database. I dati cronologici vengono mantenuti per un'ora.
La DMV sys.resource_stats restituisce i dati di archiviazione e di uso della CPU per un’istanza gestita di SQL di Azure. I dati vengono raccolti e aggregati in intervalli di 15 secondi.
Numerose singole query hanno un utilizzo cumulativo elevato della CPU

Se si identifica il problema come risorsa insufficiente, è possibile aggiornare le risorse per aumentare la capacità del database per assorbire i requisiti della CPU. Per informazioni su come ridimensionare un'istanza gestita, vedere Limiti delle risorse a livello di istanza

Problemi di prestazioni causati da un aumento del volume del carico di lavoro

Un aumento del traffico dell'applicazione e del volume del carico di lavoro può causare un aumento dell'utilizzo della CPU. Tuttavia, è necessario prestare attenzione a diagnosticare correttamente questo problema. Quando viene visualizzato un problema elevato della CPU, rispondere a queste domande per determinare se l'aumento è causato da modifiche al volume del carico di lavoro:

Le query eseguite dall'applicazione sono la causa del problema di utilizzo elevato della CPU?
Per le query con utilizzo più elevato della CPU che possono essere identificate:
- Erano presenti più piani di esecuzione associati alla stessa query? In caso affermativo, perché?
- Per le query con lo stesso piano di esecuzione, i tempi di esecuzione erano coerenti? Il numero di esecuzioni è aumentato? In tal caso, è probabile che l'aumento del carico di lavoro causi problemi di prestazioni.

Per riepilogare, se il piano di esecuzione della query non è stato eseguito in modo diverso, ma l'utilizzo della CPU è aumentato con il numero di esecuzioni, è probabile che esista un problema di prestazioni correlato all'aumento del carico di lavoro.

Non è sempre facile identificare una modifica del volume del carico di lavoro che causa un problema di CPU. Tenere presente questi fattori:

Modifica utilizzo delle risorse:: si consideri, ad esempio, uno scenario in cui l'utilizzo della CPU è aumentato fino all'80% per un lungo periodo di tempo. L'utilizzo della CPU in sé non significa che il volume di carico di lavoro sia cambiato. Anche le regressioni del piano di esecuzione di una query e le modifiche alla distribuzione dei dati possono contribuire al maggior utilizzo di risorse anche se l'applicazione esegue lo stesso identico carico di lavoro.
Comparsa di una nuova query: un'applicazione potrebbe generare un nuovo set di query in momenti diversi.
Aumento o diminuzione del numero di richieste: questo scenario è la misura più ovvia di un carico di lavoro. Il numero di query non corrisponde sempre a un maggiore utilizzo delle risorse. Tuttavia, questa metrica rimane un segnale significativo, supponendo che gli altri fattori restino invariati.
Parallelismo: l'eccessivo parallelismo può peggiorare altre prestazioni simultanee del carico di lavoro privando altre query di risorse del thread di lavoro e CPU. Per altre informazioni sull'opzione relativa al parallelismo, vedere Configurare l'opzione di configurazione del server massimo grado di parallelismo.

Dopo aver eliminato un piano non ottimale e problemi correlati all'attesa correlati ai problemi di esecuzione, il problema di prestazioni è in genere l'attesa di alcune risorse. I problemi correlati all'attesa potrebbero essere causati da:

Processi bloccati:

Una query potrebbe contenere il blocco sugli oggetti nel database mentre altre tentano di accedere agli stessi oggetti. È possibile identificare i blocchi delle query usando DMV. Per altre informazioni, vedere Informazioni e risoluzione dei problemi di blocco.
Problemi di I/O

Le query potrebbero attendere che le pagine vengano scritte nei file di dati o di log. In questo caso, controllare le statistiche di attesa INSTANCE_LOG_RATE_GOVERNOR, WRITE_LOG o PAGEIOLATCH_* nel DMV. Vedere come usare i DMV per identificare i problemi di prestazioni delle operazioni di I/O.
Problemi relativi a Tempdb

Se il carico di lavoro usa tabelle temporanee o si verificano perdite tempdb nei piani, le query potrebbero avere un problema con la velocità effettiva tempdb. Per un'ulteriore analisi, vedere come individuare i problemi di tempdb.
Problemi correlati alla memoria

Se il carico di lavoro non ha memoria sufficiente, l'aspettativa di vita della pagina potrebbe diminuire oppure le query potrebbero ottenere meno memoria rispetto alle esigenze. In alcuni casi, l'intelligenza predefinita in Query Optimizer risolverà i problemi correlati alla memoria. Vedere l'uso di DMV per identificare i problemi di concessione della memoria. Se si verificano errori di memoria insufficiente, esaminare sys.dm_os_out_of_memory_events. Si consideri anche il livello della serie Premium ottimizzata per la memoria dell'hardware di Istanza gestita di SQL di Azure con un rapporto di memoria superiore a vCore.

Metodi per visualizzare le categorie di attesa principali

Questi metodi vengono comunemente usati per visualizzare le categorie principali di tipi di attesa:

Usare Query Store per trovare le statistiche di attesa per ogni query nel tempo. In Query Store, i pi di attesa vengono combinati in categorie di attesa. Il mapping delle categorie di attesa ai tipi di attesa è disponibile in sys.query_store_wait_stats.
Utilizzare sys.dm_db_wait_stats restituisce informazioni su tutte le attese incontrate dai thread eseguiti durante un'operazione di query. È possibile usare questa vista aggregata per diagnosticare i problemi di prestazioni con l'Istanza gestita di SQL di Azure o l'istanza di SQL Server. Le query possono essere in attesa di risorse, attese code o attese esterne.
Utilizzare sys_waiting_tasks per ottenere informazioni relative alla coda di attesa dell'attività in attesa su alcune risorse.

Negli scenari con utilizzo elevato della CPU, query store e statistiche di attesa potrebbero non riflettere l'utilizzo della CPU se:

Le query con utilizzo elevato della CPU sono ancora in esecuzione.
Le query con utilizzo elevato della CPU erano in esecuzione quando si è verificato un failover.

Le DMV che tengono traccia di Query Store e le statistiche di attesa mostrano i risultati solo per le query completate e con timeout. Non mostrano i dati per le istruzioni attualmente in esecuzione fino al termine delle istruzioni. Utilizzare la vista dinamica DMV sys.dm_exec_requests per tenere traccia delle query attualmente in esecuzione e della durata del ruolo di lavoro associato.

Suggerimento

Strumenti aggiuntivi: