Monitorare e risolvere i problemi di prestazioni

• 10 minuti

Il monitoraggio e la risoluzione dei problemi sono fondamentali per garantire prestazioni coerenti. Azure SQL offre gli stessi strumenti e funzionalità di SQL Server per monitorare le prestazioni e risolvere i relativi problemi, oltre a funzionalità aggiuntive. Sono incluse funzionalità come le DMV, eventi estesi e Monitoraggio di Azure. È anche importante apprendere come usare questi strumenti e funzionalità in diversi scenari correlati alle prestazioni per Azure SQL. Questi scenari comportano un utilizzo elevato della CPU o l'attesa di una risorsa.

Strumenti e funzionalità per il monitoraggio delle prestazioni

Azure SQL fornisce funzionalità di monitoraggio e risoluzione dei problemi nell'ecosistema di Azure, nonché strumenti familiari disponibili con SQL Server. Le sezioni seguenti descrivono brevemente queste soluzioni.

Monitoraggio di Azure

Monitoraggio di Azure fa parte dell'ecosistema di Azure e Azure SQL è integrato per supportare metriche, avvisi e log di Azure. I dati di Monitoraggio di Azure possono essere visualizzati nel portale di Azure e le applicazioni possono accedere a tali dati tramite gli Hub eventi di Azure o le API. In modo molto simile a Monitor prestazioni di Windows, Monitoraggio di Azure consente di accedere alle metriche di utilizzo delle risorse per Azure SQL senza servirsi degli strumenti di SQL Server.

Viste a gestione dinamica (DMV)

Azure SQL fornisce quasi la stessa infrastruttura DMV di SQL Server, con alcune differenze. Le DMV sono fondamentali per il monitoraggio delle prestazioni, dal momento che è possibile visualizzare i dati sulle prestazioni chiave di SQL Server usando query T-SQL standard. È ad esempio possibile visualizzare informazioni come le query attive, l'utilizzo delle risorse, i piani di query e i tipi di attesa delle risorse. Più avanti nella presente unità, sono disponibili altre informazioni sulle DMV con Azure SQL.

Eventi estesi

Azure SQL fornisce quasi la stessa infrastruttura di eventi estesi di SQL Server, con alcune differenze. Gli eventi estesi consentono di analizzare gli eventi chiave dell'esecuzione all'interno SQL Server, su cui Azure SQL si basa. Per le prestazioni, gli eventi estesi consentono di tracciare l'esecuzione di singole query. Più avanti nella presente unità, sarà possibile apprendere altri dettagli sugli eventi estesi con Azure SQL.

Profilatura di query leggera

La profilatura leggera è un metodo avanzato per risolvere i problemi relativi agli scenari che richiedono di accedere al piano di esecuzione effettivo per le richieste in anteprima e le query di alto valore. A causa del basso sovraccarico, qualsiasi server che non sia già associato alla CPU può eseguire continuamente la profilatura leggera e consentire ai professionisti del database di accedere in qualsiasi momento a una esecuzione in corso; ad esempio, tramite Monitoraggio attività in SQL Server Management Studio (SSMS) o eseguendo direttamente query su sys.dm_exec_query_profiles o sys.dm_exec_query_statistics_xml.

La profilatura di query leggera permette di esaminare il piano di query e lo stato di esecuzione di una query attiva. Si tratta di una funzionalità chiave per eseguire il debug delle prestazioni delle query per le istruzioni durante l'esecuzione. Questa funzionalità consente di ridurre i tempi di risoluzione dei problemi di prestazioni rispetto all'uso di strumenti come gli eventi estesi per analizzare le prestazioni di query. È possibile accedere alla profilatura di query leggera tramite le DMV; questa funzionalità è attiva per impostazione predefinita per Azure SQL, ma anche per SQL Server 2019 e versioni successive.

Funzionalità di debug dei piani di query

