Udostępnij za pośrednictwem

Monitorowanie i 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ł pomaga monitorować, diagnozować i rozwiązywać problemy z przyspieszonym odzyskiwaniem bazy danych (ADR) w programie SQL Server 2019 (15.x) i nowszych, Azure SQL Managed Instance, Azure SQL Database i SQL Database w 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.

Poniższe przykładowe zapytanie diagnostyczne przedstawia informacje o bieżącym rozmiarze PVS, procesach oczyszczania i innych szczegółach we wszystkich bazach danych, w których rozmiar PVS jest większy niż zero:

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;

Sprawdź kolumnę pvs_percent_of_database_size, aby zobaczyć rozmiar PVS względem całkowitego rozmiaru bazy danych. Zwróć uwagę na różnicę między typowym rozmiarem PVS a wartościami odniesienia widocznymi w typowych okresach 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.

Jeśli rozmiar pvS nie zmniejsza się, skorzystaj z poniższych kroków rozwiązywania problemów, aby znaleźć i rozwiązać przyczynę dużego rozmiaru PVS.

Wskazówka

Kolumny wymienione w poniższych krokach rozwiązywania problemów odwołują się do kolumn w zestawie wyników zapytania diagnostycznego w tej sekcji.

Duży rozmiar PVS może być spowodowany dowolnym z następujących powodów:

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 znaleźć długotrwałe transakcje, użyj następującego przykładowego zapytania. Możesz ustawić progi czasu trwania transakcji i ilość wygenerowanego dziennika transakcji:

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;

Po zidentyfikowaniu sesji, rozważ zakończenie sesji, jeśli jest to dozwolone. Przejrzyj aplikację, aby określić charakter problematycznych 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 migawkowe używa znacznika czasu do określenia widoczności wersji wiersza 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 czasowe transakcji na poziomie instancji są używane nawet w przypadku transakcji z jedną bazą danych, ponieważ każda transakcja może zostać przekształcona w transakcję obejmującą wiele baz 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, nawet jeśli ADR (automatyczne odzyskiwanie danych) nie jest włączone, 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.

Kolumna pvs_off_row_page_skipped_min_useful_xts pokazuje liczbę stron pominiętych podczas czyszczenia w wyniku długiego skanowania migawki. Jeśli ta kolumna zawiera dużą wartość, oznacza to, że długie skanowanie migawki przeszkadza w czyszczeniu PVS.

Użyj następującego przykładowego zapytania, aby znaleźć sesje z długotrwałą SNAPSHOT lub transakcją RCSI:

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;

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, aby uniknąć opóźnienia oczyszczania PVS w innych bazach danych w tej samej 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.

Duża wartość w kolumnie pvs_off_row_page_skipped_low_water_mark może wskazywać na opóźnienie procesu oczyszczania z powodu długotrwałego zapytania na repliki wtórnej. Oprócz wstrzymania oczyszczania PVS długotrwałe zapytanie w repliki pomocniczej może również pomieścić oczyszczanie duchów.

Na głównej replikacji można używać następujących przykładowych zapytań, aby sprawdzić, czy długotrwałe zapytania na replikach drugorzędnych mogą uniemożliwiać czyszczenie PVS. Jeśli obciążenie zapisu jest uruchomione na replice podstawowej, ale wartość w kolumnie low_water_mark_for_ghosts nie rośnie z jednego wykonania przykładowego zapytania do następnego, funkcja PVS i oczyszczanie duchów mogą być opóźnione przez długotrwałe zapytanie na replice pomocniczej.

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;

Aby uzyskać więcej informacji, zobacz opis kolumny low_water_mark_for_ghosts w sys.dm_hadr_database_replica_states.

Połącz się z każdą repliką pomocniczą z możliwością odczytu, znajdź sesję, w której działa zapytanie przez długi czas, i rozważ zakończenie sesji, jeśli jest to dozwolone. Aby uzyskać więcej informacji, zobacz Znajdowanie wolnych zapytań.

Sprawdź dużą liczbę przerwanych transakcji

Sprawdź kolumny aborted_version_cleaner_start_time i aborted_version_cleaner_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ższa niż oldest_active_transaction_id, a wartość current_abort_transaction_count jest duża, prawdopodobnie istnieje stara przerwana transakcja, która uniemożliwia czyszczenie PVS.

Aby rozwiązać problem z opóźnieniem oczyszczania PVS spowodowanym dużą liczbą przerwanych transakcji, rozważ następujące kwestie:

  • Jeśli używasz programu SQL Server 2022 (16.x), zwiększ wartość konfiguracji serwera ADR Cleaner Thread Count. Aby uzyskać więcej informacji, zobacz Konfiguracja serwera: Liczba wątków ADR Cleaner.
  • Jeśli to możliwe, zatrzymaj obciążenie, aby umożliwić narzędziu do czyszczenia wersji postęp.
  • Przejrzyj aplikację, aby zidentyfikować i rozwiązać problem z wysokim współczynnikiem przerwania transakcji. Przerwania mogą pochodzić z dużej liczby zakleszczeń, zduplikowanych kluczy, naruszeń ograniczeń lub przekroczenia limitu czasu zapytania.
  • Zoptymalizuj obciążenie, aby zmniejszyć liczbę blokad, które są niezgodne z blokadami poziomu obiektu lub poziomu partycji IX wymaganymi przez narzędzie czyszczące PVS. Aby uzyskać więcej informacji, zobacz Kompatybilność blokady.
  • 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 korzystasz z programu SQL Server i przerwane czyszczenie transakcji nie zostało ostatnio pomyślnie zakończone, sprawdź dziennik błędów pod kątem komunikatów dotyczących problemów 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.

Kontrolowanie rozmiaru PVS

Jeśli masz obciążenie z dużą ilością instrukcji DML (INSERT, UPDATE, DELETE, MERGE), takich jak duże OLTP i zauważ, że rozmiar PVS jest duży, może być konieczne zwiększenie wartości konfiguracji serwera ADR Cleaner Thread Count, aby zachować kontrolę rozmiaru PVS. Aby uzyskać więcej informacji, zobacz Server configuration: ADR Cleaner Thread Count, który jest dostępny począwszy od programu SQL Server 2022 (16.x).

W SQL Server 2019 (15.x) lub jeśli zwiększenie wartości konfiguracji ADR Cleaner Thread Count nie pomoże wystarczająco zmniejszyć rozmiaru PVS, obciążenie może wymagać okresu odpoczynku lub odzyskiwania, aby proces oczyszczania PVS mógł odzyskać przestrzeń.

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), w dzienniku błędów rejestrowane są istotne komunikaty oczyszczania PVS. Statystyki oczyszczania są również zgłaszane przez zdarzenie rozszerzone tx_mtvc2_sweep_stats.

Powiązana zawartość