Udostępnij za pośrednictwem

Rozwiązywanie problemów z przyspieszonym odzyskiwaniem bazy danych

  • Artykuł

Dotyczy: SQL Server 2019 (15.x) i nowsze wersje Azure SQL DatabaseAzure SQL Managed InstanceSQL Database w usłudze Microsoft Fabric

Ten artykuł ułatwia diagnozowanie problemów z przyspieszonym odzyskiwaniem bazy danych (ADR) w programie SQL Server 2019 (15.x) i nowszych, usłudze Azure SQL Managed Instance, usłudze Azure SQL Database i bazie danych SQL Database w usłudze Microsoft Fabric.

Zbadaj rozmiar PVS

Użyj widoku DMV sys.dm_tran_persistent_version_store_stats, aby określić, czy rozmiar magazynu wersji trwałej (PVS) jest większy niż oczekiwano.

Następujące przykładowe zapytanie przedstawia informacje o bieżącym rozmiarze PVS, procesach oczyszczania i innych szczegółach:

SELECT  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_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
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();

Sprawdź kolumnę pvs_pct_of_database_size, aby zobaczyć rozmiar PVS względem całkowitego rozmiaru bazy danych. Zwróć uwagę na wszelkie różnice w stosunku do typowego rozmiaru PVS w porównaniu z punktami odniesienia zaobserwowanymi podczas innych okresów aktywności aplikacji. PVS jest uważany za duży, jeśli jest znacznie większy niż punkt odniesienia lub jeśli jest zbliżony do 50% rozmiaru bazy danych. Skorzystaj z poniższych kroków rozwiązywania problemów, aby znaleźć przyczynę dużego rozmiaru PVS.

Jeśli rozmiar PVS jest większy niż oczekiwano, sprawdź, czy:

Sprawdzanie długotrwałych aktywnych transakcji

Długotrwałe aktywne transakcje mogą zapobiegać czyszczeniu PVS w bazach danych z włączonym ADR. Sprawdź godzinę rozpoczęcia najstarszej aktywnej transakcji przy użyciu kolumny oldest_transaction_begin_time. Aby uzyskać więcej informacji na temat długotrwałych transakcji, użyj następującego przykładowego zapytania. Możesz ustawić progi czasu trwania transakcji i ilość wygenerowanego dziennika transakcji:

DECLARE @LongTxThreshold int = 1800;  /* 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 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 GETDATE() >= DATEADD(second, @LongTxThreshold, tr.transaction_begin_time)
        OR
        dbtr.database_transaction_log_bytes_used >= @LongTransactionLogBytes;

Po zidentyfikowaniu sesji, rozważ zakończenie sesji, jeśli jest to dozwolone. Przejrzyj aplikację, aby określić charakter problematycznych aktywnych transakcji, aby uniknąć problemu w przyszłości.

Aby uzyskać więcej informacji na temat rozwiązywania problemów z długotrwałym zapytaniami, zobacz:

Sprawdzanie długotrwałych aktywnych skanów migawek

Długotrwałe aktywne skanowania migawek mogą zapobiegać czyszczeniu PVS w bazach danych z włączonym ADR. Instrukcje korzystające z izolacji migawek READ COMMITTED (RCSI) lub SNAPSHOTpoziomów izolacji odbierają znaczniki czasu na poziomie instancji. Skanowanie migawki używa znacznika czasu, aby określić widoczność wiersza wersji dla transakcji RCSI lub SNAPSHOT. Każda instrukcja używająca RCSI ma własny znacznik czasu, podczas gdy izolacja SNAPSHOT ma znacznik czasu na poziomie transakcji.

Te znaczniki czasu transakcji na poziomie wystąpienia są używane nawet w transakcjach pojedynczej bazy danych, ponieważ każda transakcja może spowodować transakcję między bazami danych. W związku z tym skanowanie migawek może uniemożliwić czyszczenie PVS w jakiejkolwiek bazie danych w ramach tego samego wystąpienia silnika bazy danych. Podobnie, gdy funkcja ADR nie jest włączona, skanowanie migawek może zapobiec czyszczeniu magazynu wersji w tempdb. W związku z tym PVS może zwiększać swój rozmiar, gdy obecne są długotrwałe transakcje korzystające z SNAPSHOT lub RCSI.

Na początku tego artykułu, w sekcji dotyczącej rozwiązywania problemów z zapytaniem, kolumna pvs_off_row_page_skipped_min_useful_xts pokazuje liczbę stron pominiętych z powodu długiego skanowania migawki podczas odzyskiwania. Jeśli w tej kolumnie jest wyświetlana większa wartość niż zwykle, oznacza to, że długotrwałe skanowanie migawki uniemożliwia czyszczenie PVS.

Użyj następującego przykładowego zapytania, aby znaleźć sesję z długotrwałymi transakcjami typu SNAPSHOT lub RCSI.

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;

Aby zapobiec opóźnieniom czyszczenia PVS:

  • Rozważ zakończenie długotrwałej aktywnej sesji transakcji, która opóźnia czyszczenie PVS, jeśli to możliwe.
  • Dostrajanie długotrwałych zapytań w celu skrócenia czasu trwania zapytania.
  • Przejrzyj aplikację, aby określić charakter problematycznego aktywnego skanowania migawek. Rozważ stosowanie innego poziomu izolacji, na przykład READ COMMITTED, zamiast SNAPSHOT lub RCSI dla długotrwałych zapytań opóźniających czyszczenie PVS. Ten problem występuje częściej z poziomem izolacji SNAPSHOT.
  • W elastycznych pulach usługi Azure SQL Database rozważ przeniesienie baz danych, które mają długotrwałe transakcje przy użyciu izolacji SNAPSHOT lub RCSI z elastycznej puli.

Sprawdź długotrwałe zapytania na replikach wtórnych.

Jeśli baza danych ma repliki pomocnicze, sprawdź, czy pomocnicza wartość progowa posuwa się naprzód.

Uruchom następujące dynamiczne widoki zarządzania w repliki podstawowej, aby zidentyfikować długotrwałe zapytania w repliki pomocniczej, które mogą uniemożliwiać czyszczenie pvS:

W widoku DMV sys.dm_tran_persistent_version_store_stats kolumny pvs_off_row_page_skipped_low_water_mark mogą również wskazywać opóźnienie oczyszczania z powodu długotrwałego zapytania w replice pomocniczej.

Połącz się z repliką wtórną, znajdź sesję, która uruchamia długie zapytanie i rozważ zatrzymanie sesji, jeśli jest to dozwolone. Długotrwałe zapytanie w replice pomocniczej może wstrzymywać czyszczenie PVS, a także zapobiegać oczyszczaniu duchów .

Sprawdź dużą liczbę przerwanych transakcji

Jeśli żaden z poprzednich scenariuszy nie ma zastosowania do twoich obciążeń, prawdopodobnie proces czyszczenia jest wstrzymany z powodu dużej liczby przerwanych transakcji. Sprawdź kolumny aborted_version_cleaner_last_start_time i aborted_version_cleaner_last_end_time, aby sprawdzić, czy ostatnie przerwane czyszczenie transakcji zostało zakończone. oldest_aborted_transaction_id powinna być wyższa po zakończeniu przerwanego czyszczenia transakcji. Jeśli oldest_aborted_transaction_id jest znacznie niższe niż oldest_active_transaction_id, a current_abort_transaction_count ma większą wartość, prawdopodobnie istnieje stara przerwana transakcja uniemożliwiająca czyszczenie PVS.

Aby rozwiązać dużą liczbę przerwanych transakcji, rozważ następujące kwestie:

  • Jeśli to możliwe, zatrzymaj obciążenie, aby umożliwić narzędziu do czyszczenia wersji postęp.
  • Zoptymalizuj obciążenie, aby zmniejszyć liczbę blokad na poziomie obiektu.
  • Przejrzyj aplikację, aby zidentyfikować problem z wysokim współczynnikiem przerwania transakcji. Przerwania mogą pochodzić z dużej liczby zakleszczeń, zduplikowanych kluczy, naruszeń ograniczeń lub przekroczenia limitu czasu zapytania.
  • Jeśli używasz SQL Server, wyłącz ADR jako krok tylko w sytuacjach awaryjnych, aby kontrolować rozmiar PVS. Zobacz Wyłącz ADR.
  • Jeśli przerwana procedura sprzątania transakcji nie została niedawno zakończona pomyślnie, sprawdź dziennik błędów pod kątem komunikatów o problemach VersionCleaner.
  • Jeśli rozmiar PVS nie zostanie zmniejszony zgodnie z oczekiwaniami, nawet po zakończeniu oczyszczania, sprawdź kolumnę pvs_off_row_page_skipped_oldest_aborted_xdesid. Duże wartości wskazują, że miejsce jest nadal używane przez wersje wierszy z przerwanych transakcji.

Ręcznie uruchom proces oczyszczania PVS

Jeśli masz obciążenie z dużą ilością instrukcji DML (INSERT, UPDATE, DELETE, MERGE), takich jak wysokowydajne OLTP, może to wymagać czasu na odpoczynek/odzyskiwanie dla procesu oczyszczania PVS w celu odzyskania miejsca.

Aby ręcznie aktywować proces oczyszczania PVS między obciążeniami lub podczas okien obsługi, użyj procedury składowanej systemu sys.sp_persistent_version_cleanup.

Na przykład:

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

Przechwytywanie błędów podczas oczyszczania

Począwszy od programu SQL Server 2022 (16.x), zachowanie oczyszczania PVS jest rejestrowane w dzienniku błędów. Zazwyczaj powoduje to zarejestrowanie nowego zdarzenia dziennika co 10 minut. Statystyki oczyszczania są również zgłaszane przez zdarzenie rozszerzone tx_mtvc2_sweep_stats.

Powiązana zawartość