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 transakcje aktywne mogą uniemożliwiać oczyszczanie PVS w bazach danych, w których włączono funkcję 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, można rozważyć jej zakończenie, 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ą utrudniać proces czyszczenia PVS w bazach danych, które mają włączoną funkcję ADR. Instrukcje korzystające z izolacji migawkowej (RCSI) lub SNAPSHOT otrzymują 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 Rozpoznawanie Problemów) 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 pokazuje dużą wartość, oznacza to, że długotrwałe skanowanie migawki utrudnia proces czyszczenia systemu PVS.

Użyj następującego przykładowego zapytania, aby znaleźć sesje z długotrwałym działaniem 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 Azure SQL Database rozważ przeniesienie baz danych, które mają długotrwałe transakcje z wykorzystaniem izolacji SNAPSHOT lub RCSI poza elastyczną pulę, aby uniknąć opóźnienia oczyszczania PVS w innych bazach danych w tej samej puli.

Sprawdź długo działające 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 replikacie wtórnej. Oprócz wstrzymania oczyszczania PVS, długotrwałe zapytanie na replice pomocniczej może również wstrzymać sprzątanie danych widmowych.

Możesz użyć następujących przykładowych zapytań na głównej replice, aby sprawdzić, czy długotrwałe zapytania na replikach drugorzędnych uniemożliwiają czyszczenie PVS. Jeśli na replice podstawowej jest uruchomione obciążenie związane z zapisem, ale wartość w kolumnie low_water_mark_for_ghosts nie zwiększa się z jednego wykonania przykładowego zapytania do następnego, proces PVS i czyszczenie ghost 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. Więcej informacji można znaleźć w Znajdowaniu 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 zadanie, aby ułatwić postęp procesu czyszczenia wersji.
  • Przejrzyj aplikację, aby zidentyfikować i rozwiązać problem z wysokim współczynnikiem przerwania transakcji. Przerwania transakcji mogą pochodzić z dużej liczby zakleszczeń, zduplikowanych kluczy, naruszeń ograniczeń lub przekroczeń 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 tylko w nagłych przypadkach, aby zarządzać rozmiarem 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 po zakończeniu czyszczenia rozmiar PVS nie zmniejszy się zgodnie z oczekiwaniami, 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 czyszczenia PVS pomiędzy obciążeniami prac lub podczas okien serwisowych, użyj procedury składowanej systemu sys.sp_persistent_version_cleanup.

Na przykład:

EXEC sys.sp_persistent_version_cleanup [WideWorldImporters];

Aktywna transakcja może uniemożliwić rozpoczęcie procesu oczyszczania PVS. W takim przypadku sesja uruchamiająca procedurę składowaną sys.sp_persistent_version_cleanup czeka z typem oczekiwania PVS_CLEANUP_LOCK. Możesz poczekać na zakończenie transakcji lub rozważyć zamknięcie sesji blokującej z aktywną transakcją, jeśli to możliwe.

Rejestrowanie niepowodzeń 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 rozszerzone zdarzenie tx_mtvc2_sweep_stats.

Powiązana zawartość