Stabilire perché le modifiche della replica primaria non si riflettono sulla replica secondaria in un gruppo di disponibilità Always On

Si applica a:SQL Server

L'applicazione client completa con esito positivo un aggiornamento per la replica primaria, ma l'esecuzione di query sulla replica secondaria rivela che qui la modifica non è stata eseguita. Questo caso presume che lo stato di sincronizzazione della disponibilità sia integro. Nella maggior parte dei casi, questo comportamento si risolve automaticamente dopo pochi minuti.

Se dopo pochi minuti le modifiche non si riflettono ancora nella replica secondaria, potrebbe esserci un collo di bottiglia nel processo di sincronizzazione. La posizione del collo di bottiglia dipende dal fatto che la replica secondaria sia configurata per il commit sincrono o per il commit asincrono.

Commit sincrono

Ogni aggiornamento andato a buon fine sulla replica primaria è già stato sincronizzato con la replica secondaria oppure i record del log sono già stati scritti su disco nella replica secondaria per il consolidamento. Pertanto, il collo di bottiglia dovrebbe quindi trovarsi nel processo di ripristino in avanti che avviene dopo che il log viene scritto nella replica secondaria.

Tuttavia, una volta che il redo si è allineato, tutti i carichi di lavoro di lettura sulla replica secondaria utilizzano l'isolamento snapshot:

  • Transazioni di lunga durata nella replica primaria

  • Riesecuzione nella replica secondaria

Commit asincrono

Poiché il commit asincrono conferma una transazione non appena viene scritta sul disco locale, il collo di bottiglia può trovarsi in qualsiasi punto successivo a quel momento:

  • Transazioni di lunga durata nella replica primaria

  • Latenza di rete o velocità effettiva

  • Finalizzazione log nella replica secondaria

  • Riesecuzione sulla replica secondaria

Le sezioni seguenti descrivono le cause comuni per cui le modifiche apportate alla replica primaria non si riflettono sulla replica secondaria nelle query di sola lettura.

Transazioni attive di lunga durata

Una transazione con esecuzione prolungata nella replica primaria impedisce la lettura nella replica secondaria degli aggiornamenti.

Spiegazione

Tutti i carichi di lavoro di lettura nella replica secondaria sono query di isolamento dello snapshot. Nell'isolamento snapshot, i client di sola lettura visualizzano il database di disponibilità nella replica secondaria al punto di inizio della transazione attiva più vecchia nel log rieseguito. Se il commit di una transazione non avviene per ore, la transazione aperta impedisce a tutte le query di sola lettura di vedere qualsiasi nuovo aggiornamento.

Diagnosi e risoluzione

Nella replica primaria, usare DBCC OPENTRAN (Transact-SQL) per visualizzare le transazioni attive meno recenti e verificare se è possibile eseguirne il rollback. Una volta che le transazioni attive più vecchie vengono annullate e sincronizzate con la replica secondaria, i carichi di lavoro in lettura sulla replica secondaria possono vedere gli aggiornamenti nel database di disponibilità fino all'inizio di quella che in quel momento è la transazione attiva più vecchia.

Una latenza elevata o una velocità effettiva bassa della rete causa un accumulo di log nella replica primaria

Una latenza elevata o un velocità effettiva bassa della rete può impedire un invio sufficientemente rapido dei log alla replica secondaria.

Spiegazione

La replica primaria attiva il controllo di flusso per l'invio dei log quando ha superato il numero massimo consentito di messaggi non riconosciuti inviati alla replica secondaria. Finché alcuni di questi messaggi non vengono confermati, non possono essere inviati altri blocchi di log alla replica secondaria. Questa situazione può avere un impatto più grave sulla potenziale perdita di dati, compromettendo potenzialmente l'obiettivo del punto di ripristino (RPO, Recovery Point Objective).

Diagnosi e risoluzione

Un valore DMV log_send_queue_size elevato può indicare che i registri vengono trattenuti nella replica primaria. Dividendo questo valore per log_send_rate, è possibile ottenere subito una stima approssimativa di quanto velocemente può avvenire il recupero dei dati nella replica secondaria.

È anche utile controllare i due oggetti prestazioni SQL Server:Replica di disponibilità > Tempo controllo di flusso (ms/sec) e SQL Server:Replica di disponibilità > Controllo di flusso/sec. Moltiplicando questi due valori si ottiene quanto dell'ultimo secondo è stato impiegato nell'attesa della cancellazione del controllo di flusso. Maggiore il tempo di attesa del controllo di flusso, minore la velocità di invio.

