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, le metodologie generali di ottimizzazione delle prestazioni nel nodo di calcolo del database SQL di Azure rappresentano il punto di partenza di un'analisi delle prestazioni. Tuttavia, data l'architettura distribuita di Hyperscale, è stata aggiunta un'ulteriore diagnostica per facilitare l'assistenza. Questo articolo descrive i dati di diagnostica specifici di Hyperscale.

Attese di limitazione della frequenza dei log

Ogni obiettivo di servizio del database SQL di Azure ha limiti di frequenza di generazione dei log applicati tramite la governance della frequenza dei log. In Hyperscale, il limite di governance dei log è impostato su 105 MB/sec, indipendentemente dal livello di servizio. Questo valore viene esposto nella colonna primary_max_log_rate in sys.dm_user_db_resource_governance.

Tuttavia, ci sono momenti in cui è necessario limitare la frequenza di generazione dei log nella replica di calcolo primaria per mantenere i contratti di servizio di recuperabilità. Questa limitazione si verifica quando un server di pagine o un'altra replica di calcolo resta notevolmente indietro rispetto all'applicazione di nuovi record di log dal servizio di log. Se i server di pagine o le repliche non restano indietro, il meccanismo di limitazione consente la frequenza di generazione dei log per raggiungere 100 MB/s. Si tratta della frequenza effettiva massima di generazione dei log in tutti gli obiettivi del servizio Hyperscale.

I tipi di attesa seguenti (in sys.dm_os_wait_stats) descrivono i motivi per cui la frequenza dei log può essere limitata nella replica di calcolo primaria:

wait_type Descrizione
RBIO_RG_STORAGE Si verifica quando viene limitata la frequenza di generazione del log dei nodi di calcolo primari del database Hyperscale a causa di un consumo ritardato dei log in uno o più server di pagine.
RBIO_RG_DESTAGE Si verifica quando viene limitata la frequenza di generazione del log dei nodi di calcolo del database Hyperscale a causa di un consumo ritardato dei log da parte dell'archiviazione di log a lungo termine.
RBIO_RG_REPLICA Si verifica quando viene limitata la frequenza di generazione del log dei nodi di calcolo del database Hyperscale a causa di un consumo ritardato dei log da parte di una o più repliche secondarie leggibili.
RBIO_RG_GEOREPLICA Si verifica quando viene limitata la frequenza di generazione del log dei nodi di calcolo del database Hyperscale a causa di un consumo ritardato dei log da parte della replica geografica secondaria.
RBIO_RG_LOCALDESTAGE Si verifica quando viene limitata la frequenza di generazione del log dei nodi di calcolo del database Hyperscale a causa di un consumo ritardato dei log da parte del servizio di log.

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 dell'estensione del pool di buffer resilienti locale (RBPEX) che rappresenta una cache parziale (non di copertura) delle pagine di dati. Questa cache RBPEX locale viene dimensionata proporzionalmente alle dimensioni di calcolo ed è il triplo della memoria del livello di calcolo. RBPEX è simile al pool di buffer in quanto dispone dei dati a cui si accede più di frequente. D'altra parte, ogni server di pagine dispone di una cache RBPEX per la parte del database che gestisce.

Quando viene eseguita una lettura in una replica di calcolo, se i dati non esistono nel pool di buffer o nella cache RBPEX locale, viene effettuata una chiamata di funzione getPage(pageId, LSN) e la pagina viene recuperata dal server di pagine corrispondente. Le letture dai server di pagine sono remote e, pertanto, sono più lente rispetto a quelle da RBPEX locale. Durante la risoluzione dei problemi di prestazioni relativi alle operazioni di I/O, è necessario essere in grado di indicare il numero di operazioni di I/O eseguite tramite letture del server di pagine remote 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 letture remote come parte delle statistiche sui tempi di esecuzione delle query.

  • Le colonne relative alle letture del server di pagine di report sono disponibili nelle DMV di esecuzione e nelle viste del catalogo, come:

  • Le letture del server di pagine vengono aggiunte agli 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

  • ActualPageServerReads/ActualPageServerReadAheads vengono aggiunti al codice XML del piano di query per i piani effettivi. Ad esempio:

<RunTimeCountersPerThread Thread="8" ActualRows="90466461" ActualRowsRead="90466461" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="133645" ActualCPUms="85105" ActualScans="1" ActualLogicalReads="6032256" ActualPhysicalReads="0" ActualPageServerReads="0" ActualReadAheads="6027814" ActualPageServerReadAheads="5687297" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobPageServerReads="0" ActualLobReadAheads="0" ActualLobPageServerReadAheads="0" />

Nota

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 DMF sys.dm_io_virtual_file_stats() è il modo principale per monitorare le operazioni di I/O del database SQL. Le caratteristiche di I/O in Hyperscale sono diverse a causa dell'architettura distribuita. Il centro di questa sezione è rappresentato dalle operazioni di I/O (letture e scritture) nei file di dati, come illustrato in questo DMF. In Hyperscale, ogni file di dati visibile in questo DMF corrisponde a un server di pagine remoto. La cache RBPEX qui menzionata è una cache locale basata su SSD, ovvero una cache non di copertura nella replica di calcolo.

Utilizzo della cache RBPEX locale

