Monitorare e risolvere i problemi relativi al ripristino accelerato del database

  • Articolo

Si applica a:SQL Server 2019 (15.x) e versioni successive database SQL di Azure Istanza gestita di SQL di Azuredatabase SQL in Microsoft Fabric

Questo articolo illustra come monitorare, diagnosticare e risolvere i problemi relativi a ripristino accelerato del database (ADR) in SQL Server 2019 (15.x) e versioni successive, Istanza gestita di SQL di Azure, database SQL di Azure e database SQL in Microsoft Fabric.

Esaminare le dimensioni del PVS

Usare la DMV sys.dm_tran_persistent_version_store_stats per identificare se le dimensioni dell'archivio versioni permanenti (PVS) sono maggiori del previsto.

La query di diagnostica di esempio seguente mostra le informazioni sulle dimensioni pvS correnti, i processi di pulizia e altri dettagli in tutti i database in cui le dimensioni PVS sono maggiori di zero:

SELECT pvss.database_id,
       DB_NAME(pvss.database_id) AS database_name,
       pvss.persistent_version_store_size_kb / 1024. / 1024 AS persistent_version_store_size_gb,
       100 * pvss.persistent_version_store_size_kb / df.total_db_size_kb AS pvs_percent_of_database_size,
       df.total_db_size_kb / 1024. / 1024 AS total_db_size_gb,
       pvss.online_index_version_store_size_kb / 1024. / 1024 AS online_index_version_store_size_gb,
       pvss.current_aborted_transaction_count,
       pvss.aborted_version_cleaner_start_time,
       pvss.aborted_version_cleaner_end_time,
       pvss.oldest_aborted_transaction_id,
       pvss.oldest_active_transaction_id,
       dt.database_transaction_begin_time AS oldest_transaction_begin_time,
       asdt.session_id AS active_transaction_session_id,
       asdt.elapsed_time_seconds AS active_transaction_elapsed_time_seconds,
       pvss.pvs_off_row_page_skipped_low_water_mark,
       pvss.pvs_off_row_page_skipped_min_useful_xts,
       pvss.pvs_off_row_page_skipped_oldest_aborted_xdesid
FROM sys.dm_tran_persistent_version_store_stats AS pvss
CROSS APPLY (
            SELECT SUM(size * 8.) AS total_db_size_kb
            FROM sys.master_files AS mf
            WHERE mf.database_id = pvss.database_id
                  AND
                  mf.state = 0
                  AND
                  mf.type = 0
            ) AS df
LEFT JOIN sys.dm_tran_database_transactions AS dt
ON pvss.oldest_active_transaction_id = dt.transaction_id
   AND
   pvss.database_id = dt.database_id
LEFT JOIN sys.dm_tran_active_snapshot_database_transactions AS asdt
ON pvss.min_transaction_timestamp = asdt.transaction_sequence_num
   OR
   pvss.online_index_min_transaction_timestamp = asdt.transaction_sequence_num
WHERE pvss.persistent_version_store_size_kb > 0
ORDER BY persistent_version_store_size_kb DESC;

Controllare la colonna pvs_percent_of_database_size per visualizzare le dimensioni del pvS rispetto alle dimensioni totali del database. Si noti qualsiasi differenza tra le dimensioni tipiche del pvS e le linee di base visualizzate durante i periodi tipici dell'attività dell'applicazione. PVS è considerato di grandi dimensioni se è significativamente maggiore del valore di riferimento o se è vicina a 50% delle dimensioni del database.

Se la dimensione del PVS non diminuisce, seguire i seguenti passaggi di risoluzione dei problemi per trovare e risolvere la causa della grande dimensione di PVS.

Suggerimento

Le colonne indicate nei passaggi di risoluzione dei problemi seguenti fanno riferimento alle colonne nel set di risultati della query di diagnostica in questa sezione.

Le grandi dimensioni PVS possono essere causate da uno dei motivi seguenti:

Verificare la presenza di transazioni attive a esecuzione prolungata

Le transazioni attive con esecuzione prolungata possono impedire la pulizia PVS nei database con ADR abilitato. Controllare l'ora di inizio della transazione attiva meno recente usando la colonna oldest_transaction_begin_time. Per trovare transazioni con esecuzione prolungata, usare la query di esempio seguente. È possibile impostare soglie per la durata della transazione e la quantità di log delle transazioni generate:

DECLARE @LongTxThreshold int = 900;  /* The number of seconds to use as a duration threshold for long-running transactions */
DECLARE @LongTransactionLogBytes bigint = 1073741824; /* The number of bytes to use as the generated log threshold for long-running transactions */

