Condividi tramite

Diagnostica della risoluzione dei problemi relativi alle prestazioni di SQL Hyperscale

  • Articolo

Si applica a:Database SQL di Azure

Per risolvere i problemi di prestazioni in un database Hyperscale, metodologie generali di ottimizzazione delle prestazioni SQL è il punto di partenza di qualsiasi analisi delle prestazioni. Tuttavia, data l'architettura distribuita di Hyperscale, potrebbe essere necessario prendere in considerazione ulteriori dati diagnostici. Questo articolo descrive i dati di diagnostica specifici di Hyperscale.

Riduzione del ritardo nella registrazione dei log

Ogni database e pool elastico nel database SQL di Azure gestisce il tasso di generazione dei log tramite governance del tasso dei log. In Hyperscale il limite di governance della frequenza dei log è impostato su 105 MB/s, indipendentemente dalle dimensioni di calcolo. Questo valore viene esposto nella colonna primary_max_log_rate in sys.dm_user_db_resource_governance.

A volte, la frequenza di generazione dei log nella replica di calcolo primaria deve essere ridotta per mantenere i contratti di servizio di recuperabilità. Ciò può verificarsi, ad esempio, quando un server di pagine o un'altra replica di calcolo è notevolmente in ritardo nell'applicazione di nuovi record di log dal servizio di log. Se non sono presenti componenti Hyperscale, il meccanismo di governance della frequenza dei log consente la velocità di generazione dei log per raggiungere 100 MB/s. Si tratta della velocità effettiva di generazione massima dei log in tutte le dimensioni di calcolo Hyperscale.

Nota

La frequenza di generazione dei log di 150 MB/s è disponibile come funzionalità di anteprima del consenso esplicito. Per altre informazioni e per acconsentire esplicitamente a 150 MB/s, vedere Blog: Miglioramenti di Hyperscale di novembre 2024.

I tipi di attesa seguenti vengono visualizzati in sys.dm_os_wait_stats quando viene ridotta la frequenza di log:

Tipo di attesa Ragione
RBIO_RG_STORAGE Utilizzo ritardato del log da parte di un server di pagine
RBIO_RG_DESTAGE Consumo ritardato dei log da parte dell'archiviazione dei log a lungo termine
RBIO_RG_REPLICA Utilizzo ritardato del log da una replica secondaria a disponibilità elevata o da una replica denominata
RBIO_RG_GEOREPLICA Consumo ritardato del log da parte di una replica geografica secondaria
RBIO_RG_DESTAGE Utilizzo ritardato del log da parte del servizio di log
RBIO_RG_LOCALDESTAGE Consumo ritardato dei log da parte del servizio di log
RBIO_RG_STORAGE_CHECKPOINT Consumo ritardato dei log da parte di un server di pagine a causa di un checkpoint lento del database
RBIO_RG_MIGRATION_TARGET Utilizzo ritardato del log da parte del database non Hyperscale durante la migrazione inversa

Il sys.dm_hs_database_log_rate() funzione di gestione dinamica (DMF) fornisce dettagli aggiuntivi per comprendere la riduzione del tasso di log, se presente. Ad esempio, può indicare quale replica secondaria specifica è dietro l'applicazione di record di log e qual è la dimensione totale del log delle transazioni non ancora applicato.

Letture del server di pagine

Le repliche di calcolo non memorizzano nella cache una copia completa del database in locale. I dati locali della replica di calcolo vengono archiviati nel pool di buffer (in memoria) e nella cache RBPEX (Resilient Buffer Extension) locale che contiene un subset delle pagine di dati a cui si accede più di frequente. Questa cache SSD locale viene ridimensionata proporzionalmente alle dimensioni di calcolo. Ogni server di pagine, d'altra parte, dispone di una cache SSD completa per la parte del database gestita.

Quando un I/O di lettura viene emesso in una replica di calcolo, se i dati non esistono nel pool di buffer o nella cache SSD locale, la pagina nella richiesta numero di sequenza di log (LSN) viene recuperata dal server di pagine corrispondente. Le letture dai server di pagine sono remote e sono più lente rispetto alle letture dalla cache SSD locale. Durante la risoluzione dei problemi di prestazioni correlati all'I/O, è necessario essere in grado di indicare il numero di operazioni di I/O eseguite tramite le letture del server di pagine relativamente più lente.

