Problembehandlung bei beschleunigter Datenbankwiederherstellung

  • Artikel

Gilt für: SQL Server 2019 (15.x) Azure SQL-DatenbankAzure 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öher, azure SQL Managed Instance und Azure SQL Database.

Untersuchen des persistenten Versionsspeichers (PVS)

Nutzen Sie die sys.dm_tran_persistent_version_store_stats DMV, um zu ermitteln, ob die Größe der beschleunigten Datenbankwiederherstellung (ADR) PVS größer als erwartet wird, und ermitteln Sie dann, 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_xdesidenthalten, 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, lange ausgeführte Transaktionen in jeder Datenbank, in der ADR aktiviert ist, können die Bereinigung der PVS verhindern. Rufen Sie diese Transaktion ab, und überprüfen Sie oldest_active_transaction_id , ob diese Transaktion lange aktiv war, indem Sie basierend auf der Transaktions-ID abfragen sys.dm_tran_database_transactions . Ü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 von lang ausgeführten Abfragen finden Sie unter:

  1. Die persistente Versionsbereinigung kann möglicherweise aufgrund lange aktiver Momentaufnahmescans verzögert werden. Anweisungen mit lesesicherer Snapshotisolation (RCSI) oder SNAPSHOT-Isolationsstufen erhalten Zeitstempel auf Instanzebene. Bei einer Momentaufnahmeüberprüfung wird der Zeitstempel verwendet, um die Zeilensichtbarkeit für die RCSI- oder SNAPSHOT-Transaktion in der PVS zu bestimmen, bei der die beschleunigte Datenbankwiederherstellung aktiviert ist. Jede Anweisung, die RCSI verwendet, verfügt über einen eigenen Zeitstempel, während die SNAPSHOT-Isolation einen Zeitstempel auf Transaktionsebene aufweist. Diese Transaktionszeitstempel auf Instanzebene werden auch in Einzeldatenbanktransaktionen verwendet, da die Transaktion möglicherweise zu einer datenbankübergreifenden Transaktion heraufgestuft wird. Snapshot-Scans können daher die Bereinigung von Datensätzen im ADR PVS verhindern oder wenn ADR nicht vorhanden ist, im tempdb Versionsspeicher. Daher kann aufgrund dieser Versionsnachverfolgung lange ausgeführte Transaktionen mit SNAPSHOT oder RCSI dazu führen, dass ADR PVS die Bereinigung in der Datenbank in der Instanz verzögert, wodurch die ADR PVS größer wird.

    In der ursprünglichen Problembehandlungsabfrage oben in diesem Artikel zeigt der pvs_off_row_page_skipped_min_useful_xts Wert die Anzahl der Seiten an, die aufgrund eines langen Snapshotscans für die Zurü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. Erwägen Sie, die lange aktive Transaktionssitzung zu töten, die die PVS-Bereinigung nach Möglichkeit verzögert. Lange ausgeführte Transaktionen in jeder Datenbank, in der ADR aktiviert ist, können die ADR PVS-Bereinigung verzögern.
    2. Optimieren Sie lange ausgeführte Abfragen, um die Abfragedauer und sperrungen zu reduzieren. Weitere Informationen und Anleitungen finden Sie unter Verstehen und Beheben von Blockierungsproblemen in SQL Server oder Verstehen und Beheben von Blockierungsproblemen in der Azure SQL-Datenbank.
    3. Überprüfen Sie die Anwendung, um die Art des problematischen aktiven Snapshotscans zu ermitteln. Betrachten Sie eine andere Isolationsstufe, z. B. READ COMMIT, anstelle von SNAPSHOT oder READ COMMIT SNAPSHOT für lange ausgeführte Abfragen, die die ADR PVS-Bereinigung verzögern. Dieses Problem tritt häufiger mit SNAPSHOT-Isolationsstufe auf.
    4. Dieses Problem kann in SQL Server-, Azure SQL Managed Instance- und elastischen Pools der Azure SQL-Datenbank auftreten, aber nicht in Singleton-Azure SQL-Datenbanken. Ziehen Sie in Azure SQL-Datenbank elastische Pools in Betracht, Datenbanken aus dem elastischen Pool zu verschieben, die über lange ausgeführte Abfragen mit READ COMMIT SNAPSHOT- oder SNAPSHOT-Isolationsebenen 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 lokale SQL Server- als auch Azure SQL DB unterstützen lesbare Secondärdateien. 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 Secondaries, suchen Sie die Sitzung, die die lange Abfrage ausgibt, und erwägen Sie, die Sitzung zu töten, 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 das letzte Mal, aborted_version_cleaner_last_start_timeaborted_version_cleaner_last_end_time ob die abgebrochene Transaktionsbereinigung abgeschlossen ist. Die oldest_aborted_transaction_id sollte nach Durchführen des Cleanups abgebrochener Transaktionen nach oben verschoben werden. Wenn dies oldest_aborted_transaction_id viel kleiner als oldest_active_transaction_idist und current_abort_transaction_count einen größeren Wert hat, gibt es eine alte abgebrochene Transaktion, die die PVS-Bereinigung verhindert. 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];

Ein auf ein Objekt angewendeter

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

Erfassen von Bereinigungsfehlern

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

Siehe auch

Nächste Schritte