SELECT  dbtr.database_id,
        DB_NAME(dbtr.database_id) AS database_name,
        st.session_id,
        st.transaction_id,
        atr.name,
        sess.login_time,
        dbtr.database_transaction_log_bytes_used,
        CASE WHEN GETDATE() >= DATEADD(second, @LongTxThreshold, tr.transaction_begin_time) THEN 'DurationThresholdExceeded'
             WHEN dbtr.database_transaction_log_bytes_used >= @LongTransactionLogBytes THEN 'LogThresholdExceeded'
             ELSE 'Unknown'
        END
        AS reason
FROM sys.dm_tran_active_transactions AS tr
INNER JOIN sys.dm_tran_session_transactions AS st
ON tr.transaction_id = st.transaction_id
INNER JOIN sys.dm_exec_sessions AS sess
ON st.session_id = sess.session_id
INNER JOIN sys.dm_tran_database_transactions AS dbtr
ON tr.transaction_id = dbtr.transaction_id
INNER JOIN sys.dm_tran_active_transactions AS atr
ON atr.transaction_id = st.transaction_id
WHERE GETDATE() >= DATEADD(second, @LongTxThreshold, tr.transaction_begin_time)
      OR
      dbtr.database_transaction_log_bytes_used >= @LongTransactionLogBytes;

Con le sessioni identificate, prendere in considerazione l'eliminazione della sessione, se consentito. Esaminare l'applicazione per determinare la natura delle transazioni problematiche per evitare il problema in futuro.

Per ulteriori informazioni sulla risoluzione dei problemi relativi alle query a esecuzione prolungata, vedere:

Verificare la presenza di analisi snapshot attive a esecuzione prolungata

Le scansioni snapshot attive a lunga esecuzione possono impedire la pulizia PVS nei database con ADR abilitato. Istruzioni che usano l'isolamento di snapshot (READ COMMITTED) (RCSI) o i livelli di isolamento SNAPSHOT ricevono timestamp a livello di istanza. Una scansione snapshot utilizza il timestamp per determinare la visibilità delle righe di versione per la transazione RCSI o SNAPSHOT. Ogni istruzione che usa RCSI ha il proprio timestamp, mentre l'isolamento SNAPSHOT ha un timestamp a livello di transazione.

Questi timestamp delle transazioni a livello di istanza vengono usati anche nelle transazioni a database singolo, perché qualsiasi transazione potrebbe essere promossa a una transazione tra database. Le scansioni snapshot possono quindi impedire la pulizia PVS in qualsiasi database sulla stessa istanza del motore di database. Analogamente, anche quando ADR non è abilitato, le scansioni snapshot possono impedire la pulizia dell'archivio versioni in tempdb. Di conseguenza, i PVS possono aumentare di dimensioni quando sono presenti transazioni a lunga esecuzione che usano SNAPSHOT o RCSI.

La colonna pvs_off_row_page_skipped_min_useful_xts mostra il numero di pagine ignorate durante la pulizia a causa di un'analisi di snapshot lunga. Se questa colonna mostra un valore elevato, significa che una lunga scansione istantanea impedisce la pulizia di PVS.

Usare il seguente esempio di query per trovare le sessioni con SNAPSHOT a lunga durata o la transazione RCSI.

SELECT sdt.transaction_id,
       sdt.transaction_sequence_num,
       s.database_id,
       s.session_id,
       s.login_time,
       GETDATE() AS query_time,
       s.host_name,
       s.program_name,
       s.login_name,
       s.last_request_start_time
FROM sys.dm_tran_active_snapshot_database_transactions AS sdt
INNER JOIN sys.dm_exec_sessions AS s
ON sdt.session_id = s.session_id;

Per evitare i ritardi nella pulizia di PVS:

  • Prendere in considerazione l'eliminazione della sessione di transazioni attiva prolungata che ritarda la pulizia di PVS, ove possibile.
  • Ottimizzare le query a esecuzione prolungata per ridurre la durata delle query.
  • Esaminare l'applicazione per determinare la natura dell'analisi snapshot attiva problematica. Si consideri un livello di isolamento diverso, ad esempio READ COMMITTED, anziché SNAPSHOT o RCSI per le query con esecuzione prolungata che ritardano la pulizia PVS. Questo problema si verifica più frequentemente con il livello di isolamento SNAPSHOT.
  • Nei pool elastici del database SQL di Azure prendere in considerazione lo spostamento di database con transazioni a esecuzione prolungata usando SNAPSHOT isolamento o RCSI dal pool elastico per evitare ritardi di pulizia PVS in altri database nello stesso pool.

Verificare la presenza di query con esecuzione prolungata nelle repliche secondarie

Se il database dispone di repliche secondarie, verificare se il segno minimo secondario sta avanzando.

Un valore elevato nella colonna pvs_off_row_page_skipped_low_water_mark potrebbe essere un'indicazione di un ritardo nella pulizia dovuto a una query con esecuzione prolungata in una replica secondaria. Oltre a ritardare la pulizia PVS, una query a esecuzione prolungata su una replica secondaria può anche ritardare la pulizia fantasma .

