Risolvere i problemi di accodamento del ripristino (redo) in un gruppo di disponibilità Always On

Sommario

Questo articolo consente di risolvere i problemi della coda di ripristino, detta anche coda di redo, su una replica secondaria di un gruppo di disponibilità Always On di SQL Server. Spiega cos'è la coda di ripristino, come verificare le dimensioni della coda di redo e la velocità di redo, come interpretare i valori e come diagnosticare e correggere le cause comuni del ritardo del redo, inclusi i thread di redo bloccati e il redo a thread singolo.

Che cos'è l'accodamento di ripristino

Le modifiche apportate alla replica primaria in un database del gruppo di disponibilità vengono inviate a ogni replica secondaria nello stesso gruppo di disponibilità. Dopo che le modifiche arrivano a una replica secondaria, vengono innanzitutto scritte in modo permanente nel file di log delle transazioni del database del gruppo di disponibilità. Microsoft SQL Server quindi usa l'operazione recovery o redo per applicare tali record di log ai file di database.

Se le modifiche arrivano e vengono rese permanenti nel log delle transazioni più rapidamente di quanto possano essere ripristinate, si forma una coda di ripristino. Questa coda è l'insieme dei record di log consolidati non ancora applicati al database.

Sintomi ed effetti dell'accodamento di recupero

Dati obsoleti nelle repliche secondarie

I carichi di lavoro di sola lettura che eseguono query su repliche secondarie potrebbero ottenere dati non aggiornati. Se si verifica l'accodamento delle operazioni di ripristino, le modifiche recenti nel database della replica primaria non sono ancora visibili nella replica secondaria quando si interrogano gli stessi dati.

Le modifiche arrivano al secondario e vengono scritte nel file di log del database, ma non sono leggibili finché redo non le applica ai file di dati.

Per altre informazioni, vedere la sezione Latenza dei dati nella replica secondaria di "Differenze tra le modalità di disponibilità per un gruppo di disponibilità AlwaysOn".

Tempo di failover più lungo o superamento dell'RTO

L'obiettivo del tempo di ripristino (RTO) è il tempo di inattività massimo del database che un'organizzazione può tollerare e quanto rapidamente l'organizzazione può recuperare l'uso del database dopo un'interruzione. Se su una replica secondaria è presente una coda di ripristino di grandi dimensioni quando si verifica un failover, il redo sulla nuova replica primaria può richiedere più tempo dell'RTO. Al termine del redo, il database passa al ruolo primario e riflette lo stato che esisteva prima del failover. Un tempo di rollforward più lungo ritarda la velocità di ripresa della produzione.

Gli strumenti di diagnostica segnalano un gruppo di disponibilità non integro

Quando l'accodamento del redo è significativo, il dashboard di Always On in SQL Server Management Studio (SSMS) potrebbe mostrare il gruppo di disponibilità come non integro.

Verificare la presenza di accodamento di ripristino

La coda di ripristino è una misura per ogni database. È possibile verificarlo dal dashboard di Always On sulla replica primaria oppure interrogando la vista di gestione dinamica (DMV) sys.dm_hadr_database_replica_states sulla replica primaria o su quella secondaria. I contatori di Monitor prestazioni riportano anche la dimensione della coda di ripristino e la velocità di ripetizione. Controllare tali contatori nella replica secondaria.

Le sezioni seguenti illustrano metodi per monitorare attivamente la coda di ripristino del database nel gruppo di disponibilità.

Query sys.dm_hadr_database_replica_states

La sys.dm_hadr_database_replica_states DMV riporta una riga per ogni database del gruppo di disponibilità. La redo_queue_size colonna mostra le dimensioni della coda di ripristino in kilobyte. Per monitorare l'andamento delle dimensioni della coda di ripristino ogni 30 secondi, configurare una query simile alla seguente. Eseguilo nella replica primaria. Usa il is_local=0 predicato per segnalare i dati per la replica secondaria, dove redo_queue_size e redo_rate sono rilevanti.

