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

È possibile usare database watcher o DMV per rilevare questi tipi di colli di bottiglia delle prestazioni.

Diagramma degli stati del carico di lavoro, in esecuzione e in attesa.

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 problemi di esecuzione correlati 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 tempdb
  • Attese di concessione di memoria

Questo articolo tratta del database SQL di Azure. Vedere anche Tipi rilevabili di colli di bottiglia delle prestazioni delle query nell'Istanza gestita di SQL di Azure.

Problemi di compilazione che comportano un piano di query non ottimale

Un piano non ottimale generato da Query Optimizer di SQL potrebbe essere la causa di un rallentamento delle prestazioni delle query. Query Optimizer 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 altro database, confrontare i piani di esecuzione effettivi per verificare se sono diversi.

Risolvere le 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 (PSP) si verifica quando uno scenario in cui 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 produttività complessiva del carico di lavoro.

Per maggiori informazioni sullo sniffing dei parametri e sull'elaborazione delle query, vedere Guida sull'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 sensibilità 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 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.
  • Utilizzare l'hint per la query OPTION (OPTIMIZE FOR UNKNOWN) per eseguire l'override del valore del parametro effettivo invece di utilizzare la media del vettore di densità. A questo scopo è anche possibile acquisire i valori dei parametri in entrata in variabili locali e quindi utilizzare le variabili locali all'interno dei predicati invece di usare i parametri stessi. Per questa correzione, 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 le ricompilazioni nella cache. Questa soluzione alternativa presuppone che il piano comune adeguato sia quello già 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 maggiori 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 viene 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';

In questo esempio, t1.c1 utilizza @p1, ma t2.c2 continua a utilizzare il 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, è necessario anche parametrizzare 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:

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 che è differente dal piano originale memorizzato nella cache. Un piano originale esistente potrebbe essere ricompilato automaticamente per diversi motivi:

  • La query fa riferimento alle modifiche nello schema
  • La query fa riferimento alle modifiche dei dati nelle tabelle
  • Le opzioni del contesto di query sono state modificate

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

  • Riavvio dell'istanza
  • Modifica della configurazione in ambito database
  • Utilizzo elevato della memoria
  • Richieste esplicite di 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, queste sono le probabili spiegazioni:

  • 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'utilizzo 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, come ad esempio il numero di processori disponibili, può influire sul piano generato. Ad esempio, se un sistema dispone di più processori, potrebbe essere scelto un piano parallelo. Per maggiori 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 sovrautilizzate. 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 di assorbire i requisiti della CPU. Per maggiori 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ò generare 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 è generato 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 generi 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 il problema di prestazioni sia correlato all'aumento del carico di lavoro.

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

  • Modifica nell'utilizzo della risorsa: 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 nel piano di esecuzione di una query e le modifiche alla distribuzione dei dati possono contribuire al maggior uso della risorsa, 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 rimane un segnale significativo, supponendo che gli altri fattori restino invariati.

Utilizzare Database Watcher per rilevare gli aumenti del carico di lavoro e pianificare le regressioni nel tempo.

Dopo aver eliminato un piano non ottimale e problemi correlati all'attesa legati ai problemi di esecuzione, il problema di prestazioni è generalmente dovuto alle query che probabilmente attendono 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 database watcher per individuare le query con riduzione del livello delle prestazioni dovuto a un aumento delle attese.
  • Usare Query Store per trovare le statistiche di attesa per ogni query nel tempo. In Query Store, i tempi 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.
  • Usare sys.dm_db_wait_stats per restituire informazioni su tutte le attese incontrate dai thread eseguiti durante l'operazione. È possibile usare questa visualizzazione 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 in coda o attese esterne.
  • Usare sys.dm_os_waiting_tasks per restituire informazioni relative alla coda di attività in attesa su alcune risorse.

Negli scenari con utilizzo elevato della CPU, Query Store e le 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 delle 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 vista a gestione dinamica sys.dm_exec_requests per tenere traccia delle query attualmente in esecuzione e della durata del ruolo di lavoro associato.

Suggerimento

Strumenti aggiuntivi:

Contenuto correlato