È possibile usare le query di esempio seguenti sulla replica primaria per verificare se le query a lunga durata nelle repliche secondarie potrebbero impedire la pulizia di PVS. Se un carico di lavoro di scrittura è in esecuzione nella replica primaria, ma il valore nella colonna low_water_mark_for_ghosts non viene aumentato da un'esecuzione della query di esempio alla successiva, la PVS e la pulizia dei record fantasma potrebbero essere ostacolate da una query a esecuzione prolungata in una replica secondaria.

SELECT database_id,
       DB_NAME(database_id) AS database_name,
       low_water_mark_for_ghosts,
       synchronization_state_desc,
       synchronization_health_desc,
       is_suspended,
       suspend_reason_desc,
       secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states
WHERE is_local = 1
      AND
      is_primary_replica = 1;

Per altre informazioni, vedere la descrizione della colonna low_water_mark_for_ghosts in sys.dm_hadr_database_replica_states.

Connettersi a ogni replica secondaria leggibile, trovare la sessione con la query a esecuzione prolungata e prendere in considerazione l'eliminazione della sessione, se consentita. Per ulteriori informazioni, consultare la sezione sulle query lente .

Verificare la presenza di un numero elevato di transazioni interrotte

Controllare le colonne aborted_version_cleaner_start_time e aborted_version_cleaner_end_time per verificare se l'ultima pulizia delle transazioni interrotta è stata completata. Il valore di oldest_aborted_transaction_id aumenta dopo il completamento della pulizia della transazione interrotta. Se il oldest_aborted_transaction_id è molto inferiore a oldest_active_transaction_ide il valore di current_abort_transaction_count è elevato, è probabile che una vecchia transazione interrotta impedisca la pulizia di PVS.

Per risolvere il ritardo di pulizia PVS a causa di un numero elevato di transazioni interrotte, considerare quanto segue:

  • Se si usa SQL Server 2022 (16.x), aumentare il valore della configurazione del server ADR Cleaner Thread Count. Per altre informazioni, vedere configurazione del server : ADR Cleaner Thread Count.
  • Se possibile, arrestare il carico di lavoro per consentire al pulitore di versioni di fare progressi.
  • Esaminare l'applicazione per identificare e risolvere il problema dell'elevato tasso di aborti delle transazioni. Gli interruzioni potrebbero provenire da una frequenza elevata di deadlock, chiavi duplicate, violazioni dei vincoli o timeout delle query.
  • Ottimizzare il carico di lavoro per ridurre i blocchi incompatibili con i blocchi a livello di oggetto o a livello di partizione IX richiesti dal pulitore PVS. Per altre informazioni, vedere Compatibilità delle serrature.
  • Se si utilizza SQL Server, disabilitare ADR come misura di sola emergenza per gestire le dimensioni PVS. Vedere Disable ADR.
  • Se si usa SQL Server e se il ripristino delle transazioni interrotte non è stato completato di recente con successo, controllare il log degli errori per individuare i messaggi che segnalano problemi VersionCleaner.
  • Se la dimensione PVS non è ridotta come previsto anche dopo il completamento di una pulizia, controllare la colonna pvs_off_row_page_skipped_oldest_aborted_xdesid. Valori elevati indicano che lo spazio viene ancora utilizzato dalle versioni di riga delle transazioni interrotte.

Controlla le dimensioni PVS

Se si dispone di un carico di lavoro con un volume elevato di istruzioni DML (INSERT, UPDATE, DELETE, MERGE), ad esempio OLTP ad alto volume e osservare che le dimensioni PVS sono elevate, potrebbe essere necessario aumentare il valore della configurazione del server ADR Cleaner Thread Count per mantenere sotto controllo le dimensioni PVS. Per altre informazioni, vedere configurazione di Server: ADR Cleaner Thread Count, disponibile a partire da SQL Server 2022 (16.x).

In SQL Server 2019 (15.x) o se l'aumento del valore della configurazione di ADR Cleaner Thread Count non consente di ridurre sufficientemente le dimensioni pvS, il carico di lavoro potrebbe richiedere un periodo di riposo/ripristino per il processo di pulizia PVS per recuperare spazio.

Per attivare manualmente il processo di pulizia PVS tra carichi di lavoro o durante le finestre di manutenzione, usare la stored procedure di sistema sys.sp_persistent_version_cleanup.

Per esempio:

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

Acquisire gli errori di pulizia

A partire da SQL Server 2022 (16.x), i messaggi di pulizia PVS rilevanti vengono registrati nel log degli errori. Le statistiche di pulizia vengono segnalate anche dall'evento esteso tx_mtvc2_sweep_stats.

Contenuto correlato