Condividi tramite


Risoluzione dei problemi relativi alla coda di ripristino in un gruppo di disponibilità AlwaysOn

Questo articolo fornisce soluzioni ai problemi relativi alla coda di ripristino.

Che cos'è la coda di ripristino?

Sintomi e cause di rollforward/ripristino: le modifiche apportate alla replica primaria in un database del gruppo di disponibilità vengono inviate a tutte le repliche secondarie definite nello stesso gruppo di disponibilità. Dopo che queste modifiche arrivano alle repliche secondarie, vengono prima scritte nel file di log delle transazioni del database del gruppo di disponibilità. Microsoft SQL Server usa quindi l'operazione di ripristino o ripristino per aggiornare i file di database.

Se le modifiche apportate a un gruppo di disponibilità arrivano e si rafforzano nel file di log delle transazioni del database più velocemente di quanto possano essere recuperate, viene creata una coda di ripristino. Questa coda è costituita da transazioni del log delle transazioni con protezione avanzata che non sono state ripristinate e recuperate nel database.

Sintomi ed effetti della coda di recupero (rollforward)

L'esecuzione di query su repliche primarie e secondarie restituisce risultati diversi

I carichi di lavoro di sola lettura che eseguono query su repliche secondarie potrebbero eseguire query sui dati non aggiornati. Se si verifica la coda di ripristino, le modifiche ai dati nel database di replica primaria potrebbero non essere riflesse nel database secondario quando si eseguono query sugli stessi dati.

Anche se le modifiche arrivano al database secondario e vengono scritte nel file di log del database, le modifiche non verranno sottoposte a query finché non vengono ripristinate e ripristinate nei file di database. L'operazione di ripristino rende le modifiche leggibili.

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".

Il tempo di failover è più lungo o il tempo RTO viene superato

L'obiettivo del tempo di ripristino (RTO) è il tempo di inattività massimo del database che un'organizzazione può gestire. RTO descrive anche la velocità con cui l'organizzazione può ottenere nuovamente l'accesso al database dopo un'interruzione. Se una coda di ripristino sostanziale è presente in una replica secondaria quando si verifica un failover, il ripristino potrebbe richiedere più tempo. Dopo il ripristino, il database passerà al ruolo primario e rappresenterà lo stato del database esistente prima del failover. Un tempo di ripristino più lungo può ritardare la velocità di ripresa della produzione dopo un failover.

Varie funzionalità di diagnostica segnalano la coda di ripristino del gruppo di disponibilità

Nel caso della coda di ripristino, il dashboard Always On in SQL Server Management Studio (SSMS) potrebbe segnalare un gruppo di disponibilità non integro.

Come verificare la presenza di code di ripristino (rollforward)

La coda di ripristino è una misura per database che può essere controllata usando il dashboard Always On nella replica primaria o usando la dmv (Dynamic Management View) sys.dm_hadr_database_replica_states nella replica primaria o secondaria. Monitor prestazioni contatori controllare la coda di ripristino e la frequenza di ripristino. Questi contatori devono essere controllati sulla replica secondaria.

Le sezioni successive forniscono metodi per monitorare attivamente la coda di ripristino del database del gruppo di disponibilità.

Query sys.dm_hadr_database_replica_states

La sys.dm_hadr_database_replica_states DMV segnala una riga per ogni database del gruppo di disponibilità. Una colonna del report è redo_queue_size. Questo valore è la dimensione della coda di recupero misurata in kilobyte. È possibile configurare una query simile alla query seguente per monitorare qualsiasi tendenza delle dimensioni della coda di ripristino ogni 30 secondi. La query viene eseguita 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 l'aspetto dell'output.

Screenshot dell'output per la query che segnala i dati per la replica secondaria in cui sono rilevanti redo_queue_size e redo_rate.

Esaminare la coda di ripristino nel dashboard Always On

Per esaminare la coda di ripristino, seguire questa procedura:

  1. Aprire il dashboard Always On in SSMS facendo clic con il pulsante destro del mouse su un gruppo di disponibilità in SSMS Esplora oggetti.

  2. Selezionare Mostra dashboard.

    I database del gruppo di disponibilità sono elencati per ultimo e sono presenti alcuni dati segnalati nei database. Sebbene le dimensioni della coda di rollforward (KB) e la frequenza di rollforward (KB/sec) non siano elencate per impostazione predefinita, è possibile aggiungerle a questa visualizzazione, come illustrato nello screenshot nel passaggio successivo.

  3. Per aggiungere questi contatori, fare clic con il pulsante destro del mouse sull'intestazione sopra i report del database e scegliere nell'elenco delle colonne disponibili.

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

    Screenshot che mostra l'aggiunta dei 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.