In alcune situazioni, possono essere necessari dettagli aggiuntivi sulle prestazioni di query per una singola istruzione T-SQL. Le istruzioni T-SQL SET, come SHOWPLAN e STATISTICS, possono fornire questi dettagli e sono completamente supportate per Azure SQL come per SQL Server.

Archivio query

Query Store è un record cronologico di esecuzione delle prestazioni per le query archiviate nel database utente. Query Store è una funzionalità attiva per impostazione predefinita per Azure SQL e consente operazioni come la correzione automatica dei piani e l'ottimizzazione automatica. I report di SQL Server Management Studio (SSMS) per Query Store sono disponibili per Azure SQL. Questi report consentono di trovare le query che consumano più risorse, tra cui le differenze tra i piani di query e i tipi di attesa principali per esaminare gli scenari di attesa delle risorse.

Visualizzazioni delle prestazioni

Per il database SQL di Azure, è possibile vedere le informazioni sulle prestazioni di Query Store integrate nel portale di Azure tramite le visualizzazioni. In questo modo è possibile visualizzare alcune delle stesse informazioni per Query Store, come si farebbe con uno strumento client come SSMS. Usare le opzioni del portale di Azure denominate Informazioni generali sulle prestazioni e Informazioni dettagliate prestazioni query.

Dettagli delle DMV

Le DMV sono state per molti anni un elemento fondamentale per monitorare le prestazioni e risolvere i relativi problemi con SQL Server. Le DMV comuni per SQL Server sono disponibili con Azure SQL, oltre ad alcune altre specifiche per Azure.

Istanza gestita di SQL di Azure

Tutte le DMV per SQL Server sono disponibili per Istanza gestita di SQL. Le DMV chiave, quali sys.dm_exec_requests e sys.dm_os_wait_stats, vengono comunemente usate per esaminare le prestazioni delle query.

La visualizzazione di sistema sys.server_resource_stats è specifica dell'Istanza gestita di SQL di Azure e mostra l'uso cronologico della risorsa. Si tratta di un strumento importante per visualizzare l'uso della risorsa, poiché non è possibile accedere direttamente a strumenti del sistema operativo come Monitor prestazioni.

Database SQL di Azure

Sono disponibili le principali DMV comuni necessarie per le prestazioni, incluse sys.dm_exec_requests e sys.dm_os_wait_stats. Notare che queste DMV forniscono solo informazioni specifiche del database e non per tutti i database per un server logico.

La DMV sys.dm_db_resource_stats è specifica del database SQL di Azure ed è possibile utilizzarla per visualizzare la cronologia dell'uso della risorsa per il database. Usare questa DMV in modo analogo a come si usa sys.server_resource_stats per un'istanza gestita.

La DMV sys.elastic_pool_resource_stats è simile a sys.dm_db_resource_stats, tuttavia è possibile utilizzarla per visualizzare l'uso della risorsa per i database del pool elastico.

DMV necessarie

Per risolvere determinati scenari di prestazioni per Azure SQL, sono necessarie le DMV seguenti:

• sys.dm_io_virtual_file_stats è importante perché non si ha accesso diretto alle metriche del sistema operativo per le prestazioni di I/O per ogni file.
• sys.dm_os_performance_counters è disponibile sia per il database SQL di Azure che per Istanza gestita di SQL, per esaminare metriche delle prestazioni comuni di SQL Server. Usare queste DMV per visualizzare le informazioni dei contatori delle prestazioni di SQL Server, generalmente disponibili in Monitor prestazioni.
• sys.dm_instance_resource_governance consente di visualizzare i limiti delle risorse per un'istanza gestita. È possibile visualizzare queste informazioni per visualizzare i limiti previsti per le risorse senza usare il portale di Azure.
• sys.dm_user_db_resource_governance consente di visualizzare i limiti delle risorse comuni in base all'opzione di distribuzione, al livello di servizio e alle dimensioni per la distribuzione del database SQL di Azure. È possibile visualizzare queste informazioni per visualizzare i limiti previsti per le risorse senza usare il portale di Azure.