Diverse viste a gestione dinamica (DMV) ed eventi estesi includono colonne e campi che specificano il numero di letture remote da un server di pagine, che possono essere confrontate con le letture totali. Query Store acquisisce anche le letture dalla memoria di pagina del server nelle statistiche di runtime delle query.

  • Le colonne per le letture del server di pagine del report sono disponibili nelle DMV di esecuzione e nelle viste del catalogo:
  • I campi di lettura del server di pagine sono presenti negli eventi estesi seguenti:
    • sql_statement_completed
    • sp_statement_completed
    • sql_batch_completed
    • rpc_completed
    • scan_stopped
    • query_store_begin_persist_runtime_stat
    • query_store_execution_runtime_info
  • Gli attributi ActualPageServerReads/ActualPageServerReadAheads sono presenti nel codice XML del piano della query per i piani che includono statistiche di runtime. Per esempio: 
    <RunTimeCountersPerThread Thread="8" ActualRows="90466461" [...] ActualPageServerReads="0" ActualPageServerReadAheads="5687297" ActualLobPageServerReads="0" ActualLobPageServerReadAheads="0" />

    Mancia

    Per visualizzare questi attributi nella finestra delle proprietà del piano di query, è necessario disporre di SSMS 18.3 o versione successiva.

Statistiche relative ai file virtuali e contabilità di I/O

Nel database SQL di Azure, il sys.dm_io_virtual_file_stats() DMF è un modo per monitorare le statistiche di I/O del database, come le operazioni di I/O al secondo (IOPS), il traffico e la latenza. Le caratteristiche di I/O in Hyperscale sono diverse a causa della relativa architettura distribuita . In questa sezione ci concentriamo su operazioni di lettura e scrittura I/O, come visto in questo DMF. In Hyperscale ogni file di dati visibile in questo DMF corrisponde a un server di pagine. DMF fornisce anche statistiche di I/O per la cache SSD locale nella replica di calcolo e per il log delle transazioni.

Utilizzo della cache SSD locale

Poiché la cache SSD locale esiste nella stessa replica di calcolo in cui il motore di database sta elaborando le query, le operazioni di I/O su questa cache sono più veloci rispetto alle operazioni di I/O rispetto ai server di pagine. In un database Hyperscale o in un pool elastico, sys.dm_io_virtual_file_stats() dispone di una riga speciale che riporta le statistiche di I/O per la cache SSD locale. Questa riga ha il valore di 0 per le colonne database_id e file_id. Ad esempio, la query seguente restituisce le statistiche di I/O della cache SSD locale dall'avvio del database.

SELECT *
FROM sys.dm_io_virtual_file_stats(0, NULL);

Il rapporto tra le letture dalla cache SSD locale e le letture complessive da tutti gli altri file di dati è il rapporto di riscontri nella cache SSD locale. Questa metrica viene fornita dai contatori delle prestazioni RBPEX cache hit ratio e RBPEX cache hit ratio base disponibili nella DMV sys.dm_os_performance_counters.

Letture di dati

  • Quando le letture vengono rilasciate dal motore di database in una replica di calcolo, potrebbero essere gestite dalla cache SSD locale o dai server di pagine o da una combinazione di due se si leggono più pagine.
  • Quando la replica di calcolo legge alcune pagine da un file di dati specifico, ad esempio il file con file_id 1, se questi dati si trovano esclusivamente nella cache SSD locale, tutte le operazioni di I/O per questa lettura vengono considerate in base a file_id 0. Se una parte di essi si trova nella cache SSD locale e un'altra parte si trova nei server di pagine, l'I/O della parte servita dalla cache SSD locale è assegnato a file_id 0, mentre la parte servita dai server di pagine è attribuita ai loro file corrispondenti.
  • Quando una replica di calcolo richiede una pagina in un determinato LSN da un server di pagine, se il server di pagine non ha ancora raggiunto l'LSN richiesto, la lettura nella replica di calcolo attende fino a quando il server di pagine non viene aggiornato prima della restituzione della pagina. Per qualsiasi lettura da un server di pagine nella replica di calcolo, viene visualizzato un tipo di attesa PAGEIOLATCH_* se è in attesa di tale I/O. In Hyperscale, questo tempo di attesa include sia il tempo necessario per recuperare la pagina richiesta nel server di pagine al numero LSN richiesto, sia il tempo necessario per trasferire la pagina dal server di pagine alla replica di calcolo.
  • Le letture di grandi dimensioni, ad esempio i read-ahead, vengono spesso eseguite usando letture a dispersione. In questo modo è possibile leggere fino a 4 MB come singola I/O di lettura. Tuttavia, quando i dati letti si trovano nella cache SSD locale, queste letture vengono considerate come più letture da 8 KB singole, poiché il pool di buffer e la cache SSD locale usano sempre pagine da 8 KB. Di conseguenza, il numero di operazioni di I/O di lettura osservate nella cache SSD locale potrebbe essere maggiore del numero effettivo di operazioni di I/O eseguite dal motore.