La cache RBPEX locale esiste nella replica di calcolo, nell'archiviazione SSD locale. Pertanto, le operazioni di I/O su questa cache sono più veloci di quelle sui server di pagine remoti. Attualmente, sys.dm_io_virtual_file_stats() in un database Hyperscale ha una riga speciale che segnala l'operazione di I/O sulla cache RBPEX locale nella replica di calcolo. Questa riga ha il valore 0 per le colonne database_id e file_id. Ad esempio, la query seguente restituisce le statistiche di utilizzo di RBPEX dall'avvio del database.

select * from sys.dm_io_virtual_file_stats(0,NULL);

Il rapporto tra le letture eseguite su RBPEX e quelle aggregate eseguite su tutti gli altri file di dati fornisce il rapporto di riscontri nella cache RBPEX. Il contatore RBPEX cache hit ratio viene esposto anche nella DMV sys.dm_os_performance_counters dei contatori delle prestazioni.

Letture di dati

  • Quando le letture vengono rilasciate dal motore di database di SQL Server in una replica di calcolo, è possibile gestirle dalla cache RBPEX locale, da server di pagine remoti o da una combinazione dei due se si leggono più pagine.
  • Quando la replica di calcolo legge alcune pagine da un file specifico, ad esempio file_id 1, se questi dati risiedono esclusivamente nella cache RBPEX locale, tutte le operazioni di I/O per questa lettura vengono considerate su file_id 0 (RBPEX). Se alcune parti di quei dati si trovano nella cache RBPEX locale e altre si trovano in un server di pagine remoto, le operazioni di I/O vengono considerate su file_id 0 per la parte servita da RBPEX, mentre la parte servita dal server di pagine remoto viene considerata su file_id 1.
  • Quando una replica di calcolo richiede una pagina a un determinato LSN da un server di pagine, se il server di pagine non ha raggiunto l'LSN richiesto, la lettura nella replica di calcolo attenderà fino a quando il server di pagine non viene aggiornato prima che la pagina venga restituita alla replica di calcolo. Per qualsiasi lettura da un server di pagine nella replica di calcolo, verrà visualizzato il tipo di attesa PAGEIOLATCH_* se è in attesa di quella operazione di 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, come quelle read-ahead, vengono spesso eseguite usando letture "Scatter-Gather". Ciò consente di leggere fino a 4 MB di pagine alla volta, considerate come una sola lettura nel motore di database di SQL Server. Tuttavia, quando i dati letti si trovano in RBPEX, queste letture vengono considerate come più letture da 8 KB singole, poiché il pool di buffer e RBPEX usano sempre pagine da 8 KB. Di conseguenza, il numero di operazioni di I/O di lettura visualizzate su RBPEX può essere maggiore rispetto al 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 nei server di pagine corrispondenti.
  • Le scritture eseguite nella replica di calcolo sono prevalentemente scritture nella cache RBPEX locale (file_id 0). Per le scritture su file logici di dimensioni superiori a 8 KB, in altre parole quelle eseguite con Gather-write, ogni operazione di scrittura viene convertita in più scritture singole da 8 KB nella cache RBPEX, poiché il pool di buffer e RBPEX usano sempre pagine da 8 KB. Di conseguenza, il numero di operazioni di I/O di scrittura visualizzate su RBPEX può essere maggiore rispetto al numero effettivo di operazioni di I/O eseguite dal motore.
  • Anche i file non RBPEX o i file di dati diversi da file_id 0 che corrispondono ai server di pagine mostrano le scritture. Nel livello di servizio Hyperscale queste scritture sono simulate, perché le repliche di calcolo non scrivono mai direttamente nei server di pagine. Le operazioni di I/O al secondo di scrittura e la produttività vengono considerate così come si verificano nella replica di calcolo, ma la latenza per i file di dati diversi da file_id 0 non riflette la latenza effettiva delle scritture del server di pagine.

Scritture di log

  • Nell'ambiente di calcolo primario viene eseguita una scrittura di log in file_id 2 di sys.dm_io_virtual_file_stats. Una scrittura di log nel calcolo primario è una scrittura nella zona di destinazione del log.
  • I record di log non vengono rafforzati nella replica secondaria in un commit. In Hyperscale, il log viene applicato dal servizio di log alle repliche secondarie in modo asincrono. Poiché le scritture di log non si verificano effettivamente nelle repliche secondarie, qualsiasi attribuzione delle operazioni di I/O dei log nelle repliche secondarie è solo a scopo di rilevamento.

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. Lo stesso valore viene segnalato nel portale di Azure come Percentuale I/O dei dati.

In un database Hyperscale, questa colonna segnala l'utilizzo delle operazioni di I/O al secondo relative al limite per l'archiviazione locale solo nella replica di calcolo, in particolare I/O su RBPEX e 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 l'obiettivo del servizio di database per aumentare il limitemassimo di operazioni di I/O al secondo per la governance delle risorse. Per la governance delle risorse delle operazioni di lettura e scrittura di RBPEX, il sistema conta singole operazioni di I/O da 8 KB, anziché di dimensioni maggiori che possono essere rilasciate dal motore di database di SQL Server.

I dati delle operazioni di I/O sui server di pagine remoti non vengono riportati nelle visualizzazioni di utilizzo delle risorse o nel portale, ma nel DMF sys.dm_io_virtual_file_stats(), come indicato in precedenza.

Risorse aggiuntive