Condividi tramite

Diagnostica della risoluzione dei problemi relativi alle prestazioni di SQL Hyperscale

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. Il limite di velocità del log viene esposto nella primary_max_log_rate colonna 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 ci sono componenti Hyperscale attivi, il meccanismo di governance della frequenza dei log consente alla velocità di generazione dei log di raggiungere i 150 MiB/s per database per hardware della serie Premium e hardware ottimizzato per la memoria della serie Premium. Per l'hardware della serie standard, la velocità massima del log è 100 MiB/s per ogni database. Per i pool elastici, la velocità massima di log è di 150 MiB/s per pool per hardware ottimizzato per la memoria premium e di serie Premium e 125 MiB/s per pool per altri hardware.

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 Utilizzo ritardato del 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

La funzione DMF (Dynamic Management Function ) sys.dm_hs_database_log_rate() fornisce altri dettagli per comprendere la riduzione della frequenza 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 delle pagine del server di 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.

Per Hyperscale, i dati rilevanti in sys.dm_io_virtual_file_stats() sono i seguenti:

  • Le righe in cui il database_id valore corrisponde al valore restituito dalla funzione DB_ID e dove il file_id valore è diverso da 2, corrispondono ai server di pagine. In genere, ogni riga corrisponde a un server di pagine. Tuttavia, per i file di dimensioni maggiori, vengono usati più server di pagine.

    • La riga con file_id 2 corrisponde al log delle transazioni.

  • Le righe in cui il valore nella database_id colonna è 0 corrispondono alla cache SSD locale nella replica di calcolo.

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 un pool elastico, sys.dm_io_virtual_file_stats() ha righe speciali che segnalano statistiche di I/O per la cache SSD locale. Queste righe hanno il valore di 0 per la database_id colonna. 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);

Un rapporto tra le letture aggregate dai file della cache SSD locale alle letture aggregate 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, tutti gli I/O per questa lettura vengono conteggiati nei file della cache SSD locale dove database_id è 0. Se alcune parti di questi dati si trovano nella cache SSD locale e alcune parti si trovano nei server di pagine, l'I/O viene conteggiato parzialmente verso i file della cache SSD locale e parzialmente verso i file di dati corrispondenti ai server di pagine.

  • 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 che venga restituita la pagina. Per qualsiasi lettura da un server di pagine nella replica di calcolo, viene visualizzato un PAGEIOLATCH_* tipo di attesa 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 (database_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 database_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. IOPS di scrittura, throughput e latenza visualizzati su una replica di calcolo per file di dati diversi da database_id 0 non riflettono le effettive statistiche di I/O delle scritture che avvengono sui page server.

Scritture di log

  • Nella replica di calcolo primaria, le scritture dei log vengono registrate in sys.dm_io_virtual_file_stats() sotto file_id 2.

  • A differenza dei gruppi di disponibilità, quando una transazione esegue il commit nella replica di elaborazione primaria, i record di log non vengono rafforzati 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 non-Hyperscale, le operazioni combinate di lettura e scrittura IOPS rispetto al limite di I/O dei dati 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 le metriche segnalano l'utilizzo di I/O dei dati rispetto al limite per l'archiviazione SSD locale solo nella replica di calcolo, che include I/O rispetto alla cache SSD locale e al tempdb database. 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 a 8 KB, anziché operazioni di I/O di dimensioni maggiori che potrebbero essere rilasciate dal motore di database.

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

Contenuto correlato

  • Last updated on