Freigeben über

Überwachen und Beheben von Problemen mit beschleunigter Datenbankwiederherstellung

  • Artikel

Gilt für: SQL Server 2019 (15.x) und höhere Versionen Azure SQL-Datenbank Azure SQL verwaltete InstanzSQL-Datenbank in Microsoft Fabric

Dieser Artikel hilft Ihnen beim Überwachen, Diagnostizieren und Beheben von Problemen mit der beschleunigten Datenbankwiederherstellung (ADR) in SQL Server 2019 (15.x) und höher, Azure SQL Managed Instance, Azure SQL Database und der SQL-Datenbank in Microsoft Fabric.

Untersuchen der PVS-Größe

Verwenden Sie den sys.dm_tran_persistent_version_store_stats DMV, um zu ermitteln, ob die Größe des persistenten Versionsspeichers (PVS) größer als erwartet ist.

Die folgende Beispieldiagnoseabfrage zeigt die Informationen über die aktuelle PVS-Größe, die Bereinigungsprozesse und andere Details in allen Datenbanken an, in denen die PVS-Größe größer als Null ist:

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;

Überprüfen Sie die spalte pvs_percent_of_database_size, um die Größe der PVS relativ zur Gesamtdatenbankgröße anzuzeigen. Beachten Sie alle Unterschiede zwischen der typischen PVS-Größe und den Basislinien, die in typischen Zeiträumen der Anwendungsaktivität zu sehen sind. PVS wird als groß betrachtet, wenn es wesentlich größer als der Ausgangswert ist oder wenn es ungefähr 50% der Datenbankgröße beträgt.

Wenn die Größe von PVS nicht verringert wird, verwenden Sie die folgenden Schritte zur Problembehandlung, um den Grund für eine große PVS-Größe zu finden und zu beheben.

Tipp

Spalten, die in den folgenden Schritten zur Problembehandlung erwähnt werden, beziehen sich auf die Spalten im Resultset der Diagnoseabfrage in diesem Abschnitt.

Große PVS-Größe kann aus einem der folgenden Gründe verursacht werden:

Überprüfen auf lang andauernde aktive Transaktionen

Lang andauernde aktive Transaktionen können die PVS-Bereinigung in Datenbanken verhindern, für die ADR aktiviert ist. Überprüfen Sie die Startzeit der ältesten aktiven Transaktion mithilfe der spalte oldest_transaction_begin_time. Um lang andauernde Transaktionen zu finden, verwenden Sie die folgende Beispielabfrage. Sie können Schwellenwerte für die Transaktionsdauer und die Menge des generierten Transaktionsprotokolls festlegen:

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;

Wenn die Sitzungen identifiziert wurden, erwägen Sie, die Sitzung zu beenden, falls zulässig. Überprüfen Sie die Anwendung, um die Art der problematischen Transaktionen zu ermitteln, um das Problem in Zukunft zu vermeiden.

Weitere Informationen zur Problembehandlung von lang andauernden Abfragen finden Sie unter:

Überprüfen auf lang andauernde aktive Momenaufnahmescans

Lange laufende aktive Momentaufnahmescans können die PVS-Bereinigung in Datenbanken verhindern, für die ADR aktiviert ist. Anweisungen mit READ COMMITTED Momentaufnahmeisolation (RCSI) oder SNAPSHOTIsolationsstufen empfangen Zeitstempel auf der Instanzebene. Bei einem Momentaufnahmescan wird der Zeitstempel verwendet, um die Sichtbarkeit der Versionszeile für die RCSI- oder SNAPSHOT-Transaktion zu erreichen. Jede Anweisung mit RCSI verfügt über einen eigenen Zeitstempel, während die SNAPSHOT-Isolation einen Zeitstempel auf Transaktionsebene aufweist.