Esaminare la coda di ripristino in Monitor prestazioni

La dimensione della coda di ripristino è univoca per ogni replica e database secondario. Di conseguenza, per esaminare la coda di ripristino di un database del gruppo di disponibilità, seguire questa procedura:

  1. Aprire Monitor prestazioni nella replica secondaria.

  2. Selezionare il pulsante Aggiungi (contatore).

  3. In Contatori disponibili selezionare SQLServer:Replica di database e quindi selezionare Coda di ripristino e Byte di ripristino/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

In questa sezione viene illustrato come interpretare i valori correlati alla coda di ripristino determinata nella sezione precedente.

Quando si verifica un problema durante la coda di ripristino? Quanto è consigliabile tollerare la coda di ripristino?

Si potrebbe presupporre che se la coda di ripristino segnala un valore pari a 0, ciò significa che non si verifica alcun accodamento di ripristino al momento del report. Tuttavia, quando l'ambiente di produzione è occupato, è consigliabile osservare che la coda di ripristino segnala spesso un valore diverso da zero anche in un ambiente AlwaysOn integro. Durante la produzione tipica, è consigliabile osservare questa fluttuazione tra 0 e un valore diverso da zero.

Se si osserva un aumento della coda di ripristino nel tempo, viene garantita un'ulteriore indagine. Questa attività aggiuntiva indica che qualcosa è cambiato. Se si osserva una crescita improvvisa nella coda di ripristino, le misure seguenti sono utili per la risoluzione dei problemi:

  • Frequenza di rollforward del log (KB/sec) (dashboard AlwaysOn)
  • Redo_rate nel sys.dm_hadr_database_replica_states DMV

Ottenere le percentuali di base per la frequenza di rollforward

Durante le prestazioni AlwaysOn integre, monitorare la frequenza di rollforward nei database del gruppo di disponibilità occupato. Che aspetto hanno durante gli orari di ufficio in genere occupati? Quali sono queste percentuali durante i periodi di manutenzione, quando le transazioni di grandi dimensioni (ricompilazione degli indici, processi ETL) determinano una maggiore velocità effettiva delle transazioni nel sistema? È possibile confrontare questi valori quando si osserva l'aumento della coda di ripristino per determinare cosa è cambiato. Il carico di lavoro potrebbe essere maggiore del solito. Se la frequenza di rollforward è inferiore, è possibile che siano necessarie ulteriori indagini per determinare il motivo.

I volumi del carico di lavoro sono importanti

Quando si dispone di carichi di lavoro di grandi dimensioni,ad esempio un'istruzione UPDATE rispetto a un milione di righe, una ricompilazione dell'indice in una tabella da 1 terabyte o anche un batch ETL che inserisce milioni di righe, è consigliabile prevedere una certa crescita della coda di ripristino, immediatamente o nel tempo. Questo è previsto quando un numero elevato di modifiche viene apportato improvvisamente nel database del gruppo di disponibilità.

Come diagnosticare la coda di ripristino (rollforward)

Dopo aver identificato la coda di ripristino per un database del gruppo di disponibilità di replica secondaria specifico, connettersi alla replica secondaria e quindi eseguire una query sys.dm_exec_requests per determinare e wait_type wait_time per i thread di ripristino. Ecco una query che può essere eseguita in un ciclo. Si sta cercando una frequenza elevata di uno o più tipi di attesa e persino i tempi di attesa per tali tipi di attesa. Ecco una query di esempio che viene eseguita ogni secondo e segnala i tipi di attesa e i tempi di attesa per il gruppo di disponibilità, "agdb":

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 un output significativo del tipo di attesa, la coda di ripristino deve essere osservata in modo da aumentare quando si usa uno dei metodi descritti in precedenza per monitorare questa condizione.

In questo esempio vengono segnalati alcuni tipi di attesa correlati all'I/O (PAGEIOLATCH_UP, PAGEIOATCH_EX). Monitorare per verificare se questi tipi di attesa continuano ad avere i valori più grandi wait_times , come indicato nella colonna successiva.

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

Tipi di attesa di rollforward di SQL Server

Quando viene identificato un tipo di attesa, vedere l'articolo SEGUENTE SQL Server 2016/2017: Modello di rollforward della replica secondaria del gruppo di disponibilità e prestazioni - Microsoft Tech Community come riferimento incrociato per i tipi di attesa comuni che causano la coda di ripristino e per risolvere il problema.

