Diagnostica della risoluzione dei problemi di prestazioni di SQL Hyperscale

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 Azure SQL database sono il punto di partenza di un'analisi delle prestazioni. Tuttavia, data l'architettura distribuita di Hyperscale, sono state aggiunte funzionalità di diagnostica aggiuntive per facilitare. Questo articolo descrive i dati di diagnostica specifici di Hyperscale.

Attese di limitazione della frequenza dei log

Ogni Azure SQL livello di servizio database ha limiti di frequenza di generazione dei log applicati tramite la governance della frequenza dei log. In Hyperscale il limite di generazione dei log è attualmente impostato su 100 MB/sec, indipendentemente dal livello di servizio. Tuttavia, in alcuni casi la frequenza di generazione dei log nella replica di calcolo primaria deve essere limitata per mantenere i contratti di servizio di recuperabilità. Questa limitazione si verifica quando un server di pagine o un'altra replica di calcolo è significativamente dietro l'applicazione di nuovi record di log dal servizio di log.

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:

Tipo di attesa Descrizione
RBIO_RG_STORAGE Si verifica quando viene limitata la frequenza di generazione dei log dei log dei nodi di calcolo primario del database Hyperscale a causa di un consumo ritardato dei log nei server di pagine.
RBIO_RG_DESTAGE Si verifica quando viene limitata la frequenza di generazione dei log dei nodi di calcolo del database Hyperscale a causa del ritardo del consumo di log dall'archiviazione dei log a lungo termine.
RBIO_RG_REPLICA Si verifica quando viene limitata la frequenza di generazione dei log dei nodi di calcolo del database Hyperscale a causa del ritardo del consumo di log da parte delle repliche secondarie leggibili.
RBIO_RG_GEOREPLICA Si verifica quando viene limitata la frequenza di generazione dei log dei nodi di calcolo del database Hyperscale a causa del ritardo del consumo di log dalla replica geografica secondaria.
RBIO_RG_LOCALDESTAGE Si verifica quando viene limitata la frequenza di generazione dei log dei log dei nodi di calcolo del database Hyperscale a causa del ritardo del consumo di 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 per la replica di calcolo vengono archiviati nel pool di buffer (in memoria) e nella cache RBPEX (Resilient Buffer Extension) locale che è una cache parziale (non di copertura) delle pagine di dati. Questa cache RBPEX locale viene ridimensionata proporzionalmente alle dimensioni di calcolo ed è tre volte la memoria del livello di calcolo. RBPEX è simile al pool di buffer in quanto ha i dati a cui si accede più di frequente. Ogni server di pagine, d'altra parte, dispone di una cache RBPEX per la parte del database gestita.

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 eseguita una chiamata di funzione getPage(pageId, LSN) e la pagina viene recuperata dal server di pagine corrispondente. Le letture dai server di pagine sono letture remote e pertanto sono più lente rispetto alle letture dal RBPEX locale. Durante la risoluzione dei problemi di prestazioni correlati alle operazioni di I/O, è necessario essere in grado di indicare quante operazioni di I/O sono state eseguite tramite letture del server di pagine remote relativamente più lente.

Diverse viste gestite dinamiche (DMV) e gli eventi estesi hanno 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 di runtime della query.

  • Le colonne per le letture del server di pagine del report sono disponibili nelle DMV di esecuzione e nelle viste del catalogo, ad esempio:

  • 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 SSMS 18.3 o versione successiva.

Statistiche dei file virtuali e contabilità di I/O

In Azure SQL Database il DMF sys.dm_io_virtual_file_stats() è il modo principale per monitorare database SQL I/O. Le caratteristiche di I/O in Hyperscale sono diverse a causa dell'architettura distribuita. In questa sezione ci concentriamo sull'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 indicata di seguito è 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, I/O rispetto a questa cache è più veloce rispetto a I/O rispetto ai server di pagine remoti. Attualmente, sys.dm_io_virtual_file_stats() in un database Hyperscale dispone di una riga speciale che segnala l'I/O rispetto alla cache RBPEX locale nella replica di calcolo. Questa riga ha il valore 0 per entrambe database_id le colonne 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);

Un rapporto tra letture eseguite su RBPEX e letture 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_countersdei contatori delle prestazioni.

Letture dei dati

  • Quando le letture vengono rilasciate dal motore di database di SQL Server in una replica di calcolo, possono essere gestite dalla cache RBPEX locale o dai server di pagine remoti o da una combinazione dei due se leggono più pagine.
  • Quando la replica di calcolo legge alcune pagine da un file specifico, ad esempio file_id 1, se questi dati si trovano esclusivamente nella cache RBPEX locale, tutti gli I/O per questa lettura vengono conteggiati su file_id 0 (RBPEX). Se alcune parti di tali dati si trovano nella cache RBPEX locale e alcune parti si trovano in un server di pagine remoto, L'I/O viene tenuto conto di file_id 0 per la parte servita da RBPEX e la parte servita dal server di pagine remoto viene conteggiata verso file_id 1.
  • Quando una replica di calcolo richiede una pagina in un determinato LSN da un server di pagine, se il server di pagine non è stato rilevato fino all'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 tale I/O. In Hyperscale questo tempo di attesa include sia il tempo necessario per recuperare la pagina richiesta nel server di pagine al LSN richiesto e il tempo necessario per trasferire la pagina dal server di pagina alla replica di calcolo.
  • Le letture di grandi dimensioni, ad esempio read-ahead, vengono spesso eseguite usando letture "Scatter-Gather". In questo modo è possibile leggere fino a 4 MB di pagine alla volta, considerate una sola lettura nel motore di database SQL Server. Tuttavia, quando i dati letti sono 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 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. I record di log del servizio log vengono invece riprodotti nei server di pagine corrispondenti.
  • Le scritture eseguite nella replica di calcolo sono prevalentemente scritture nel 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 in 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 del numero effettivo di operazioni di I/O eseguite dal motore.
  • I file non RBPEX o i file di dati diversi da file_id 0 che corrispondono ai server di pagine, mostrano anche le scritture. Nel livello di servizio Hyperscale queste scritture vengono 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 velocità effettiva vengono considerate 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

  • Nel calcolo primario viene conteggiata 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 con protezione avanzata 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 vengono effettivamente eseguite nelle repliche secondarie, qualsiasi contabilità 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 non Hyperscale, le operazioni di I/O al secondo di lettura e scrittura combinate nei file di dati, relative al limite di operazioni di I/O al secondo di governance delle risorse , vengono segnalate nelle visualizzazioni sys.dm_db_resource_stats e sys.resource_stats , nella avg_data_io_percent colonna . Lo stesso valore viene segnalato nella portale di Azure come Percentuale I/O dati.

In un database Hyperscale questa colonna segnala l'utilizzo delle operazioni di I/O al secondo dei dati rispetto al limite per l'archiviazione locale solo nella replica di calcolo, in particolare I/O rispetto a RBPEX e tempdb. Un valore del 100% in questa colonna indica che la governance delle risorse limita 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 IOPS 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 a 8 KB, invece di operazioni di I/O maggiori che possono essere rilasciate dal motore di database SQL Server.

L'I/O dei dati nei server di pagine remoti non viene segnalato nelle visualizzazioni di utilizzo delle risorse o nel portale, ma viene segnalato nel DMF sys.dm_io_virtual_file_stats(), come indicato in precedenza.

Risorse aggiuntive