DMV per informazioni più approfondite

Queste DMV forniscono informazioni più approfondite sui limiti delle risorse e sulla governance delle risorse per Azure SQL. Non sono destinate a essere usate per scenari comuni, ma possono essere utili quando si esaminano in modo approfondito i problemi di prestazioni complessi. Per tutti i dettagli di queste DMV, vedere la documentazione:

• sys.dm_user_db_resource_governance_internal (solo Istanza gestita di SQL)
• sys.dm_resource_governor_resource_pools_history_ex
• sys.dm_resource_governor_workload_groups_history_ex

Dettagli degli eventi estesi

La funzionalità di eventi estesi è il meccanismo di analisi per SQL Server. Gli eventi estesi per Azure SQL si basano sul motore di SQL Server, quindi sono praticamente gli stessi di Azure SQL, con alcune differenze importanti. Queste differenze sono illustrate nelle sezioni successive.

Eventi estesi per il database SQL di Azure

È possibile usare gli eventi estesi per il database SQL di Azure, proprio come SQL Server, creando sessioni e utilizzando eventi, azioni e destinazioni. Quando si creano sessioni di eventi estesi, tenere presente questi aspetti importanti:

• Sono supportati gli eventi e le azioni usati più comunemente.
• Sono supportate le destinazioni di tipo file, ring_buffer e contatore.
• Le destinazioni di tipo file sono supportate con Archiviazione BLOB di Azure, perché non si ha accesso ai dischi del sistema operativo sottostanti.

È possibile usare SSMS o T-SQL per creare e avviare sessioni. È possibile usare SSMS per visualizzare i dati di destinazione della sessione eventi estesi o la funzione di sistema sys.fn_xe_file_target_read_file.

È importante sapere che gli eventi estesi generati per le sessioni sono specifici del database e non si applicano a tutto il server logico.

Eventi estesi per Istanza gestita di SQL di Azure

È possibile usare gli eventi estesi per Istanza gestita di SQL proprio come con SQL Server, creando sessioni e usando eventi, azioni e destinazioni. Quando si creano sessioni di eventi estesi, tenere presente questi aspetti importanti:

• Sono supportati tutti gli eventi, le destinazioni e le azioni.
• Le destinazioni di tipo file sono supportate con Archiviazione BLOB di Azure, perché non si ha accesso ai dischi del sistema operativo sottostanti.
• In Istanza gestita di SQL sono stati aggiunti alcuni eventi per analizzare eventi specifici della gestione e dell'esecuzione dell'istanza.

È possibile usare SSMS o T-SQL per creare e avviare sessioni. È possibile usare SSMS per visualizzare i dati di destinazione della sessione eventi estesi o la funzione di sistema sys.fn_xe_file_target_read_file. La possibilità di visualizzare i dati in tempo reale con SSMS è supportata per SQL Server e per l'Istanza gestita di SQL di Azure.

Scenari di prestazioni per Azure SQL

Per decidere come applicare le funzionalità e gli strumenti di monitoraggio delle prestazioni e di risoluzione dei relativi problemi, è importante esaminare le prestazioni per Azure SQL tramite scenari.

Scenari di prestazioni comuni

Una tecnica comune per la risoluzione dei problemi delle prestazioni di SQL Server consiste nell'esaminare se un problema di prestazioni è In esecuzione (CPU elevata) o In attesa (in attesa di una risorsa). Il diagramma seguente mostra un albero delle decisioni per determinare se un problema di prestazioni di SQL Server riguarda l'esecuzione o l'attesa e come usare gli strumenti per le prestazioni per determinare la causa e la soluzione.

Approfondiremo i dettagli di ogni aspetto del diagramma.

Esecuzione e attesa

Esaminare prima di tutto l'utilizzo complessivo delle risorse. Per una distribuzione standard di SQL Server è possibile usare strumenti come Monitor prestazioni su Windows o il comando top su Linux. È possibile usare i metodi seguenti per Azure SQL:

• Portale di Azure/PowerShell/avvisi

  Monitoraggio di Azure offre metriche integrate per visualizzare l'utilizzo delle risorse per Azure SQL. È anche possibile configurare avvisi per esaminare le condizioni di utilizzo delle risorse.

• sys.dm_db_resource_stats

  Per il database SQL di Azure, è possibile esaminare questa DMV per visualizzare l'utilizzo di CPU, memoria e risorse di I/O per la distribuzione del database. Questa DMV acquisisce uno snapshot dei dati ogni 15 secondi.

• sys.server_resource_stats

  Questa DMV si comporta come sys.dm_db_resource_stats, ma viene usata per visualizzare l'utilizzo delle risorse per CPU, memoria e I/O per Istanza gestita di SQL. Anche questa DMV acquisisce uno snapshot ogni 15 secondi.

• sys.dm_user_db_resource_governance

  Per database SQL di Azure, questa DMV restituisce le impostazioni effettive di configurazione e capacità usate dai meccanismi di governance delle risorse nel database corrente o nel pool elastico.

• sys.dm_instance_resource_governance

  Per l'Istanza gestita di SQL di Azure, questa DMV restituisce informazioni simili a sys.dm_user_db_resource_governance, ma per l'Istanza gestita di SQL corrente.

In esecuzione

Se è stato determinato che il problema è un utilizzo elevato della CPU, questo viene definito scenario in esecuzione. Uno scenario in esecuzione può includere query che usano risorse attraversi la compilazione o l'esecuzione. Per ulteriori analisi, usare gli strumenti seguenti:

• Archivio query

  Usare i report sulle prime risorse per consumo in SSMS, le viste del catalogo di Query Store o le informazioni dettagliate sulle prestazioni delle query nel portale di Azure (solo database SQL di Azure) per individuare le query che usano la maggior parte delle risorse della CPU.

• sys.dm_exec_requests

  Usare questa DMV in Azure SQL per ottenere uno snapshot dello stato delle query attive. Cercare le query con lo stato RUNNABLE e un tipo di attesa SOS_SCHEDULER_YIELD per verificare se si dispone di una capacità CPU sufficiente.

• sys.dm_exec_query_stats

  Questa DMV può essere usata in modo molto simile a Query Store per trovare le query che consumano più risorse. Tenere presente che è disponibile solo per i piani di query memorizzati nella cache, mentre Query Store fornisce un record cronologico persistente delle prestazioni. Questa DMV consente anche di trovare il piano di query per una query memorizzata nella cache.

• sys.dm_exec_procedure_stats

  Questa DMV fornisce informazioni in modo molto simile a sys.dm_exec_query_stats, ad eccezione del fatto che le informazioni sulle prestazioni possono essere visualizzate a livello di stored procedure.

  Dopo aver determinato la query o le query che utilizzano la maggior parte delle risorse, può essere necessario esaminare se si dispone di risorse della CPU sufficienti per il carico di lavoro. È possibile eseguire il debug dei piani di query con strumenti come la profilatura di query leggera, le istruzioni SET, Query Store o l'analisi degli eventi estesi.

In attesa

Se il problema non sembra essere un utilizzo elevato di risorse della CPU, il problema di prestazioni può dipendere dall'attesa in una risorsa. Gli scenari che implicano l'attesa delle risorse includono:

• Attese di I/O
• Attese di blocco
• Attese latch
• Limiti del pool di buffer
• Concessioni di memoria
• Rimozione della cache dei piani

Per eseguire analisi sugli scenari di attesa, in genere si esaminano gli strumenti seguenti:

• sys.dm_os_wait_stats

  Usare questa DMV per vedere quali sono i principali tipi di attesa per il database o l'istanza. In questo modo è possibile stabilire l'azione da eseguire in seguito, a seconda dei principali tipi di attesa.