Di seguito è riportato un elenco di metriche utili per diagnosticare la velocità effettiva e la latenza di rete. Per valutare l'utilizzo della rete, è possibile usare altri strumenti di Windows, ad esempio ping.exe.

  • DMV log_send_queue_size

  • DMV log_send_rate

  • Contatore delle prestazioni SQL Server:Database > Log Bytes Flushed/sec

  • Contatore delle prestazioni SQL Server:Database Mirroring > Send/Receive Ack Time

  • Contatore delle prestazioni SQL Server:Availability Replica > Bytes Sent to Replica/sec

  • Contatore delle prestazioni SQL Server:Availability Replica > Bytes Sent to Transport/sec

  • Contatore delle prestazioni SQL Server:Availability Replica > Flow Control Time (ms/sec)

  • Contatore delle prestazioni SQL Server:Availability Replica > Flow Control/sec

  • Contatore delle prestazioni SQL Server:Availability Replica > Resent Messages/sec

Per risolvere questo problema, provare ad aggiornare la larghezza di banda di rete o a rimuovere o ridurre traffico di rete non necessario.

Un altro carico di lavoro di reporting impedisce al thread di redo di essere eseguito

L'esecuzione di modifiche DDL (Data Definition Language) del thread di rollforward nella replica secondaria viene bloccata da una query di sola lettura con esecuzione prolungata. Il thread di redo deve essere sbloccato prima di poter rendere disponibili ulteriori aggiornamenti per il carico di lavoro di lettura.

Spiegazione

Nella replica secondaria, le query di sola lettura acquisiscono blocchi di stabilità dello schema (Sch-S). Questi blocchi Sch-S possono impedire al thread di ripristino di acquisire blocchi di modifica dello schema (Sch-M) per apportare qualsiasi modifica DDL. Un thread di redo bloccato non può applicare i record del log finché non viene sbloccato.

Diagnosi e risoluzione

Quando il thread di rollforward è bloccato, viene generato un evento esteso denominato sqlserver.lock_redo_blocked. Inoltre, è possibile interrogare la DMV sys.dm_exec_request sulla replica secondaria per individuare quale sessione blocca il thread REDO e quindi intraprendere le azioni correttive necessarie. La query seguente restituisce l'ID di sessione del carico di lavoro di reporting che sta bloccando il thread di redo.

select session_id, command, blocking_session_id, wait_time, wait_type, wait_resource   
from sys.dm_exec_requests where command = 'DB STARTUP'

È possibile lasciar finire il carico di lavoro di creazione di report, e a questo punto il thread di rollforward viene sbloccato, oppure è possibile sbloccare il thread di rollforward immediatamente eseguendo il comando KILL (Transact-SQL) sull'ID di sessione che causa il blocco.

Il thread di redo rimane indietro a causa di una contesa di risorse

Un carico elevato di reportistica sulla replica secondaria ha rallentato le prestazioni della replica secondaria e il thread di ripristino ha accumulato ritardo.

Spiegazione

Quando si applicano record di log alla replica secondaria, il thread di rollforward legge tali record dal disco dei log e quindi per ogni record di log accede alle pagine di dati corrispondenti per applicarlo. Se una pagina non è ancora nel pool di buffer, l'accesso a questa può avvenire tramite binding I/O (accesso al disco fisico). Se è presente un carico di lavoro di reportistica limitato dall'I/O, tale carico di lavoro compete per le risorse di I/O con il thread di redo e può rallentarlo. Questa situazione non solo impedisce ad altri carichi di lavoro di creazione di report di vedere i dati aggiornati, ma influisce anche sull'obiettivo RTO (Recovery Time Objective, obiettivo tempo di recupero).

Diagnosi e risoluzione

È possibile usare la query DMV seguente per vedere di quanto il thread di ripristino è in ritardo, misurando la differenza tra last_redone_lsn e last_received_lsn.

select recovery_lsn, truncation_lsn, last_hardened_lsn, last_received_lsn,   
   last_redone_lsn, last_redone_time  
from sys.dm_hadr_database_replica_states

Se il thread di ripristino è effettivamente in ritardo, è necessario indagare la causa principale del degrado delle prestazioni nella replica secondaria. Se è presente una contesa per le risorse I/O con il carico di lavoro di creazione di report, è possibile usare Resource Governor per controllare i cicli della CPU usati dal carico di lavoro di creazione di report e controllare indirettamente, in una certa misura, i cicli di I/O eseguiti. Ad esempio, se il carico di lavoro di reportistica consuma il 10% della CPU ma è vincolato dall'I/O, è possibile usare Resource Governor per limitare l'utilizzo delle risorse della CPU al 5% e rallentare il carico di lavoro di lettura, riducendo così al minimo l'impatto sull'I/O.

Contenuti correlati

  • Last updated on