WHILE 1=1
BEGIN
SELECT drcs.database_name, ars.role_desc, drs.redo_queue_size, drs.redo_rate,
ars.recovery_health_desc, ars.connected_state_desc, ars.operational_state_desc, ars.synchronization_health_desc, *
FROM sys.dm_hadr_availability_replica_states ars JOIN sys.dm_hadr_database_replica_cluster_states drcs ON ars.replica_id=drcs.replica_id
JOIN sys.dm_hadr_database_replica_states drs ON drcs.group_database_id=drs.group_database_id
WHERE ars.role_desc='SECONDARY' AND drs.is_local=0
waitfor delay '00:00:30'
END

Ecco come appare l'output.

Schermata dell'output della query che restituisce i dati per la replica secondaria, in cui redo_queue_size e redo_rate sono rilevanti.

Esamina la coda di ripristino nel dashboard di Always On

Per esaminare la coda di ripristino, seguire questa procedura:

  1. In SSMS Esplora oggetti selezionare e tenere premuto (o fare clic con il pulsante destro del mouse) un gruppo di disponibilità per aprire il menu di scelta rapida.

  2. Selezionare Mostra dashboard.

    I database del gruppo di disponibilità sono elencati per ultimo, con alcuni dati segnalati per ogni database. Le dimensioni della coda di rollforward (KB) e la frequenza di rollforward (KB/sec) non sono elencate per impostazione predefinita, ma è possibile aggiungerle alla visualizzazione, come illustrato nel passaggio successivo.

  3. Per aggiungere questi contatori, selezionare e tenere premuto (o fare clic con il pulsante destro del mouse) sull'intestazione sopra i report del database, quindi scegliere le colonne da visualizzare.

  4. Per aggiungere le dimensioni della coda di rollforward (KB) e la frequenza di rollforward (KB/sec), selezionare e tenere premuto (o fare clic con il pulsante destro del mouse) sull'intestazione evidenziata in rosso nello screenshot seguente.

    Schermata che mostra come aggiungere i contatori Redo Queue Size (KB) e Redo Rate (KB/sec).

    Per impostazione predefinita, il dashboard AlwaysOn aggiorna automaticamente le dimensioni della coda di rollforward (KB) e la frequenza di rollforward (KB/sec) ogni 60 secondi.

    Screenshot che mostra i contatori di aggiornamento impostati su ogni 60 secondi.

Esamina la coda di ripristino in Monitoraggio prestazioni

Ciascuna replica secondaria e ciascun database dispone di una propria dimensione della coda di ripristino. Per esaminare la coda di ripristino per un database del gruppo di disponibilità, seguire questa procedura:

  1. Apri Monitoraggio prestazioni sulla replica secondaria.

  2. Selezionare il pulsante Aggiungi (contatore).

  3. In Contatori disponibili, selezionare SQLServer:Database Replica, quindi selezionare i contatori Recovery Queue e Redone Bytes/sec.

  4. Nella casella di riepilogo Istanza selezionare il database del gruppo di disponibilità da monitorare per la coda di ripristino.

  5. Selezionare Aggiungi>OK.

    Ecco l'aspetto dell'aumento della coda di ripristino.

    Screenshot che mostra un aumento della coda di ripristino.

Interpretazione dei valori della coda di ripristino

Questa sezione illustra come interpretare i valori di accodamento di ripristino raccolti nella sezione precedente.

Quando l'accodamento del ripristino è un problema

Un valore pari a 0 per la coda di ripristino indica che al momento del report non è presente alcun backlog di redo. In un ambiente di produzione occupato, la coda di ripristino spesso segnala un valore diverso da zero anche quando il gruppo di disponibilità è integro. Durante la produzione tipica, si prevede che il valore fluttua tra 0 e un valore diverso da zero.

Se la coda di ripristino aumenta nel tempo, approfondire l'indagine. La crescita indica che qualcosa è cambiato. Quando si osserva una crescita improvvisa, le misurazioni seguenti sono utili per la risoluzione dei problemi:

  • Frequenza di rollforward log (KB/sec) ( dashboard AlwaysOn)
  • redo_rate pollici sys.dm_hadr_database_replica_states

