Rozwiązywanie problemów z automatycznym czyszczeniem danych śledzenia zmian

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Ten artykuł przedstawia sposoby rozwiązywania typowych problemów związanych z automatycznym czyszczeniem historii zmian.

Objawy

Ogólnie rzecz biorąc, jeśli automatyczne czyszczenie nie działa zgodnie z oczekiwaniami, można zobaczyć co najmniej jeden z następujących objawów:

• Wysokie zużycie pamięci przez jedną lub więcej tabel śledzenia zmian lub tabelę systemową syscommittab.
• Tabele boczne (tabele wewnętrzne, których nazwa zaczyna się od prefiksu change_tracking, na przykład change_tracking_12345) lub syscommittab lub oba, pokazują znaczną liczbę wierszy, które znajdują się poza skonfigurowanym okresem przechowywania.
• dbo.MSChange_tracking_history tabela zawiera wpisy z określonymi błędami czyszczenia.
• Wydajność CHANGETABLE uległa pogorszeniu w czasie.
• Automatyczne czyszczenie lub czyszczenie ręczne zgłasza wysokie użycie procesora.

Debugowanie i ograniczanie ryzyka

Aby zidentyfikować główne przyczyny problemu z usuwaniem automatycznym śledzenia zmian, wykonaj następujące kroki, aby przeanalizować i rozwiązać problem.

Stan automatycznego czyszczenia

Sprawdź, czy automatyczne czyszczenie zostało uruchomione. Aby to sprawdzić, wykonaj zapytanie dotyczące tabeli historii oczyszczania w tej samej bazie danych. Jeśli czyszczenie zostało uruchomione, tabela zawiera wpisy z godzinami rozpoczęcia i zakończenia oczyszczania. Jeśli czyszczenie nie zostało uruchomione, tabela jest pusta lub zawiera nieaktualne wpisy. Jeśli tabela historii zawiera wpisy z tagiem cleanup errors w kolumnie comments, oczyszczanie kończy się niepowodzeniem z powodu błędów oczyszczania na poziomie tabeli.

SELECT TOP 1000 * FROM dbo.MSChange_tracking_history ORDER BY start_time DESC;

Automatyczne czyszczenie jest okresowo uruchamiane z domyślnym interwałem wynoszącym 30 minut. Jeśli tabela historii nie istnieje, najprawdopodobniej automatyczne czyszczenie nigdy nie zostało uruchomione. W przeciwnym razie sprawdź wartości kolumn start_time i end_time. Jeśli najnowsze wpisy nie są aktualne, to znaczy, że mają kilka godzin lub dni, wtedy automatyczne czyszczenie może nie być uruchomione. Jeśli tak jest, wykonaj następujące kroki, aby rozwiązać problem.

1. Oczyszczanie jest wyłączone

Sprawdź, czy automatyczne czyszczenie jest włączone dla bazy danych. Jeśli tak nie jest, włącz ją i zaczekaj co najmniej 30 minut, zanim przyjrzyj się tabeli historii dla nowych wpisów. Następnie monitoruj postęp w tabeli historii.

SELECT * FROM sys.change_tracking_databases WHERE database_id=DB_ID('<database_name>')

Wartość niezerowa w is_auto_cleanup_on wskazuje, że automatyczne czyszczenie jest włączone. Wartość okresu przechowywania określa czas trwania, dla którego metadane śledzenia zmian są przechowywane w systemie. Wartość domyślna okresu przechowywania śledzenia zmian to 2 dni.

Aby włączyć lub wyłączyć śledzenie zmian, zobacz Włączanie i wyłączanie śledzenia zmian (SQL Server).

2. Oczyszczanie jest włączone, ale nie jest uruchomione

Jeśli automatyczne czyszczenie jest włączone, wątek automatycznego czyszczenia prawdopodobnie został zatrzymany z powodu nieoczekiwanych błędów. Obecnie ponowne uruchomienie wątku automatycznego oczyszczania nie jest możliwe. Należy zainicjować przejście w tryb failover na serwer pomocniczy (lub ponownie uruchomić serwer w przypadku braku pomocniczego) i potwierdzić, że ustawienie automatycznego oczyszczania jest włączone dla bazy danych.

Automatyczne czyszczenie działa, ale nie przynosi efektów

Jeśli jedna lub więcej tabel pomocniczych wykazuje znaczne zużycie pamięci lub zawiera dużą liczbę rekordów poza skonfigurowanym okresem przechowywania, wykonaj kroki opisane w tej sekcji, które opisują środki zaradcze dla jednej tabeli pomocniczej. W razie potrzeby te same kroki można powtórzyć w przypadku większej liczby tabel.

