Monitorare e risolvere i problemi di prestazioni

Completato

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 includono 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. È possibile visualizzare i dati di Monitoraggio di Azure nella portale di Azure e le applicazioni possono accedere a questi dati tramite Hub eventi di Azure o API. Analogamente a Windows Monitor prestazioni, Monitoraggio di Azure consente di accedere alle metriche di utilizzo delle risorse per Azure SQL senza usare gli strumenti di SQL Server.

Viste a gestione dinamica (DMV)

Azure SQL offre 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. Altre informazioni sulle DMV con Azure SQL verranno fornite più avanti in questa unità.

Eventi estesi

Azure SQL offre 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. Altre informazioni sugli eventi estesi con Azure SQL verranno fornite più avanti in questa unità.

Profilatura di query leggera

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

È possibile usare la profilatura di query leggera per 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 semplificata tramite DMV ed è attivata per impostazione predefinita per Azure SQL perché è 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 Panoramica prestazioni e Informazioni dettagliate prestazioni query nella portale di Azure.

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 come sys.dm_exec_requests e sys.dm_os_wait_stats vengono comunemente usate per esaminare le prestazioni delle query.

La sys.server_resource_stats visualizzazione di sistema è specifica per Istanza gestita di SQL di Azure e mostra l'utilizzo cronologico delle risorse. Si tratta di uno strumento utile per visualizzare l'utilizzo delle risorse, perché non si ha accesso diretto agli strumenti del sistema operativo come Monitor prestazioni.

Database SQL di Microsoft Azure

La maggior parte delle DMV comuni necessarie per le prestazioni, tra cui sys.dm_exec_requests e sys.dm_os_wait_stats, sono disponibili. Notare che queste DMV forniscono solo informazioni specifiche del database e non per tutti i database per un server logico.

La sys.dm_db_resource_stats DMV è specifica per database SQL di Azure ed è possibile usarla per visualizzare una cronologia dell'utilizzo delle risorse per il database. Usare questa DMV in modo analogo a come si usa sys.server_resource_stats per un'istanza gestita.

La sys.elastic_pool_resource_stats DMV è simile a sys.dm_db_resource_stats, ma è possibile usarla per visualizzare l'utilizzo delle risorse 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 questa DMV per visualizzare le informazioni sul contatore delle prestazioni di SQL Server in genere 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 e pertanto sono quasi uguali per Azure SQL, con alcune differenze significative. Queste differenze sono illustrate nelle sezioni successive.

Eventi estesi per il database SQL di Azure

È possibile usare eventi estesi per database SQL di Azure, proprio come SQL Server, creando sessioni e usando 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 sys.fn_xe_file_target_read_filedi sistema .

Nota

Non è possibile usare SSMS per visualizzare i dati attivi per database SQL di Azure.

È 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 sys.fn_xe_file_target_read_filedi sistema . La possibilità di SSMS di visualizzare i dati in tempo reale è supportata per SQL Server e 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 in Windows o superiore in 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 Istanza gestita di SQL di Azure, questa DMV restituisce informazioni simili a sys.dm_user_db_resource_governance, ma per il 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 stato RUNNABLE e un tipo di attesa di SOS_SCHEDULER_YIELD per verificare se la capacità della 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 l'analisi sugli scenari di attesa, in genere si esamineranno 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

  Usare questa DMV per trovare tipi di attesa specifici per le query attive per visualizzare la risorsa in attesa. 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 trovare 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 aggregazioni 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.

Suggerimento

È possibile usare gli eventi estesi per qualsiasi scenario di esecuzione o attesa. A tale scopo, è necessario configurare una sessione di eventi estesi per analizzare le query. Questo metodo per eseguire il debug di un problema di prestazioni è più avanzato e può restituire molte informazioni in cambio di un sovraccarico delle prestazioni maggiore rispetto alle DMV.

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: attende database SQL di Azure
• POOL_LOG_RATE_GOVERNOR: attende i pool elastici
• INSTANCE_LOG_GOVERNOR: attende Istanza gestita di SQL di Azure
• HADR_THROTTLE_LOG_RATE*: attende la 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 con un numero elevato di utenti simultanei potrebbero affrontare i limiti del ruolo di lavoro applicati per database SQL di Azure e 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.
• Al momento corrente, Istanza gestita di SQL usa max worker threads, quindi i ruoli di lavoro oltre questo limite potrebbero vedere THREADPOOL attese.

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 successivo si apprenderà come monitorare e risolvere un problema di prestazioni per Azure SQL usando gli strumenti e le conoscenze acquisite in questa unità.