Stabilire i tassi di ripristino di base

Durante il normale funzionamento di Always On, monitorare la velocità di ripristino nei database più attivi del gruppo di disponibilità. Acquisite i tassi di acquisizione durante le normali ore lavorative e durante le finestre di manutenzione, quando operazioni di grandi dimensioni, come la ricompilazione degli indici o i processi ETL, determinano un throughput più elevato. Confronta queste baseline quando osservi una crescita della coda di ripristino per identificare cosa è cambiato. Il carico di lavoro potrebbe essere maggiore del solito oppure la frequenza di redo potrebbe essere inferiore al previsto, e ciò richiede ulteriori indagini.

Tenere conto del volume del carico di lavoro

Carichi di lavoro di grandi dimensioni(ad esempio un'istruzione UPDATE su un milione di righe, una ricompilazione dell'indice in una tabella da 1 TB o un batch ETL che inserisce milioni di righe) provocano in genere una certa crescita della coda di ripristino, immediatamente o nel tempo. Questa crescita è prevista quando vengono apportate improvvisamente molte modifiche nel database del gruppo di disponibilità.

Diagnosticare l'accodamento di ripristino

Dopo aver identificato l'accodamento del ripristino per uno specifico database del gruppo di disponibilità della replica secondaria, connettersi alla replica secondaria, quindi interrogare sys.dm_exec_requests per verificare wait_type e wait_time per i thread di ripristino. Si cerca una frequenza elevata di uno o più tipi di attesa e tempi di attesa elevati per tali tipi. La query di esempio seguente viene eseguita ogni cinque secondi e segnala i tipi di attesa e i tempi di attesa per il database agdbdel gruppo di disponibilità :

WHILE (1=1)
BEGIN
SELECT db_name(database_id) AS dbname, command, session_id, database_id, wait_type, wait_time,
os.runnable_tasks_count, os.pending_disk_io_count FROM sys.dm_exec_requests der JOIN sys.dm_os_schedulers os
ON der.scheduler_id=os.scheduler_id
WHERE command IN('PARALLEL REDO HELP TASK', 'PARALLEL REDO TASK', 'DB STARTUP')
AND database_id= db_id('agdb')
waitfor delay '00:00:05.000'
END

Importante

Per ottenere un output significativo per il tipo di attesa, la coda di recupero deve essere in crescita quando si raccolgono questi dati utilizzando uno dei metodi descritti in precedenza.

Nell'esempio seguente vengono segnalati alcuni tipi di attesa correlati all'I/O (PAGEIOLATCH_UP, PAGEIOLATCH_EX). Monitorare se questi tipi di attesa continuano a visualizzare i valori più grandi wait_time , come indicato nella colonna successiva.

Screenshot che mostra i tempi di attesa più grandi segnalati nella colonna successiva.

Identificare i tipi di attesa di ripristino

Dopo aver identificato un tipo di attesa, utilizzare modello redo e prestazioni della replica secondaria del gruppo di disponibilità come riferimento per i tipi di attesa comuni che causano l'accodamento del recupero e per indicazioni su come risolvere il problema.

Thread di ripristino bloccati nelle repliche secondarie di sola lettura

Se la soluzione indirizza la creazione di report (query) sui database del gruppo di disponibilità in una replica secondaria, tali query di sola lettura acquisiscono blocchi di stabilità dello schema (Sch-S). I blocchi Sch-S possono impedire ai thread redo di acquisire i blocchi di modifica dello schema (Sch-M) (noti anche come blocchi di modifica dello schema, o LCK_M_SCH_M) necessari per applicare modifiche DDL come ALTER TABLE o ALTER INDEX. Un thread di redo bloccato non può applicare i record di log finché non viene sbloccato, causando l'accodamento delle operazioni di ripristino.