Diese Zeitstempel auf Instanzebene werden auch bei Einzeldatenbanktransaktionen verwendet, da jede Transaktion möglicherweise zu einer datenbankübergreifenden Transaktion heraufgestuft wird. Momentaufnahmescans können daher die PVS-Bereinigung in jeder Datenbank in derselben Datenbankmodulinstanz verhindern. Auch wenn ADR nicht aktiviert ist, können Momentaufnahmescans die Bereinigung des Versionsspeichers in tempdb verhindern. Daher kann PVS größer werden, wenn langfristige Transaktionen, die SNAPSHOT oder RCSI verwenden, vorhanden sind.

In der Spalte pvs_off_row_page_skipped_min_useful_xts wird die Anzahl der Seiten angezeigt, die bei der Bereinigung aufgrund eines langen Snapshotscans übersprungen wurden. Wenn diese Spalte einen großen Wert anzeigt, bedeutet dies, dass ein langer Momentaufnahmescan die PVS-Bereinigung verhindert.

Verwenden Sie die folgende Beispielabfrage, um die Sitzungen mit der langlaufenden SNAPSHOT- oder RCSI-Transaktion zu finden:

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;

So verhindern Sie PVS-Bereinigungsverzögerungen:

  • Die für lange Zeit aktive Transaktionssitzung, die die PVS-Bereinigung verzögert, sollte nach Möglichkeit beendet werden.
  • Optimieren Sie langlaufende Abfragen, um die Abfragedauer zu verringern.
  • Überprüfen Sie die Anwendung, um die Art der problematischen aktiven Momentaufnahmescans zu ermitteln. Ziehen Sie eine andere Isolationsstufe wie READ COMMITTEDanstelle von SNAPSHOT oder RCSI für lange ausgeführte Abfragen in Betracht, die die PVS-Bereinigung verzögern. Dieses Problem tritt häufiger mit der SNAPSHOT Isolationsstufe auf.
  • In Pools für elastische Azure SQL-Datenbanken sollten Sie erwägen, Datenbanken mit langlaufenden Transaktionen, die SNAPSHOT-Isolation oder RCSI verwenden, aus dem Pool für elastische Datenbanken zu verschieben, um Verzögerungen bei der PVS-Bereinigungs in anderen Datenbanken im selben Pool zu vermeiden.

Überprüfen auf lang ausgeführte Abfragen für sekundäre Replikate

Wenn die Datenbank über sekundäre Replikate verfügt, überprüfen Sie, ob der sekundäre niedrige Grenzwert sich weiterentwickelt.

Ein großer Wert in der spalte pvs_off_row_page_skipped_low_water_mark kann ein Hinweis auf eine Bereinigungsverzögerung aufgrund einer lang andauernden Abfrage für ein sekundäres Replikat sein. Neben der Bereinigung von PVS kann eine lange laufende Abfrage eines sekundären Replikats auch Cleanup inaktiver Datensätze halten.

Sie können die folgenden Beispielabfragen für das primäre Replikat verwenden, um zu ermitteln, ob lange ausgeführte Abfragen für sekundäre Replikate möglicherweise die PVS-Bereinigung verhindern. Wenn eine Schreibarbeitsauslastung auf dem primären Replikat ausgeführt wird, der Wert in der Spalte low_water_mark_for_ghosts jedoch von einer Ausführung der Beispielabfrage zur nächsten nicht erhöht wird, kann PVS oder Cleanup inaktiver Datensätze durch eine langlaufende Abfrage auf einem sekundären Replikat verzögert werden.

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;

Weitere Informationen finden Sie in der Beschreibung der Spalte low_water_mark_for_ghosts in sys.dm_hadr_database_replica_states.

Stellen Sie eine Verbindung zu jedem lesbaren sekundären Replikat her, suchen Sie die Sitzung mit der lang laufenden Abfrage, und erwägen Sie, die Sitzung wenn möglich zu beenden. Weitere Informationen finden Sie unter Finden von langsamen Abfragen.

Überprüfen auf eine große Anzahl abgebrochener Transaktionen