• sys.dm_exec_requests

  Utilizzare questa DMV per trovare tipi di attesa specifici per le query attive, al fine di visualizzare la risorsa che stanno aspettando. Può trattarsi di uno scenario di blocco standard, in attesa di blocchi di altri utenti.

• sys.dm_os_waiting_tasks

  È possibile usare questa DMV per individuare i tipi di attesa per una determinata attività per una query specifica attualmente in esecuzione, ad esempio, per vedere perché richiede più tempo del normale. sys.dm_os_waiting_tasks contiene le statistiche di attesa in tempo reale che sys.dm_os_wait_stats aggrega nel tempo.

• Archivio query

  Query Store fornisce report e viste del catalogo che mostrano un'aggregazione delle attese principali per l'esecuzione del piano di query. È importante sapere che un'attesa di CPU è equivalente a un problema di esecuzione.

Scenari specifici di Azure SQL

Ci sono alcuni scenari di prestazioni, sia di esecuzione che di attesa, specifici di Azure SQL. Tra questi, la governance dei log, i limiti del ruolo di lavoro, le attese rilevate per i livelli di servizio business critical e le attese specifiche di una distribuzione di livello Hyperscale.

Governance dei log

Azure SQL può usare la governance della frequenza di log per applicare limiti di risorse per l'utilizzo del log delle transazioni. Questa applicazione può essere necessaria per garantire i limiti di risorse e per soddisfare il contratto di servizio promesso. La governance dei log può essere osservata dai tipi di attesa seguenti:

• LOG_RATE_GOVERNOR: è in attesa del database SQL di Azure
• POOL_LOG_RATE_GOVERNOR: è in attesa dei pool elastici
• INSTANCE_LOG_GOVERNOR: è in attesa dell'Istanza gestita di SQL di Azure
• HADR_THROTTLE_LOG_RATE*: è in attesa della latenza di replica geografica e business critical

Limiti del ruolo di lavoro

SQL Server usa un pool di thread del ruolo di lavoro, ma dispone di limiti del numero massimo di ruoli di lavoro. Le applicazioni usate da un numero elevato di utenti simultanei potrebbero avvicinarsi ai limiti dei ruoli di lavoro applicati al database SQL di Azure e all'Istanza gestita di SQL:

• Il database SQL di Azure ha limiti basati sul livello di servizio e le dimensioni. Se si supera questo limite, per una nuova query si verifica un errore.
• Attualmente, Istanza gestita di SQL utilizza max worker threads, quindi i ruoli di lavoro oltre questo limite potrebbero riscontrare attese di tipo THREADPOOL.

Attese HADR business critical

Se si usa un livello di servizio business critical, si possono osservare in modo imprevisto i tipi di attesa seguenti:

• HADR_SYNC_COMMIT
• HADR_DATABASE_FLOW_CONTROL
• HADR_THROTTLE_LOG_RATE_SEND_RECV

Anche se queste attese possono non rallentare l'applicazione, è possibile che non ci si aspetti di riscontrarle. In genere sono specifiche dell'uso di un gruppo di disponibilità Always On. I livelli business critical usano la tecnologia dei gruppi di disponibilità per implementare il contratto di servizio e le funzionalità di disponibilità di un livello di servizio business critical, quindi questi tipi di attesa sono previsti. Si noti che tempi di attesa lunghi possono indicare un collo di bottiglia, ad esempio latenza di I/O o una replica in corso.

Hyperscale

L'architettura con iperscalabilità può comportare alcuni tipi di attesa univoci con prefisso RBIO (una possibile indicazione della governance dei log). DMV, viste del catalogo ed eventi estesi sono inoltre stati migliorati per mostrare le metriche per le letture dei server di pagine.

Nell'esercizio seguente sarà possibile apprendere come monitorare e risolvere un problema di prestazioni per Azure SQL utilizzando gli strumenti e le conoscenze acquisite nel corso di questa unità.