Problembehandlung bei beschleunigter Datenbankwiederherstellung

  • Artikel

Gilt für: SQL Server 2019 (15.x) Azure SQL-Datenbank Azure SQL Managed Instance

Dieser Artikel hilft Administratoren bei der Diagnose von Problemen mit der beschleunigten Datenbankwiederherstellung (ADR) in SQL Server 2019 (15.x) und höheren Versionen, Azure SQL Managed Instance und Azure SQL-Datenbank.

Untersuchen des persistenten Versionsspeichers (PVS)

Verwenden Sie die sys.dm_tran_persistent_version_store_stats-DMV, um zu ermitteln, ob die Größe des PVS der beschleunigte Datenbankwiederherstellung (ADR) größer als erwartet wird, und um dann zu bestimmen, welcher Faktor die Bereinigung des persistenten Versionsspeichers (PVS) verhindert.

Im folgenden Beispielskript ist die Spalte sys.dm_tran_persistent_version_store_stats.pvs_off_row_page_skipped_oldest_aborted_xdesid enthalten, die in SQL Server 2022 (16.x) hinzugefügt wurde und die Anzahl der Seiten enthält, die aufgrund der ältesten abgebrochenen Transaktionen für die Rückforderung übersprungen wurden. Wenn die Versionsbereinigung langsam oder ungültig ist, spiegelt dies wider, wie viele Seiten für abgebrochene Transaktionen aufbewahrt werden müssen.