Überprüfen Sie die Spalten aborted_version_cleaner_start_time und aborted_version_cleaner_end_time, um festzustellen, ob die letzte abgebrochene Transaktionsbereinigung abgeschlossen wurde. Die oldest_aborted_transaction_id sollte nach Durchführen des Cleanups abgebrochener Transaktionen nach oben verschoben werden. Wenn oldest_aborted_transaction_id viel niedriger ist als oldest_active_transaction_id und der current_abort_transaction_count-Wert groß ist, gibt es wahrscheinlich eine alte abgebrochene Transaktion, die die PVS-Bereinigung verhindert.

Beachten Sie folgendes, um die PVS-Bereinigungsverzögerung aufgrund einer großen Anzahl abgebrochener Transaktionen zu lösen:

  • Wenn Sie SQL Server 2022 (16.x) verwenden, erhöhen Sie den Wert der ADR Cleaner Thread Count Serverkonfiguration. Weitere Informationen finden Sie unter Serverkonfiguration: ADR Bereinigungs-Thread-Anzahl.
  • Beenden Sie nach Möglichkeit die Workload, damit der Versionsbereiniger Fortschritte machen kann.
  • Überprüfen Sie die Anwendung, um das Problem mit der hohen Transaktionsabbruchrate zu identifizieren und zu beheben. Die Abbrüche können durch eine hohe Anzahl von Deadlocks, doppelten Schlüsseln, Einschränkungsverletzungen oder Timeouts bei Abfragen verursacht werden.
  • Optimieren Sie die Arbeitsauslastung, um Sperren zu reduzieren, die mit den IX-Sperren, die vom PVS-Cleaner auf Objekt- oder Partitionsebene benötigt werden, nicht kompatibel sind. Weitere Informationen finden Sie unter Sperrkompatibilität.
  • Wenn Sie SQL Server verwenden, deaktivieren Sie ADR als notfallgeschützten Schritt, um die PVS-Größe zu steuern. Siehe Deaktivieren von ADR.
  • Wenn SQL Server verwendet wird und die abgebrochene Transaktionsbereinigung nicht kürzlich erfolgreich abgeschlossen wurde, überprüfen Sie das Fehlerprotokoll auf Meldungen, die VersionCleaner Probleme melden.
  • Wenn die PVS-Größe nach Abschluss des Bereinigungsvorgangs nicht wie erwartet reduziert wird, überprüfen Sie die Spalte pvs_off_row_page_skipped_oldest_aborted_xdesid. Große Werte deuten darauf hin, dass der Speicherplatz weiterhin von Zeilenversionen aus abgebrochenen Transaktionen verwendet wird.

Steuern der PVS-Größe

Wenn Sie eine Workload mit einer hohen Menge von DML-Anweisungen (INSERT, UPDATE, DELETE, MERGE) haben, z. B. OLTP mit hohem Volumen, und beachten Sie, dass die PVS-Größe groß ist, müssen Sie möglicherweise den Wert der ADR Cleaner Thread Count Serverkonfiguration erhöhen, um die PVS-Größe unter Kontrolle zu halten. Weitere Informationen finden Sie unter Serverkonfiguration: ADR Cleaner Thread Count, die ab SQL Server 2022 (16.x) verfügbar ist.

In SQL Server 2019 (15.x), oder wenn das Erhöhen des Werts der ADR Cleaner Thread Count-Konfiguration nicht dazu beiträgt, die PVS-Größe ausreichend zu reduzieren, kann die Arbeitslast einen Zeitraum der Ruhe oder Wiederherstellung für den PVS-Bereinigungsprozess erfordern, um Platz freizugeben.

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

Zum Beispiel:

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

Erfassen von Bereinigungsfehlern

Ab SQL Server 2022 (16.x) werden wichtige PVS-Bereinigungsmeldungen im Fehlerprotokoll aufgezeichnet. Bereinigungsstatistiken werden auch vom tx_mtvc2_sweep_statserweiterten Ereignis gemeldet.

Verwandte Inhalte