Tipi rilevabili di colli di bottiglia delle prestazioni delle query nel database SQL di Azure

  • Articolo

Si applica a:Database 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. Usare il diagramma seguente per comprendere i fattori che possono causare un problema correlato all'esecuzione o un problema correlato all'attesa. I problemi e le risoluzioni relativi a ogni tipo di problema sono illustrati in questo articolo.

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

Workload states

Problemi relativi all'esecuzione: i problemi relativi all'esecuzione sono in genere correlati a problemi di compilazione che causano problemi di esecuzione o piani di query non ottimali correlati a risorse insufficienti o sovrautilate. Problemi correlati all'attesa: i problemi correlati all'attesa sono in genere correlati a:

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

Questo articolo riguarda il database SQL di Azure, vedere anche Tipi rilevabili di colli di bottiglia delle prestazioni delle query in Istanza gestita di 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. Query Optimizer SQL potrebbe produrre un piano 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 imprecisa della memoria richiesta. Se si sa che la query è stata eseguita più velocemente in passato o in un altro database, confrontare i piani di esecuzione effettivi per verificare se sono diversi.

  • Identificare gli indici mancanti usando uno di questi metodi:

  • Provare ad aggiornare le statistiche o ricompilare gli indici per ottenere un piano migliore. Abilitare la correzione automatica dei piani 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.

    Questo esempio di ottimizzazione e hint delle query 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.

  • Provare a modificare il livello di compatibilità del database e implementare l'elaborazione intelligente delle query. Sql Query Optimizer può generare un piano di query diverso a seconda del livello di compatibilità per il database. I livelli di compatibilità più elevati offrono funzionalità di elaborazione delle query più intelligenti.

    • Per altre informazioni sull'elaborazione delle query, vedere Guida all'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 relativo al piano sensibile ai parametri (PSP) si verifica quando Query Optimizer genera un piano di esecuzione di query ottimale solo per un valore di parametro specifico (o un set di valori) e il piano memorizzato nella cache non è quindi ottimale per i valori dei parametri usati nelle 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 sull'elaborazione di query, vedere la guida all'architettura di elaborazione delle query.

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

  • 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 nel database SQL di Azure.
  • 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 è possibile per i carichi di lavoro che richiedono una velocità effettiva elevata.
  • Usare l'hint di query OPTION (OPTIMIZE FOR...) per eseguire l'override del valore effettivo del parametro con un valore di parametro tipico che produce un piano sufficiente per la maggior parte delle possibilità di valore del parametro. Questa opzione richiede una buona conoscenza dei valori di parametro ottimali e delle caratteristiche del piano associate.
  • Usare l'hint di query OPTION (OPTIMIZE FOR UNKNOWN) per eseguire l'override del valore effettivo del parametro e usare invece la media del vettore di densità. A tale scopo, è anche possibile acquisire i valori dei parametri in ingresso nelle variabili locali e quindi usare le variabili locali all'interno dei predicati anziché usare i parametri stessi. Per questa correzione, la densità media deve essere sufficiente.
  • Disabilitare completamente l'analisi dei parametri usando l'hint per la query DISABLE_PARAMETER_SNIFFING .
  • Usare l'hint per la query KEEPFIXEDPLAN per impedire ricompilazione nella cache. Questa soluzione alternativa presuppone che il piano comune sufficientemente valido sia quello già presente nella cache. È anche possibile disabilitare gli aggiornamenti automatici delle 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 hint per la query, modificare la query o usare gli hint di Query Store per applicare l'hint senza apportare modifiche al codice.

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 ha valori letterali, il motore di database parametrizza automaticamente l'istruzione o un utente parametrizza in modo esplicito l'istruzione 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 query parzialmente con parametri:

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

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

La query seguente mostra il numero di query in base all'hash della query per determinare se una query è parametrizzata correttamente:

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ò comportare un piano di query generato diverso dal piano memorizzato nella cache originale. Un piano originale esistente potrebbe essere ricompilato automaticamente per diversi motivi:

  • Le modifiche apportate allo schema vengono a cui fa riferimento la query
  • Le modifiche ai dati alle tabelle fanno riferimento alla query
  • Le opzioni del contesto di query sono state modificate

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

  • Riavvii dell'istanza
  • Modifiche alla configurazione con 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 sul parallelismo nel database SQL di Azure, vedere Configurare il massimo grado di parallelismo (MAXDOP) nel database SQL di Azure.

  • 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 (forza ce legacy) e 2312 (forza ce predefinita) influiscono sul piano.

Problemi relativi ai limiti delle risorse

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 sovrautilate. Se il piano di query è ottimale, la query (e il database) potrebbero raggiungere i limiti delle risorse per il database o il pool elastico. Un esempio potrebbe essere l'eccesso della velocità effettiva di scrittura del log per il livello di servizio.

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 altre informazioni, vedere Ridimensionare le risorse di database singolo nel database SQL di Azure e Ridimensionare le risorse del pool elastico nel database SQL di Azure.

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 dell'applicazione sono la causa del problema elevato della CPU?

  • Per le principali query che utilizzano la CPU che è possibile identificare:

    • Sono stati associati più piani di esecuzione alla stessa query? In caso affermativo, perché?
    • Per le query con lo stesso piano di esecuzione, i tempi di esecuzione sono coerenti? Il numero di esecuzioni è aumentato? In tal caso, è probabile che l'aumento del carico di lavoro causi problemi di prestazioni.

In sintesi, se il piano di esecuzione della query non è stato eseguito in modo diverso, ma l'utilizzo della CPU è aumentato insieme al numero di esecuzioni, è probabile che il problema delle prestazioni sia correlato a un 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 dell'utilizzo delle risorse: si consideri, ad esempio, uno scenario in cui l'utilizzo della CPU è aumentato all'80% per un lungo periodo di tempo. L'utilizzo della CPU da solo non significa che il volume del carico di lavoro sia cambiato. Le regressioni nel piano di esecuzione delle query e le modifiche nella distribuzione dei dati possono contribuire anche a un maggiore utilizzo delle risorse anche se l'applicazione esegue lo stesso carico di lavoro.

  • Aspetto 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 è ancora un segnale significativo, presupponendo che altri fattori siano invariati.

Usare Intelligent Insights per rilevare gli aumenti del carico di lavoro e pianificare le regressioni.

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:

Metodi per visualizzare le categorie di attesa principali

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

  • Usare Intelligent Insights per identificare le query con riduzione delle prestazioni a causa di un aumento delle attese
  • 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. È possibile trovare il mapping delle categorie di attesa per i tipi di attesa in sys.query_store_wait_stats.
  • Usare sys.dm_db_wait_stats per restituire informazioni su tutte le attese rilevate dai thread eseguiti durante un'operazione di query. È possibile usare questa vista aggregata per diagnosticare i problemi di prestazioni con il database SQL di Azure e anche con query e batch specifici. Le query possono essere in attesa di risorse, attese code o attese esterne.
  • Usare sys.dm_os_waiting_tasks per restituire informazioni sulla coda di attività in attesa di 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 venivano eseguite 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. Usare la visualizzazione a gestione dinamica sys.dm_exec_requests per tenere traccia delle query attualmente in esecuzione e del tempo di lavoro associato.

Suggerimento

Strumenti aggiuntivi:

Passaggi successivi