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

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 descritti in questo articolo.

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

Stati del carico di lavoro

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

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

Questo articolo riguarda Azure SQL Database, 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. Sql Query Optimizer potrebbe produrre un piano non ottimale a causa di un indice mancante, statistiche non aggiornate, una stima errata del numero di righe da elaborare o 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.

Risoluzione di query con piani di esecuzione di query non ottimali

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

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

Un problema relativo al piano sensibile ai parametri 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 in esecuzioni consecutive. I piani che non sono 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 in Azure SQL Database.
  • 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 per la 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 sia già quello 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. Oppure 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 Query Store hint 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 parametrizzazione non corretta

Quando una query include 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 per una query usando lo stesso modello, ma valori letterali diversi può comportare un utilizzo elevato della CPU. Analogamente, se si parametrizza solo parzialmente una query che continua ad avere valori letterali, il motore di database non parametrizza ulteriormente la query.

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

  • Le modifiche apportate allo schema vengono a cui fa riferimento la query
  • Le modifiche dei 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 di 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 di 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 dei dati selezionata originariamente per la prima versione dell'esecuzione della query. Eventuali modifiche fisiche agli oggetti a cui si fa riferimento potrebbero 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 ha più processori, potrebbe essere scelto un piano parallelo. Per altre informazioni sul parallelismo in Azure SQL Database, vedere Configurare max degree of parallelism (MAXDOP) in Azure SQL Database.

  • 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 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 un piano di esecuzione delle query modificato.

  • 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 al 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 predefinito) 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 sovrautilizzate. Se il piano di query è ottimale, la query (e il database) potrebbe raggiungere i limiti delle risorse per il database o il pool elastico. Un esempio potrebbe essere la velocità effettiva di scrittura del log in eccesso 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 in Azure SQL Database e Ridimensionare le risorse del pool elastico in Azure SQL Database.

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 di utilizzo 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 prime query che usano 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 di 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:

  • Utilizzo delle risorse modificato: 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 implica la modifica del volume del carico di lavoro. Le regressioni nel piano di esecuzione delle query e le modifiche apportate alla 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 i problemi relativi all'attesa correlati ai problemi di esecuzione, il problema di prestazioni è in genere rappresentato dalle query in 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 Azure SQL Database 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 cpu elevata, le statistiche di attesa e Query Store 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 di timeout. Non mostrano i dati per l'esecuzione di 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