Scritture di dati

  • La replica di calcolo primaria non scrive direttamente nei server di pagine. Invece, i record di log del servizio di log vengono riprodotti sui server delle pagine corrispondenti.
  • Le scritture nella replica di calcolo sono prevalentemente scritture nella cache SSD locale (file_id 0). Per le scritture di dimensioni superiori a 8 KB, in altre parole quelle eseguite usando gather-write , ogni operazione di scrittura viene convertita in più scritture singole da 8 KB nella cache SSD locale poiché il pool di buffer e la cache SSD locale usano sempre pagine da 8 KB. Di conseguenza, il numero di operazioni di I/O di scrittura visualizzate nella cache SSD locale potrebbe essere maggiore del numero effettivo di operazioni di I/O eseguite dal motore.
  • Anche i file di dati diversi da file_id 0, relativi ai server di pagine, potrebbero mostrare delle operazioni di scrittura. In Hyperscale queste scritture vengono simulate, perché le repliche di elaborazione non scrivono mai direttamente nei server delle pagine. Le statistiche di I/O vengono considerate man mano che si verificano nella replica di calcolo. Le operazioni di I/O al secondo, la velocità effettiva e la latenza rilevate in una replica di calcolo per i file di dati diversi da file_id 0 non riflettono le statistiche di I/O effettive delle scritture eseguite nei server di pagine.

Scritture di log

  • Nella replica di calcolo primaria, viene registrata una scrittura di log in sys.dm_io_virtual_file_stats() in file_id 2.
  • A differenza dei gruppi di disponibilità AlwaysOn, quando una transazione esegue il commit sulla replica primaria di calcolo, i record di log non vengono consolidati nella replica secondaria. In Hyperscale il log viene sottoposto a protezione avanzata nel servizio di log e applicato alle repliche secondarie in modo asincrono. Poiché le scritture di log non vengono effettivamente eseguite nelle repliche secondarie, qualsiasi contabilità di I/O dei log in sys.dm_io_virtual_file_stats() nelle repliche secondarie non deve essere usata come statistiche di I/O del log delle transazioni.

I/O dei dati nelle statistiche di utilizzo delle risorse

In un database diverso da Hyperscale, le operazioni di I/O al secondo di lettura e scrittura combinate sui file di dati, relative al limite di operazioni di I/O al secondo di governance delle risorse, vengono segnalate nelle viste sys.dm_db_resource_stats e sys.resource_stats, nella colonna avg_data_io_percent. Le DMV corrispondenti per i pool elastici sono sys.dm_elastic_pool_resource_stats e sys.elastic_pool_resource_stats. Gli stessi valori vengono segnalati come percentuale di I/O di dati metriche di Monitoraggio di Azure per database e pool elastici.

In un database Hyperscale, queste colonne e metriche riportano l'utilizzo dell'IOPS dei dati rispetto al limite per lo storage SSD locale solo sulla replica di calcolo, che include le operazioni di I/O contro la cache SSD locale e nel database tempdb. Un valore del 100% in questa colonna indica che la governance delle risorse sta limitando le operazioni di I/O al secondo dell'archiviazione locale. Se questo problema è correlato a un problema di prestazioni, ottimizzare il carico di lavoro per generare meno operazioni di I/O o aumentare le dimensioni di calcolo per migliorare la gestione delle risorse e raggiungere il limite Max Data IOPS . Per la governance delle risorse delle operazioni di lettura e scrittura della cache SSD locale, il sistema conta singole operazioni di I/O da 8 KB, anziché operazioni di I/O di dimensioni maggiori che possono essere emesse dal motore di database.

I dati di I/O sui server di pagine non vengono segnalati nelle visualizzazioni di utilizzo delle risorse o tramite le metriche di Monitoraggio di Azure, ma vengono segnalate in sys.dm_io_virtual_file_stats() come descritto in precedenza.

Contenuto correlato