Die Beispielabfrage zeigt alle Informationen zu den Bereinigungsprozessen sowie die aktuelle PVS-Größe, die älteste abgebrochene Transaktion und weitere Details:

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. Überprüfen Sie die pvs_pct_of_database_size-Größe, und beachten Sie alle Unterschiede zu den typischen Werten im Vergleich mit Baselines in anderen Zeiträumen der Anwendungsaktivität. Der PVS gilt als groß, wenn er deutlich größer als die Baseline ist oder annähernd 50 % der Größe der Datenbank entspricht. Verwenden Sie die folgenden Schritte als Problembehandlungshilfe für einen großen PVS.

  2. Aktive zeitintensive Transaktionen in einer Datenbank, in der ADR aktiviert ist, können die Bereinigung des PVS verhindern. Rufen Sie oldest_active_transaction_id ab, und überprüfen Sie, ob diese Transaktion für einen langen Zeitraum aktiv war, indem Sie eine sys.dm_tran_database_transactions-Abfrage basierend auf der Transaktions-ID durchführen. Überprüfen Sie mit einer Abfrage wie im folgenden Beispiel, die Variablen deklariert, um Schwellenwerte für die Dauer oder den Protokollumfang zu setzen, ob lang ausgeführte, aktive Transaktionen vorliegen:

    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 );

    Wenn die Sitzung(en) identifiziert wurden, erwägen Sie, die Sitzung zu beenden, falls zulässig. Überprüfen Sie außerdem die Anwendung, um die Art der problematischen aktiven Transaktion(en) zu ermitteln.

    Weitere Informationen zur Problembehandlung bei zeitintensiven Abfragen finden Sie unter:

  1. Die persistente Versionsbereinigung kann möglicherweise aufgrund lange aktiver Momentaufnahmescans verzögert werden. Anweisungen mit Read-Committed-Momentaufnahmeisolation (RCSI) oder SNAPSHOT-Isolationsstufen werden auf Instanzebene mit Zeitstempeln versehen. Bei einem Momentaufnahmescan wird anhand des Zeitstempels über die Zeilensichtbarkeit für die RCSI- oder SNAPSHOT-Transaktion im PVS entschieden, wenn die beschleunigte Datenbankwiederherstellung aktiviert ist. Jede Anweisung mit RCSI verfügt über einen eigenen Zeitstempel, während die SNAPSHOT-Isolation einen Zeitstempel auf Transaktionsebene hat. Diese Transaktionszeitstempel auf Instanzebene werden auch in Einzeldatenbanktransaktionen verwendet, da die Transaktion gegebenenfalls zu einer datenbankübergreifenden Transaktion hochgestuft werden kann. Momentaufnahmescans können daher die Bereinigung von Datensätzen im ADR PVS oder, wenn ADR nicht vorhanden ist, im tempdb-Versionsspeicher verhindern. Daher können aufgrund dieser Versionsverfolgung zeitintensive Transaktionen mit SNAPSHOT oder RCSI dazu führen, dass ADR PVS die Bereinigung in der Datenbank in der Instanz verzögert, wodurch der ADR PVS an Größe zunimmt.

    In der ursprünglichen Problembehandlungsabfrage weiter oben in diesem Artikel zeigt der Wert pvs_off_row_page_skipped_min_useful_xts die Anzahl der Seiten an, die aufgrund eines langen Momentaufnahmescans für die Rückforderung übersprungen wurden. Wenn pvs_off_row_page_skipped_min_useful_xts einen größeren Wert als normal zeigt, bedeutet dies, dass ein langer Momentaufnahmescan die PVS-Bereinigung verhindert.

    Diese Beispielabfrage kann verwendet werden, um zu entscheiden, welche Sitzung problematisch ist:

    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;

    So verhindern Sie Verzögerungen bei der PVS-Bereinigung:

    1. Die für lange Zeit aktive Transaktionssitzung, die die PVS-Bereinigung verzögert, sollte nach Möglichkeit beendet werden. Zeitintensive Transaktionen in einer Datenbank, in der ADR aktiviert ist, können die ADR-PVS-Bereinigung verzögern.
    2. Optimieren Sie zeitintensive Abfragen, um die Abfragedauer und die erforderlichen Blockierungen zu reduzieren. Weitere Informationen und Anleitungen finden Sie unter Verstehen und Beheben von Problemen mit Blockierungen in SQL Server oder Verstehen und Beheben von Problemen durch Blockierungen in Azure SQL-Datenbank.
    3. Überprüfen Sie die Anwendung, um die Art der problematischen aktiven Momentaufnahmescans zu ermitteln. Ziehen Sie für zeitintensive Abfragen, die die ADR-PVS-Bereinigung verzögern, eine andere Isolationsstufe in Erwägung, wie z. B. READ COMMITTED, anstelle von SNAPSHOT oder READ COMMITTED SNAPSHOT. Dieses Problem tritt mit der SNAPSHOT-Isolationsstufe häufiger auf.
    4. Dieses Problem kann in SQL Server, Azure SQL Managed Instance und Pools für elastische Datenbanken von Azure SQL-Datenbank auftreten, aber nicht in Singletons in Azure SQL-Datenbank. In Pools für elastische Datenbanken in Azure SQL-Datenbank sollten Sie Datenbanken aus dem Pool herausnehmen, die über zeitintensive Abfragen mit den Isolationsstufen READ COMMIT SNAPSHOT oder SNAPSHOT verfügen.

  2. Wenn die PVS-Größe aufgrund lang ausgeführter Transaktionen auf primären oder sekundären Replikaten wächst, untersuchen Sie die Abfragen mit langer Ausführung und beheben Sie den Engpass. Die sys.dm_tran_aborted_transactions-DMV zeigt alle abgebrochenen Transaktionen an. Weitere Informationen finden Sie unter sys.dm_tran_aborted_transactions (Transact-SQL). Die Spalte nest_aborted zeigt an, dass ein Commit der Transaktion durchgeführt wurde, aber einige Teile abgebrochen wurden (Sicherungspunkte oder geschachtelte Transaktionen), die den PVS-Bereinigungsprozess blockieren können.

  3. Wenn die Datenbank Teil einer Verfügbarkeitsgruppe ist, überprüfen Sie die secondary_low_water_mark. Diese ist identisch mit dem low_water_mark_for_ghosts, das von sys.dm_hadr_database_replica_states gemeldet wird. Führen Sie die sys.dm_hadr_database_replica_states-Abfrage durch, um festzustellen, ob eines der Replikate diesen Wert zurückhält, da dadurch auch das PVS-Cleanup verhindert wird. Die Versionsbereinigung wird aufgrund von Leseabfragen für lesbare sekundäre Replikate verzögert. Sowohl die lokale SQL Server-Instanz als auch Azure SQL DB unterstützt lesbare sekundäre Replikate. In der sys.dm_tran_persistent_version_store_stats-DMV kann auch pvs_off_row_page_skipped_low_water_mark auf eine Verzögerung des sekundären Replikats hinweisen. Weitere Informationen finden Sie unter sys.dm_tran_persistent_version_store_stats.

    Die Lösung ist mit der Verzögerung des Momentaufnahmescans identisch. Wechseln Sie zu den sekundären Replikaten, suchen Sie die Sitzung, aus der die lange Abfrage stammt, und erwägen Sie, die Sitzung zu beenden, falls zulässig. Beachten Sie, dass die sekundären Replikate nicht nur die ADR-Versionsbereinigung verzögern, sondern auch die Bereinigung von inaktiven Datensätzen verhindern können.

  4. Überprüfen Sie min_transaction_timestamp (oder online_index_min_transaction_timestamp, wenn der Online-PVS den Vorgang aufhält), und überprüfen Sie auf dieser Grundlage sys.dm_tran_active_snapshot_database_transactions für die Spalte transaction_sequence_num, um die Sitzung mit der alten Momentaufnahmetransaktion zu suchen, die das PVS-Cleanup aufhält.

  5. Wenn nichts davon zutrifft, bedeutet dies, dass das Cleanup von abgebrochenen Transaktionen aufgehalten wird. Überprüfen Sie ein letztes Mal aborted_version_cleaner_last_start_time und aborted_version_cleaner_last_end_time, um festzustellen, ob das Cleanup abgebrochener Transaktionen durchgeführt wurde. Die oldest_aborted_transaction_id sollte nach Durchführen des Cleanups abgebrochener Transaktionen nach oben verschoben werden. Wenn die oldest_aborted_transaction_id viel kleiner ist als oldest_active_transaction_id und current_abort_transaction_count einen höheren Wert hat, verhindert eine alte abgebrochene Transaktion die PVS-Bereinigung. Gehen Sie folgendermaßen vor:

    • Beenden Sie die Workload nach Möglichkeit, damit die Versionsbereinigung fortfahren kann.
    • Optimieren Sie die Workload, um die Verwendung von Sperren auf Objektebene zu reduzieren.
    • Überprüfen Sie die Anwendung auf Probleme mit einer hohen Zahl von Transaktionsabbrüchen. Deadlocks, doppelte Schlüssel und andere Einschränkungsverletzungen können eine hohe Abbruchrate auslösen.
    • Deaktivieren Sie unter SQL Server ADR als nur im Notfall auszuführenden Schritt, um sowohl die PVS-Größe als auch die Zahl der Transaktionsabbrüche zu steuern. Siehe Deaktivieren der ADR-Funktion.

  6. Wenn die abgebrochene Transaktion zuletzt nicht erfolgreich abgeschlossen wurde, überprüfen Sie das Fehlerprotokoll auf Meldungen, die VersionCleaner-Probleme berichten.

  7. Überwachen Sie das SQL Server-Fehlerprotokoll auf Einträge mit dem Wert „PreallocatePVS“. Wenn Einträge mit dem Wert „PreallocatePVS“ vorhanden sind, bedeutet dies möglicherweise, dass Sie die ADR-Fähigkeit erhöhen müssen, Seiten für Hintergrundaufgaben vorab zuzuweisen, da die Leistung verbessert werden kann, wenn der ADR-Hintergrundthread genügend Seiten vorab zuweist und der Prozentsatz der PVS-Zuordnungen im Vordergrund nahe 0 liegt. Sie können sp_configure 'ADR Preallocation Factor' verwenden, um diesen Betrag zu erhöhen. Weitere Informationen finden Sie unter Die Konfigurationsoption „ADR Preallocation Factor“.

Manuelles Starten des PVS-Bereinigungsprozesses

ADR wird nicht für Datenbankumgebungen mit einer hohen Transaktionsanzahl von Aktualisierungen/Löschungen wie z. B. OLTP mit hohem Volumen empfohlen, ohne dass dem PVS-Bereinigungsprozess ein Zeitraum der Ruhe/Wiederherstellung zur Verfügung steht, in dem der Speicherplatz freimachen kann.

Um den PVS-Bereinigungsprozess manuell zwischen Workloads oder während Wartungsfenstern zu aktivieren, verwenden Sie die gespeicherte Systemprozedur sys.sp_persistent_version_cleanup.

EXEC sys.sp_persistent_version_cleanup [database_name];

Beispiel:

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

Erfassen von Bereinigungsfehlern

Ab SQL Server 2022 (16.x) verzeichnet SQL Server das ADR-PVS-Bereinigungsverhalten im SQL Server-Fehlerprotokoll. Dies würde in der Regel zu einem neuen, alle 10 Minuten aufgezeichneten Protokollereignis führen.

Weitere Informationen

Nächste Schritte