Problembehandlung bei beschleunigter Datenbankwiederherstellung
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_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();
Ü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.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 abfragensys.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:
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. Wennpvs_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:
- 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.
- 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.
- Ü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.
- 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.
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 Spaltenest_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.Wenn die Datenbank Teil einer Verfügbarkeitsgruppe ist, überprüfen Sie die
secondary_low_water_mark
. Diese ist identisch mit demlow_water_mark_for_ghosts
, das vonsys.dm_hadr_database_replica_states
gemeldet wird. Führen Sie diesys.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 dersys.dm_tran_persistent_version_store_stats
-DMV kann auchpvs_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.
Überprüfen Sie
min_transaction_timestamp
(oderonline_index_min_transaction_timestamp
, wenn der Online-PVS den Vorgang aufhält), und überprüfen Sie auf dieser Grundlagesys.dm_tran_active_snapshot_database_transactions
für die Spaltetransaction_sequence_num
, um die Sitzung mit der alten Momentaufnahmetransaktion zu suchen, die das PVS-Cleanup aufhält.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_time
aborted_version_cleaner_last_end_time
ob die abgebrochene Transaktionsbereinigung abgeschlossen ist. Dieoldest_aborted_transaction_id
sollte nach Durchführen des Cleanups abgebrochener Transaktionen nach oben verschoben werden. Wenn diesoldest_aborted_transaction_id
viel kleiner alsoldest_active_transaction_id
ist undcurrent_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.
Wenn die abgebrochene Transaktion zuletzt nicht erfolgreich abgeschlossen wurde, überprüfen Sie das Fehlerprotokoll auf Meldungen, die
VersionCleaner
-Probleme berichten.Ü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
- sys.sp_persistent_version_cleanup
- sys.dm_tran_persistent_version_store_stats
- sys.dm_tran_aborted_transactions
Nächste Schritte
Feedback
https://aka.ms/ContentUserFeedback.
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unterFeedback senden und anzeigen für