Risoluzione dei problemi relativi al ripristino accelerato del database

  • Articolo

Si applica a: SQL Server 2019 (15.x) Database SQL di Azure Istanza gestita di SQL di Azure

Questo articolo consente agli amministratori di diagnosticare i problemi relativi al ripristino accelerato del database in SQL Server 2019 (15.x) e versioni successive, Istanza gestita di SQL di Azure e database SQL di Azure.

Esaminare l'archivio versioni permanenti (PVS)

Sfrutta la DMV sys.dm_tran_persistent_version_store_stats per identificare se le dimensioni del PSV di ripristino accelerato del database (ADR) stanno crescendo più del previsto e quindi per determinare quale fattore impedisce la pulizia dell'archivio versioni permanenti (PVS).

Nello script di esempio seguente è inclusa la colonna sys.dm_tran_persistent_version_store_stats.pvs_off_row_page_skipped_oldest_aborted_xdesid, che è stata aggiunta in SQL Server 2022 (16.x) e contiene il numero di pagine ignorate per il recupero a causa delle transazioni interrotte meno recenti. Se la pulizia della versione è lenta o invalidata, questo rifletterà il numero di pagine da conservare per le transazioni interrotte.

La query di esempio mostra tutte le informazioni sui processi di pulizia e mostra la dimensione attuale del PVS, la transazione meno recente interrotta e altri dettagli:

SELECT
 db_name(pvss.database_id) AS DBName,
 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_pct_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,
 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 -- SQL Server 2022 only