Per verificare la presenza di evidenze storiche di un redo bloccato, aprire i file di traccia degli eventi estesi AlwaysOn_health sulla replica secondaria tramite SSMS. lock_redo_blocked Cercare gli eventi.

Schermata che mostra il controllo della presenza di evidenze storiche di un redo bloccato.

Usa Monitor prestazioni per monitorare attivamente l'impatto del redo bloccato sulla coda di ripristino. Aggiungi i contatori SQL Server:Database Replica\Redo bloccato/sec e SQL Server:Database Replica\Recovery Queue. La schermata seguente mostra un comando ALTER TABLE ALTER COLUMN eseguito sulla replica primaria mentre una query di lunga durata è in esecuzione sulla stessa tabella nella replica secondaria. Il contatore Redo bloccato/sec registra picchi quando viene eseguito il comando ALTER TABLE ALTER COLUMN. Mentre la query a esecuzione prolungata è attiva nella stessa tabella della replica secondaria, tutte le modifiche successive apportate al database primario aumentano la coda di ripristino.

Schermata del monitor per il tipo di attesa del blocco di modifica dello schema.

Monitorare il tipo di attesa per il blocco di modifica dello schema che il thread di redo tenta di acquisire. Usare la query precedente per verificare i tipi di attesa segnalati per le operazioni di ripristino in sys.dm_exec_requests. È possibile vedere il tempo di attesa per LCK_M_SCH_M aumentare mentre il redo è bloccato.

Screenshot che mostra il tempo di attesa crescente per il LCK_M_SCH_M.

Ripristino a thread singolo

SQL Server 2016 ha introdotto il ripristino parallelo per i database di replica secondaria. Se si esegue una versione precedente, ad esempio SQL Server 2014 o SQL Server 2012, eseguire l'aggiornamento a una versione supportata per ottenere un rollforward parallelo e migliorare le prestazioni di rollforward.

Il redo a singolo thread può comunque verificarsi in SQL Server da 2016 a 2019, che usano l'architettura di ripristino parallela. In queste versioni, un'istanza di SQL Server può usare fino a 100 thread per il rollforward parallelo. Il sistema alloca thread di ripristino paralleli tra i database del gruppo di disponibilità in base al numero di processori e di database, fino a un totale di 100 thread. Quando viene raggiunto il limite di 100 thread, il sistema assegna ad alcuni database del gruppo di disponibilità un unico thread di ripristino.

Per verificare se il database del gruppo di disponibilità utilizza il ripristino parallelo, connettersi alla replica secondaria ed eseguire la query seguente per contare le righe (thread) che applicano il ripristino al database. Nell'esempio seguente, se il agdb database ha una singola riga e il relativo comando è DB STARTUP, il carico di lavoro di ripristino potrebbe trarre vantaggio dal ripristino parallelo.

SELECT db_name(database_id) AS dbname, command, session_id, database_id, wait_type, wait_time,
os.runnable_tasks_count, os.pending_disk_io_count FROM sys.dm_exec_requests der JOIN sys.dm_os_schedulers os
ON der.scheduler_id=os.scheduler_id
WHERE command IN ('PARALLEL REDO HELP TASK', 'PARALLEL REDO TASK', 'DB STARTUP')
AND database_id= db_id('agdb')

Schermata che mostra come determinare se il database del gruppo di disponibilità usa il ripristino in parallelo.

Se il database usa il redo a thread singolo, rivedere l'algoritmo precedente per verificare se SQL Server sta superando i 100 thread di lavoro dedicati al ripristino parallelo. Il raggiungimento di tale limite può spiegare perché agdb utilizza un solo thread di redo.

SQL Server 2022 e versioni successive usano un algoritmo di ripristino parallelo che assegna thread di lavoro in base al carico di lavoro, eliminando la possibilità che un database molto attivo rimanga nella fase di redo a thread singolo. Per altre informazioni, vedere la sezione Utilizzo dei thread in base ai gruppi di disponibilità di "Prerequisiti, restrizioni e consigli per i gruppi di disponibilità AlwaysOn".

Contenuti correlati

  • Last updated on