1. Ocena zaległości w automatycznym oczyszczaniu

Zidentyfikuj tabele pomocnicze, które mają duże zaległości zapisów wygasłych, wymagających podjęcia działań naprawczych. Uruchom następujące zapytania, aby zidentyfikować tabele boczne z dużą liczbą wygasłych rekordów. Pamiętaj, aby zastąpić wartości w przykładowych skryptach zgodnie z podanym wzorem.

1. Pobierz nieprawidłową wersję oczyszczania:

   SELECT * FROM sys.change_tracking_tables;
   

   Wartość cleanup_version ze zwróconych wierszy reprezentuje nieprawidłową wersję czyszczenia.

2. Uruchom następujące dynamiczne zapytanie Transact-SQL (T-SQL), które generuje zapytanie w celu uzyskania liczby wygasłych wierszy w tabelach podrzędnych. Zastąp wartość <invalid_version> w zapytaniu wartością uzyskaną w poprzednim kroku.

   SELECT 'SELECT ''' + QUOTENAME(name) + ''', COUNT_BIG(*) FROM [sys].' + QUOTENAME(name)
       + ' WHERE sys_change_xdes_id IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts <= <invalid_version>) UNION'
   FROM sys.internal_tables
   WHERE internal_type = 209;
   

3. Skopiuj zestaw wyników z poprzedniego zapytania i usuń słowo kluczowe UNION z ostatniego wiersza. Jeśli uruchomisz wygenerowane zapytanie T-SQL za pośrednictwem dedykowanego połączenia administratora (DAC), zapytanie podaje liczby wygasłych wierszy wszystkich tabel pomocniczych. W zależności od rozmiaru tabeli sys.syscommittab i liczby tabel bocznych wykonanie tego zapytania może zająć dużo czasu.

   Ważny

   Ten krok jest niezbędny do wykonania kroków zaradczych. Jeśli poprzednie zapytanie nie powiedzie się, zidentyfikuj wygasłe liczby wierszy dla poszczególnych tabel bocznych przy użyciu zapytań podanych dalej.

Wykonaj następujące kroki zaradcze dla tabel pomocniczych, zaczynając od tych z największą liczbą wygaśniętych wierszy, aż liczba wygaśniętych wierszy zmniejszy się do poziomu, na który może reagować automatyczne oczyszczanie.

Po zidentyfikowaniu tabel dodatkowych z dużą liczbą wygasłych rekordów zbierz informacje na temat opóźnienia instrukcji usuwania rekordów w tabelach dodatkowych i prędkości usuwania rekordów na sekundę w ciągu ostatnich kilku godzin. Następnie szacuj czas wymagany do wyczyszczenia tabeli bocznej, biorąc pod uwagę zarówno nieaktualną liczbę wierszy, jak i opóźnienie usuwania.

Użyj następującego fragmentu kodu języka T-SQL, zastępując szablony parametrów odpowiednimi wartościami.

• Wykonaj zapytanie dotyczące szybkości czyszczenia na sekundę:

  SELECT
      table_name,
      rows_cleaned_up / ISNULL(NULLIF(DATEDIFF(second, start_time, end_time), 0), 1),
      cleanup_version
  FROM dbo.MSChange_tracking_history
  WHERE table_name = '<table_name>'
  ORDER BY end_time DESC;
  

  Możesz również użyć stopnia szczegółowości minuty lub godziny dla funkcji DATEDIFF.

• Znajdź nieaktualizowaną liczbę wierszy w tabeli bocznej. To zapytanie ułatwia znalezienie liczby wierszy oczekujących na wyczyszczenie.

  <internal_table_name> i <cleanup_version> dla tabeli użytkownika znajdują się w wynikach zaprezentowanych w poprzedniej sekcji. Korzystając z tych informacji, wykonaj następujący kod T-SQL za pośrednictwem dedykowanego połączenia administratora (DAC):

  SELECT '<internal_table_name>',
      COUNT_BIG(*)
  FROM sys.<internal_table_name>
  WHERE sys_change_xdes_id IN (
          SELECT xdes_id
          FROM sys.syscommittab ssct
          WHERE ssct.commit_ts <= <cleanup_version>
  );
  

  Wykonanie tego zapytania może zająć trochę czasu. W przypadkach, gdy zapytanie przekroczyło limit czasu, oblicz nieaktualne wiersze, wyszukując różnicę między łącznymi wierszami a aktywnymi wierszami, które mają zostać oczyszczone.

• Znajdź łączną liczbę wierszy w tabeli bocznej, wykonując następujące zapytanie:

  SELECT sum(row_count) FROM sys.dm_db_partition_stats
  WHERE object_id = OBJECT_ID('sys.<internal_table_name>')
  GROUP BY partition_id;
  

• Znajdź liczbę aktywnych wierszy w tabeli bocznej, wykonując następujące zapytanie:

  SELECT '<internal_table_name>', COUNT_BIG(*) FROM sys.<internal_table_name> WHERE sys_change_xdes_id
  IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts > <cleanup_version>);
  

  Szacowany czas czyszczenia tabeli można obliczyć przy użyciu tempa oczyszczania i liczby nieaktualnych wierszy. Rozważmy następującą formułę:

  czas czyszczenia w minutach = (liczba nieprzetworzonych wierszy) / (tempo czyszczenia w minutach)

  Jeśli czas ukończenia oczyszczania tabeli jest akceptowalny, monitoruj postęp i pozwól, aby automatyczne czyszczenie kontynuowało pracę. Jeśli nie, przejdź do następnych kroków, aby przejść do szczegółów.

2. Sprawdź konflikty blokady tabeli

Ustal, czy czyszczenie nie postępuje z powodu konfliktów eskalacji blokady tabeli, które konsekwentnie uniemożliwiają uzyskanie blokad na tabeli bocznej do usunięcia wierszy.

Aby potwierdzić konflikt blokady, uruchom następujący kod T-SQL. To zapytanie pobiera rekordy dla problematycznej tabeli, aby określić, czy istnieje wiele wpisów wskazujących konflikty blokady. Kilka sporadycznych konfliktów rozłożonych w okresie czasu nie powinno kwalifikować się do kolejnych kroków łagodzących. Konflikty powinny być powtarzane.

SELECT TOP 1000 *
FROM dbo.MSChange_tracking_history
WHERE table_name = '<user_table_name>'
ORDER BY start_time DESC;

Jeśli tabela historii zawiera wiele wpisów w kolumnach comments z wartością Cleanup error: Lock request time out period exceeded, jest to jasne wskazanie, że wiele prób oczyszczania nie powiodło się z powodu konfliktów blokady lub przekroczenia czasu blokady następująco po sobie. Rozważ następujące środki zaradcze:

• Wyłącz i włącz śledzenie zmian w problematycznej tabeli. Powoduje to przeczyszczenie wszystkich metadanych śledzenia dla tabeli. Dane tabeli pozostają nienaruszone. Jest to najszybszy środek zaradczy.

• Jeśli poprzednia opcja nie jest możliwa, przejdź do ręcznego czyszczenia tabeli, włączając flagę śledzenia 8284 w następujący sposób:

  DBCC TRACEON (8284, -1);
  GO
  EXEC [sys].[sp_flush_CT_internal_table_on_demand] @TableToClean = '<table_name>';
  

3. Sprawdź inne przyczyny

Inną możliwą przyczyną opóźnienia w oczyszczaniu jest spowolnienie instrukcji 'delete'. Aby określić, czy tak, sprawdź wartość hardened_cleanup_version. Tę wartość można pobrać za pośrednictwem dedykowanego połączenia administracyjnego (DAC) z rozpatrywaną bazą danych.

Znajdź wzmocnioną wersję oczyszczania, wykonując następujące zapytanie:

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1004;

Znajdź wersję oczyszczania, wykonując następujące zapytanie:

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1003;

Jeśli hardened_cleanup_version i cleanup_version wartości są równe, pomiń tę sekcję i przejdź do następnej sekcji.

Jeśli obie wartości są różne, oznacza to, że co najmniej jedna tabela side napotkała błędy. Najszybszym sposobem ograniczenia ryzyka jest wyłączenie & i włączenie śledzenia zmian w problematycznej tabeli. Powoduje to usunięcie wszystkich metadanych śledzenia dla tabeli. Dane w tabeli pozostają nienaruszone.

Jeśli poprzednia opcja nie jest możliwa, przeprowadź ręczne czyszczenie tabeli.

Rozwiązywanie problemów z syscommittab

W tej sekcji opisano kroki debugowania i rozwiązywania problemów z tabelą systemu syscommittab, jeśli używa dużo przestrzeni dyskowej lub jeśli ma duże zaległości w postaci nieaktualnych wierszy.

Oczyszczanie tabeli systemu syscommittab zależy od oczyszczania tabeli pomocniczej. Tylko po wyczyszczeniu wszystkich tabel bocznych można oczyścić syscommittab. Upewnij się, że wszystkie kroki w sekcji pod tytułem "Automatyczne czyszczenie działa, ale nie robi postępów" zostały wykonane.

Aby w sposób jawny wywołać oczyszczanie syscommittab, użyj procedury składowanej sys.sp_flush_commit_table_on_demand.

Notatka

Procedura składowana sys.sp_flush_commit_table_on_demand może zająć dużo czasu, jeśli usuwa dużą liczbę zaległych wierszy.

Jak pokazano w sekcji przykładowej z artykułu sys.sp_flush_commit_table_on_demand, ta procedura składowana zwraca wartość safe_cleanup_version()oraz liczbę usuniętych wierszy. Jeśli zwrócona wartość wydaje się być 0, a jeśli izolacja migawki jest włączona, czyszczenie może nie usunąć niczego z syscommittab.

Jeśli okres przechowywania jest dłuższy niż jeden dzień, można bezpiecznie uruchomić ponownie procedurę składowaną sys.sp_flush_commit_table_on_demand po włączeniu globalnie znacznika śledzenia 8239. Użycie tej flagi śledzenia, gdy izolacja migawki jest wyłączona, jest zawsze bezpieczna, ale w niektórych przypadkach może nie być konieczne.

Wysokie wykorzystanie procesora podczas czyszczenia

Problem opisany w tej sekcji może być widoczny w starszych wersjach programu SQL Server. Jeśli w bazie danych istnieje duża liczba tabel śledzonych zmian, a automatyczne czyszczenie lub czyszczenie ręczne powoduje wysokie wykorzystanie procesora CPU. Ten problem może być również spowodowany tabelą historii, która została krótko wymieniona w poprzednich sekcjach.

Użyj następującego kodu T-SQL, aby sprawdzić liczbę wierszy w tabeli historii:

SELECT COUNT(*) from dbo.MSChange_tracking_history;

Jeśli liczba wierszy jest wystarczająco duża, spróbuj dodać następujący indeks, jeśli go brak. Użyj następującego kodu T-SQL, aby dodać indeks:

IF NOT EXISTS (
    SELECT *
    FROM sys.indexes
    WHERE name = 'IX_MSchange_tracking_history_start_time'
        AND object_id = OBJECT_ID('dbo.MSchange_tracking_history')
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_MSchange_tracking_history_start_time
    ON dbo.MSchange_tracking_history (start_time)
END

Uruchamiaj oczyszczanie częściej niż co 30 minut

Określone tabele mogą mieć wysoką częstotliwość zmian i może się okazać, że zadanie automatycznego czyszczenia nie może wyczyścić tabel bocznych i syscommittab w ramach 30-minutowego interwału. W takim przypadku można uruchomić zadanie oczyszczania ręcznego ze zwiększoną częstotliwością w celu ułatwienia procesu.

W przypadku programu SQL Server i usługi Azure SQL Managed Instance utworzyć zadanie w tle przy użyciu sp_flush_CT_internal_table_on_demand z krótszym wewnętrznym niż domyślne 30 minut. W przypadku usługi Azure SQL Database można użyć azure Logic Apps do planowania tych zadań.

Poniższy kod T-SQL może służyć do utworzenia zadania ułatwiającego czyszczenie tabel bocznych na potrzeby śledzenia zmian:

-- Loop to invoke manual cleanup procedure for cleaning up change tracking tables in a database
-- Fetch the tables enabled for change tracking
SELECT IDENTITY(INT, 1, 1) AS TableID,
    (SCHEMA_NAME(tbl.Schema_ID) + '.' + OBJECT_NAME(ctt.object_id)) AS TableName
INTO #CT_Tables
FROM sys.change_tracking_tables ctt
INNER JOIN sys.tables tbl
    ON tbl.object_id = ctt.object_id;

-- Set up the variables
DECLARE @start INT = 1,
    @end INT = (
        SELECT COUNT(*)
        FROM #CT_Tables
        ),
    @tablename VARCHAR(255);

WHILE (@start <= @end)
BEGIN
    -- Fetch the table to be cleaned up
    SELECT @tablename = TableName
    FROM #CT_Tables
    WHERE TableID = @start

    -- Execute the manual cleanup stored procedure
    EXEC sp_flush_CT_internal_table_on_demand @tablename

    -- Increment the counter
    SET @start = @start + 1;
END

DROP TABLE #CT_Tables;

Powiązana zawartość

• Informacje o usłudze Change Tracking (SQL Server)
• funkcje śledzenia zmian (Transact-SQL)
• Procedury składowane śledzenia zmian (Transact-SQL)
• tabele śledzenia zmian (Transact-SQL)
• Wyświetlanie informacji o danych i przestrzeni logu dla bazy danych
• Rozwiązywanie problemów z wysokim użyciem procesora CPU w programie SQL Server