FROM sys.dm_tran_persistent_version_store_stats AS pvss
CROSS APPLY (SELECT SUM(size*8.) AS total_db_size_kb FROM sys.database_files WHERE [state] = 0 and [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.database_id = DB_ID();

  1. Controlla le dimensioni di pvs_pct_of_database_size, nota eventuali differenze rispetto alle previsioni e confrontale con i valori di riferimento durante altri periodi di attività dell'applicazione. L'archivio versioni permanente è considerato grande se le sue dimensioni sono significativamente maggiori rispetto a quelle di base o se è prossimo al 50% delle dimensioni del database. Utilizza i seguenti passaggi come aiuto per la risoluzione dei problemi di un PVS di grandi dimensioni.

  2. Le transazioni attive e a esecuzione prolungata in qualsiasi database in cui è abilitata la replica automatica del database possono impedire la pulizia del PVS. Recuperare oldest_active_transaction_id e verificare se la transazione è stata attiva per un tempo lungo eseguendo una query su sys.dm_tran_database_transactions in base all'ID della transazione. Verificare la presenza di transazioni attive e a esecuzione prolungata con una query simile all'esempio seguente, che dichiara le variabili per impostare le soglie per la durata o l'importo del log:

    DECLARE @longTxThreshold int = 1800; --number of seconds to use as a duration threshold for long-running transactions
DECLARE @longTransactionLogBytes bigint = 2147483648; --number of bytes to use as a log amount threshold for long-running transactions

SELECT
    dbtr.database_id, 
    transess.session_id,  
    transess.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 transess on tr.transaction_id = transess.transaction_id  
    INNER JOIN sys.dm_exec_sessions AS sess on transess.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 = transess.transaction_id 
WHERE transess.session_id <> @@spid AND 
    ( getdate() >= dateadd(second, @longTxThreshold, tr.transaction_begin_time) OR
      dbtr.database_transaction_log_bytes_used >= @longTransactionLogBytes );

    Dopo aver identificato le sessioni, prendere in considerazione l'eliminazione della sessione, se consentito. Esaminare anche l'applicazione per determinare la natura delle transazioni attive problematiche.

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

  1. La pulizia della versione persistente potrebbe essere bloccata a causa di una lunga scansione attiva snapshot. Le istruzioni che usano l'isolamento dello snapshot read committed (RCSI) o i livelli di isolamento SNAPSHOT ricevono timestamp a livello di istanza. Un'analisi snapshot usa il timestamp per decidere la visibilità delle righe per la transazione RCSI o SNAPSHOT nel PVS in cui è abilitato il ripristino accelerato del database. Ogni istruzione che utilizza RCSI ha un proprio timestamp, mentre l'isolamento SNAPSHOT ha un timestamp a livello di transazione. Questi timestamp delle transazioni a livello di istanza vengono utilizzati anche nelle transazioni su un solo database, perché la transazione può essere promossa a transazione su più database. Le scansioni istantanee possono quindi impedire la pulizia dei record nel PVS del ripristino accelerato del database o, quando questo non è presente, nell'archivio delle versioni tempdb. Pertanto, a causa di questo rilevamento della versione, le transazioni in esecuzione prolungata che utilizzano SNAPSHOT o RCSI possono far ritardare la pulizia del database dell'istanza da parte del PVS del ripristino accelerato del database, causando la crescita delle dimensioni di quest'ultimo.

    Nella query originale di risoluzione dei problemi nella parte superiore di questo articolo, il valore pvs_off_row_page_skipped_min_useful_xts mostra il numero di pagine ignorate per il recupero a causa di un'analisi snapshot lunga. Se pvs_off_row_page_skipped_min_useful_xts mostra un valore più grande del normale, significa che è presente un'analisi snapshot lunga che impedisce la pulizia di PVS.

    Questa query di esempio può essere usata per decidere quale è la sessione problematica:

    SELECT 
    snap.transaction_id, snap.transaction_sequence_num, session.session_id, session.login_time, 
    GETUTCDATE() as [now], session.host_name, session.program_name, session.login_name, session.last_request_start_time
FROM sys.dm_tran_active_snapshot_database_transactions AS snap
INNER JOIN sys.dm_exec_sessions AS session ON snap.session_id = session.session_id  
ORDER BY snap.transaction_sequence_num asc;

    Per evitare ritardi nella pulizia di PVS:

    1. Prendere in considerazione l'eliminazione della sessione di transazioni attiva prolungata che ritarda la pulizia di PVS, ove possibile. Le transazioni con esecuzione prolungata in qualsiasi database in cui è abilitato il ripristino accelerato del database possono ritardare la pulizia di PVS del ripristino accelerato del database.
    2. Ottimizzare le query a esecuzione prolungata per ridurre la durata delle query e i blocchi necessari. Per altre informazioni e indicazioni, vedere Comprendere e risolvere i blocchi in SQL Server o Comprendere e risolvere i problemi di blocco del database SQL di Azure.
    3. Esaminare l'applicazione per determinare la natura dell'analisi snapshot attiva problematica. Prendere in considerazione un livello di isolamento diverso, come READ COMMITTED, invece di SNAPSHOT o READ COMMITTED SNAPSHOT per le query a esecuzione prolungata che ritardano la pulizia dell'ADR PVS. Questo problema si verifica più frequentemente con il livello di isolamento SNAPSHOT.
    4. Questo problema può verificarsi in SQL Server, Istanza gestita di SQL di Azure e nei pool elastici dei database SQL di Azure, ma non nei database SQL di Azure singleton. Nei pool elastici dei database SQL di Azure, prendi in considerazione l'idea di spostare dal pool elastico i database che hanno query a esecuzione prolungata che utilizzano i livelli di isolamento READ COMMIT SNAPSHOT o SNAPSHOT.

  2. Quando le dimensioni del PVS crescono a causa di transazioni in esecuzione prolungata sulle repliche primarie o secondarie, analizza le query a esecuzione prolungata e individua il collo di bottiglia. La DMV sys.dm_tran_aborted_transactions mostra tutte le transazioni interrotte. Per altre informazioni, consultare sys.dm_tran_aborted_transactions (Transact-SQL). La colonna nest_aborted indica che è stato eseguito il commit della transazione, ma sono presenti parti interrotte (punti di salvataggio o transazioni annidate) che possono bloccare il processo di pulizia PVS.

  3. Se il database fa parte di un gruppo di disponibilità, controllare secondary_low_water_mark. Questo valore corrisponde a quello di low_water_mark_for_ghosts restituito da sys.dm_hadr_database_replica_states. Eseguire una query su sys.dm_hadr_database_replica_states per verificare se una delle repliche conserva questo valore perché anche in questo caso viene impedita la pulizia dell'archivio versioni permanente. La pulizia della versione viene bloccata a causa di query di lettura su repliche secondarie leggibili. Sia SQL Server locale che il database SQL di Azure supportano repliche secondarie leggibili. Nella DMV sys.dm_tran_persistent_version_store_stats, pvs_off_row_page_skipped_low_water_mark può anche fornire indicazioni di un ritardo di replica secondaria. Per altre informazioni, vedere sys.dm_tran_persistent_version_store_stats.

    La soluzione è uguale a quella dell'analisi snapshot. Passare ai database secondari, individuare la sessione che esegue la query prolungata e prendere in considerazione l'eliminazione della sessione, se consentita. Da notare che il blocco secondario non influisce solo sulla pulizia della versione del ripristino accelerato del database, ma può anche impedire la pulizia dei record fantasma.

  4. Controllare min_transaction_timestamp (o online_index_min_transaction_timestamp se l'archivio versioni permanente online impedisce l'operazione) e quindi controllare in sys.dm_tran_active_snapshot_database_transactions la colonna transaction_sequence_num per individuare la sessione con la transazione snapshot precedente che impedisce la pulizia dell'archivio versioni permanente.

  5. Se non si applica alcuna delle opzioni precedenti, significa che la pulizia viene impedita da transazioni interrotte. Controllare gli orari di aborted_version_cleaner_last_start_time e aborted_version_cleaner_last_end_time più recenti per verificare se la pulizia della transazione interrotta è stata completata. Il valore di oldest_aborted_transaction_id aumenta dopo il completamento della pulizia della transazione interrotta. Se è oldest_aborted_transaction_id molto inferiore a oldest_active_transaction_id e current_abort_transaction_count ha un valore maggiore, esiste una transazione interrotta precedente che impedisce la pulizia PVS. Per risolvere il problema:

    • Ove possibile, interrompere il carico di lavoro per consentire alla versione di pulizia di progredire.
    • Ottimizzare il carico di lavoro per ridurre l'utilizzo dei blocchi a livello di oggetto.
    • Esaminare l'applicazione per visualizzare eventuali problemi di interruzione delle transazioni elevati. Deadlock, chiavi duplicate e altre violazioni dei vincoli possono innescare un'alta percentuale di interruzioni.
    • Se si tratta di SQL Server, disabilitare il ripristino accelerato del database come operazione di sola emergenza per controllare le dimensioni del PVS e il numero di transazioni interrotte. Vedere Disabilitare la funzionalità di ripristino accelerato del database.

  6. Se la transazione interrotta non è stata completata di recente, controllare se nel log degli errori sono presenti messaggi che segnalano problemi di tipo VersionCleaner.

  7. Monitorare il log degli errori di SQL Server per trovare le voci 'PreallocatePVS'. Se sono presenti voci "PreallocatePVS", significa che potrebbe essere necessario aumentare la capacità dell'ADR di preallocare le pagine per le attività in background, in quanto le prestazioni possono migliorare quando il thread in background dell'ADR prealloca un numero sufficiente di pagine e la percentuale di allocazioni PVS in primo piano è prossima a 0. È possibile utilizzare sp_configure 'ADR Preallocation Factor' per aumentare questo importo. Per altre informazioni, vedere Opzione di configurazione del server del fattore di preallocazione ADR.

Avviare manualmente il processo di pulizia PVS

L'ADR non è consigliato per gli ambienti di database con un elevato numero di transazioni di aggiornamento/eliminazione, come ad esempio gli OLTP ad alto volume, senza un periodo di riposo/recupero per il processo di pulizia del 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.

EXEC sys.sp_persistent_version_cleanup [database_name];

ad esempio:

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

Acquisire gli errori di pulizia

A partire da SQL Server 2022 (16.x), SQL Server registra il comportamento di pulizia dell'ADR PVS nel log degli errori di SQL Server. In genere questo comporta la registrazione di un nuovo evento di log ogni 10 minuti.

Vedi anche

Passaggi successivi