Thread di rollforward bloccati nei server di report secondari

Se la soluzione indirizza la creazione di report (esecuzione di query) sui database del gruppo di disponibilità nella replica secondaria, queste query di sola lettura acquisiscono blocchi di stabilità dello schema (Sch-S). Questi blocchi Sch-S possono impedire ai thread di rollforward di acquisire blocchi di modifica dello schema (Sch-M) (noti anche come "blocchi di modifica dello schema" o LCK_M_SCH_M) per apportare modifiche DDL (Data Definition Language), ad esempio ALTER TABLE o ALTER INDEX. Un thread di rollforward bloccato non può applicare record di log fino a quando non viene sbloccato. Ciò può causare la coda di ripristino.

Per verificare la presenza di prove cronologiche di un rollforward bloccato, aprire i file di traccia Xevent AlwaysOn_health nella replica secondaria usando SSMS. lock_redo_blocked Cercare gli eventi.

Screenshot che mostra la verifica della presenza di prove cronologiche di un rollforward bloccato.

Usare Monitor prestazioni per monitorare attivamente l'impatto sul rollforward bloccato per la coda di ripristino. Aggiungere i contatori SQL Server::D atabase Replica::Redo bloccato/sec e SQL Server::D atabase Replica::Coda di ripristino. Lo screenshot seguente mostra un ALTER TABLE ALTER COLUMN comando eseguito sulla replica primaria mentre viene eseguita una query con esecuzione prolungata sulla stessa tabella nella replica secondaria. Il contatore Rollforward bloccato/sec indica che il ALTER TABLE ALTER COLUMN comando viene eseguito. Mentre la query a esecuzione prolungata è in esecuzione nella stessa tabella della replica secondaria, eventuali modifiche successive sul database primario causeranno un aumento della coda di ripristino.

Screenshot che mostra un monitoraggio per il tipo di attesa del blocco di modifica dello schema.

Monitorare il tipo di attesa del blocco di modifica dello schema che il thread di rollforward tenta di acquisire. A tale scopo, usare la query descritta in precedenza per controllare i tipi di attesa segnalati per le operazioni di rollforward su sys.dm_exec_requests. È possibile osservare il tempo di attesa crescente per l'oggetto LCK_M_SCH_M nel blocco di rollforward in corso.

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

Rollforward a thread singolo

SQL Server ha introdotto il ripristino parallelo per i database di replica secondaria in Microsoft SQL Server 2016. Se si verifica una coda di ripristino quando si esegue SQL Server 2012 o Microsoft SQL Server 2014, è possibile eseguire l'aggiornamento a una versione successiva del programma per migliorare le prestazioni di rollforward nell'ambiente di produzione.

Un rollforward a thread singolo può verificarsi anche in un secondo momento, versioni più avanzate di SQL Server in cui viene usata l'architettura di ripristino parallelo. In queste versioni un'istanza di SQL Server può usare fino a 100 thread per un rollforward parallelo. A seconda del numero di processori e di database del gruppo di disponibilità, i thread di rollforward paralleli vengono allocati fino a un massimo di 100 thread totali. Se viene raggiunto il limite di rollforward di 100 thread, a alcuni database nel gruppo di disponibilità viene assegnato un singolo thread di rollforward.

Per determinare se il database del gruppo di disponibilità usa il ripristino parallelo, connettersi alla replica secondaria e usare la query seguente per determinare il numero di righe (thread) che applicano il ripristino per il database del gruppo di disponibilità. Nell'esempio seguente, se il database "agdb" è un singolo thread 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')

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

Se si verifica che il database usi un rollforward a thread singolo, esaminare l'algoritmo descritto in precedenza per determinare se SQL Server supera il numero di 100 thread di lavoro dedicati per il ripristino parallelo. Una condizione di questo tipo potrebbe essere il motivo per cui il database "agdb" usa solo un singolo thread per il ripristino.

SQL Server 2022 usa ora un nuovo algoritmo di recupero parallelo in modo che i thread di lavoro vengano assegnati per il ripristino parallelo in base al carico di lavoro. In questo modo si elimina la possibilità che un database occupato rimanga in un ripristino a thread singolo. Per altre informazioni, vedere la sezione Utilizzo thread per gruppi di disponibilità di "Prerequisiti, restrizioni e consigli per i gruppi di disponibilità AlwaysOn".