Administrowanie przechwytywaniem danych zmian i ich monitorowanie

Artykuł
2025-03-05

Dotyczy:programu SQL Server Azure SQL Managed Instance

W tym temacie opisano sposób administrowania i monitorowania przechwytywania danych zmian dla programu SQL Server i usługi Azure SQL Managed Instance.

W przypadku usługi Azure SQL Database korzystającej z innego mechanizmu zadań zobacz CDC z usługą Azure SQL Database.

Zadanie przechwytywania

Zadanie przechwytywania jest inicjowane przez uruchomienie procedury składowanej bez parametrów sp_MScdc_capture_job. Ta procedura składowana rozpoczyna się od wyodrębnienia skonfigurowanych wartości dla maxtrans, maxscans, continuousi pollinginterval dla zadania przechwytywania z msdb.dbo.cdc_jobs. Te skonfigurowane wartości są następnie przekazywane jako parametry do procedury składowanej sp_cdc_scan. Służy to do wywoływania sp_replcmds w celu przeprowadzenia skanowania dziennika.

Przechwytywanie parametrów zadania

Aby zrozumieć zachowanie zadania przechwytywania, należy zrozumieć, jak konfigurowalne parametry są używane przez sp_cdc_scan.

parametr `maxtrans`

Parametr maxtrans określa maksymalną liczbę transakcji, które mogą być przetwarzane w jednym cyklu skanowania dziennika. Jeśli podczas skanowania liczba transakcji do przetworzenia osiągnie ten limit, żadne dodatkowe transakcje nie zostaną uwzględnione w bieżącym skanowaniu. Po zakończeniu cyklu skanowania liczba przetworzonych transakcji będzie zawsze mniejsza lub równa maxtrans.

parametr `maxscans`

Parametr maxscans określa maksymalną liczbę cykli skanowania podejmowanych w celu opróżnienia dziennika przed przerwaniem działania (gdy ciągłe = 0) lub wykonaniem instrukcji WAITFOR (gdy ciągłe = 1).

parametr `continuous`

Parametr continuous kontroluje, czy sp_cdc_scan zrezygnuje z kontroli po opróżnieniu dziennika lub wykonaniu maksymalnej liczby cykli skanowania (tryb jednorazowy). Określa również, czy sp_cdc_scan nadal działa aż do jawnego zatrzymania (tryb ciągły).

Tryb jednorazowy

W trybie jednorazowego przechwytywania zadanie przechwytywania sp_cdc_scan ma wykonać do maxtrans skanów, aby spróbować opróżnić dziennik i powrócić. Wszystkie transakcje oprócz maxtrans, które znajdują się w dzienniku, zostaną przetworzone w kolejnych skanowaniach.

Tryb jednorazowy jest używany w kontrolowanych testach, gdzie znana jest ilość transakcji do przetworzenia, i zaletą jest to, że zadanie jest zamykane automatycznie po jego zakończeniu. Tryb jednorazowy nie jest zalecany do użytku produkcyjnego. Jest to spowodowane tym, że opiera się na harmonogramie zadań, aby zarządzać częstotliwość uruchamiania cyklu skanowania.

Podczas uruchamiania w trybie jednosstrzałowym można obliczyć górną granicę oczekiwanej przepływności zadania przechwytywania, wyrażone w transakcjach na sekundę przy użyciu następujących obliczeń:

(maxtrans * maxscans) / number of seconds between scans

Nawet jeśli czas wymagany do skanowania dziennika i wypełnienia tabel zmian nie różnił się znacząco od 0, średnia przepływność zadania nie może przekroczyć wartości uzyskanej przez podzielenie maksymalnej dozwolonej transakcji dla pojedynczego skanowania pomnożonego przez maksymalną dozwoloną liczbę skanowań przez liczbę sekund oddzielających przetwarzanie dziennika.

Gdyby tryb jednostrzałowy był używany do regulacji skanowania logów, liczba sekund pomiędzy przetwarzaniem logów musiałaby być określona zgodnie z harmonogramem zadań. Gdy tego rodzaju zachowanie jest pożądane, uruchomienie zadania przechwytywania w trybie ciągłym jest lepszym sposobem na ponowne zaplanowanie skanowania dziennika.

Tryb ciągły i interwał sondowania

W trybie ciągłym zadanie przechwytywania żąda, aby sp_cdc_scan działało w sposób ciągły. Dzięki temu procedura składowana może zarządzać własną pętlą oczekiwania, zapewniając nie tylko maxtrans i maxscans, ale także wartość określającą liczbę sekund pomiędzy operacjami przetwarzania dziennika (okres sondowania). W trybie ciągłym zadanie przechwytywania pozostaje aktywne, wykonując WAITFOR pomiędzy skanowaniem logów.

Notatka

Gdy wartość interwału sondowania jest większa niż 0, ten sam górny limit przepływności dla cyklicznego zadania jednorazowego dotyczy również działania zadania w trybie ciągłym. Oznacza to, że (maxtrans * maxscans) podzielone przez interwał odpytywania niezerowego wyznaczy górną granicę średniej liczby transakcji, które mogą być przetwarzane przez zadanie rejestrowania.

Konfiguracja zadania przechwytywania

W przypadku zadania przechwytywania można zastosować dodatkową logikę, aby określić, czy nowe skanowanie rozpoczyna się natychmiast, czy też należy wprowadzić opóźnienie przed rozpoczęciem nowego skanowania, zamiast polegać na stałym interwale sondowania. Wybór może być oparty tylko na porze dnia, być może wymuszając bardzo długi sen w godzinach szczytu aktywności, a nawet przechodząc do sondowania o interwale 0 na koniec dnia, gdy ważne jest, aby ukończyć przetwarzanie dnia i przygotować się na nocne procesy. Postęp procesu przechwytywania można również monitorować w celu określenia, kiedy wszystkie transakcje zatwierdzone do północy zostały zeskanowane i zdeponowane w tabelach zmian. Dzięki temu zadanie przechwytywania zostanie zakończone, aby mogło być ponownie uruchomione w ramach zaplanowanego codziennego restartu. Aby dostosować zachowanie, możesz zastąpić krok zadania, który wywołuje sp_cdc_scan, wywołaniem otoki napisanej przez użytkownika dla sp_cdc_scan.

Zadanie oczyszczania

Ta sekcja zawiera informacje o sposobie działania zadania czyszczenia dla przechwytywania zmian w danych.

Struktura zadania oczyszczania

Przechwytywanie zmian danych używa strategii oczyszczania opartej na przechowywaniu w celu zarządzania zmianą rozmiaru tabeli. W usługach SQL Server i Azure SQL Managed Instance mechanizm oczyszczania składa się z zadania Transact-SQL agenta programu SQL Server utworzonego po włączeniu pierwszej tabeli bazy danych. Jedna operacja oczyszczania obsługuje oczyszczanie wszystkich tabel zmieniających się w bazie danych i stosuje ten sam okres przechowywania dla wszystkich zdefiniowanych instancji przechwytywania.

Zadanie oczyszczania jest inicjowane przez uruchomienie bez parametrów procedury składowanej sp_MScdc_cleanup_job. Ta procedura składowana rozpoczyna się od wyodrębnienia skonfigurowanych wartości retencji i wartości progowych operacji oczyszczania z msdb.dbo.cdc_jobs. Wartość przechowywania służy do obliczania nowego niskiego limitu dla tabel zmian. Określona liczba minut jest odejmowana od maksymalnej wartości tran_end_time z tabeli cdc.lsn_time_mapping w celu uzyskania nowego niskiego limitu wody wyrażonego jako wartość typu data/godzina. Następnie tabela CDC.lsn_time_mapping służy do konwertowania tej wartości daty/godziny na odpowiadającą lsn wartość. Jeśli ten sam czas zatwierdzania jest dzielony przez wiele wpisów w tabeli, lsn, który odpowiada wpisowi z najmniejszą lsn, jest wybierany jako nowy dolny próg. Ta wartość lsn jest przekazywana do sp_cdc_cleanup_change_tables, aby usunąć wpisy z tabel zmian w bazie danych.

Notatka

Zaletą korzystania z czasu zatwierdzania ostatniej transakcji jako podstawy do obliczania nowego niskiego limitu jest to, że pozwala to, aby zmiany pozostały w tabelach zmian dla określonego czasu. Dzieje się tak nawet wtedy, gdy proces przechwytywania jest opóźniony. Wszystkie wpisy, które mają taki sam czas zatwierdzenia jak aktualny niski poziom wody, nadal są reprezentowane w tabelach zmian przez wybór najmniejszej wartości lsn, która ma taki sam czas zatwierdzenia co rzeczywisty niski poziom wody.

Po wykonaniu czyszczenia niski punkt odniesienia dla wszystkich wystąpień przechwytywania jest początkowo aktualizowany w ramach jednej transakcji. Następnie próbuje usunąć przestarzałe wpisy z tabel zmian i tabeli cdc.lsn_time_mapping. Konfigurowalna wartość progowa ogranicza liczbę wpisów usuniętych w dowolnej instrukcji pojedynczej. Nie można wykonać operacji usuwania w żadnej pojedynczej tabeli, co uniemożliwi próbę wykonania operacji w pozostałych tabelach.

Dostosowywanie zadania oczyszczania

W przypadku zadania oczyszczania możliwość dostosowania jest w strategii używanej do określenia, które wpisy tabeli zmian mają zostać odrzucone. W dostarczonym zadaniu oczyszczania jedyną obsługiwaną strategią jest strategia oparta na czasie. W tej sytuacji, nowy najniższy poziom jest obliczany przez odjęcie okresu retencji od czasu zatwierdzenia ostatniej przetworzonej transakcji. Ponieważ podstawowe procedury oczyszczania są oparte na lsn zamiast czasu, dowolna liczba strategii może być użyta do określenia najmniejszej lsn, aby zachować ją w tabelach zmian. Tylko niektóre z nich są ściśle oparte na czasie. Wiedza na temat klientów, na przykład, może służyć do zapewnienia bezpieczeństwa w trybie failsafe, jeśli nie można uruchomić procesów podrzędnych, które wymagają dostępu do tabel zmian. Ponadto, mimo że strategia domyślna stosuje te same lsn w celu wyczyszczenia wszystkich tabel zmian baz danych, podstawowa procedura oczyszczania może być również wywoływana w celu oczyszczenia na poziomie wystąpienia przechwytywania.

Monitorowanie procesu

Monitorowanie procesu przechwytywania zmian danych pozwala określić, czy zmiany są zapisywane poprawnie i z rozsądnym opóźnieniem w tabelach zmian. Monitorowanie może również pomóc w zidentyfikowaniu wszelkich błędów, które mogą wystąpić. Program SQL Server zawiera dwa dynamiczne widoki zarządzania, które ułatwiają monitorowanie przechwytywania zmian danych: sys.dm_cdc_log_scan_sessions i sys.dm_cdc_errors.

Identyfikowanie sesji z pustymi zestawami wyników

Każdy wiersz w sys.dm_cdc_log_scan_sessions reprezentuje sesję skanowania dziennika (z wyjątkiem wiersza o identyfikatorze 0). Sesja skanowania dziennika jest równoważna jednemu wykonaniu sp_cdc_scan. Podczas sesji skanowanie może zwracać zmiany lub zwracać pusty wynik. Jeśli zestaw wyników jest pusty, kolumna empty_scan_count w sys.dm_cdc_log_scan_sessions jest ustawiona na 1. Jeśli istnieją kolejne puste zestawy wyników, na przykład jeśli zadanie przechwytywania jest uruchomione w sposób ciągły, empty_scan_count w ostatnim istniejącym wierszu jest zwiększany. Jeśli na przykład w sys.dm_cdc_log_scan_sessions znajduje się już 10 wierszy ze skanów, które wykazały zmiany i następuje pięć pustych wyników z rzędu, widok obejmuje 11 wierszy. Ostatni wiersz ma wartość 5 w kolumnie empty_scan_count. Aby określić sesje z pustym skanowaniem, uruchom następujące zapytanie:

SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count <> 0

Określanie opóźnienia

Widok zarządzania sys.dm_cdc_log_scan_sessions zawiera kolumnę, która rejestruje opóźnienie dla każdej sesji przechwytywania. Opóźnienie jest definiowane jako czas, który upłynął między transakcją zatwierdzaną w tabeli źródłowej a ostatnią przechwyconą transakcją zatwierdzaną w tabeli zmian. Kolumna opóźnienia jest wypełniana tylko dla aktywnych sesji. W przypadku sesji o wartości większej niż 0 w kolumnie empty_scan_count kolumna opóźnienia jest ustawiona na 0. Następujące zapytanie zwraca średnie opóźnienie dla ostatnich sesji:

SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0

Możesz użyć danych opóźnienia, aby określić, jak szybko lub wolno proces przechwytywania przetwarza transakcje. Te dane są najbardziej przydatne, gdy proces przechwytywania jest uruchomiony w sposób ciągły. Jeśli proces przechwytywania jest uruchamiany zgodnie z harmonogramem, opóźnienie może być wysokie ze względu na opóźnienie między transakcjami zatwierdzonymi w tabeli źródłowej a procesem przechwytywania uruchomionym w zaplanowanym czasie.

Kolejną ważną miarą wydajności procesu przechwytywania jest przepływność. Jest to średnia liczba poleceń na sekundę przetwarzanych podczas każdej sesji. Aby określić przepływność sesji, podziel wartość w kolumnie command_count według wartości w kolumnie czasu trwania. Następujące zapytanie zwraca średnią przepływność dla ostatnich sesji:

SELECT command_count/duration AS [Throughput] FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0

Zbieranie danych próbkowania przy użyciu modułu zbierającego dane

Moduł zbierający dane programu SQL Server umożliwia zbieranie migawek danych z dowolnej tabeli lub dynamicznego widoku zarządzania i tworzenie magazynu danych wydajności. Gdy przechwytywanie zmian danych jest włączone w bazie danych, warto wykonywać migawki widoku sys.dm_cdc_log_scan_sessions i widoku sys.dm_cdc_errors w regularnych odstępach czasu na potrzeby późniejszej analizy. Poniższa procedura konfiguruje moduł zbierający dane na potrzeby zbierania przykładowych danych z widoku zarządzania sys.dm_cdc_log_scan_sessions.

Konfigurowanie zbierania danych

Włącz moduł zbierający dane i skonfiguruj magazyn danych zarządzania. Aby uzyskać więcej informacji, zapoznaj się z sekcją „Zarządzanie Zbieraniem Danych” .

Wykonaj poniższy kod, aby utworzyć niestandardowy moduł zbierający na potrzeby przechwytywania danych zmian.

USE msdb;  

DECLARE @schedule_uid uniqueidentifier;  

-- Collect and upload data every 5 minutes  
SELECT @schedule_uid = (  
SELECT schedule_uid from sysschedules_localserver_view
WHERE name = N'CollectorSchedule_Every_5min')  

DECLARE @collection_set_id int;  

EXEC dbo.sp_syscollector_create_collection_set  
@name = N' CDC Performance Data Collector',  
@schedule_uid = @schedule_uid,
@collection_mode = 0,
@days_until_expiration = 30,
@description = N'This collection set collects CDC metadata',  
@collection_set_id = @collection_set_id output;  

-- Create a collection item using statistics from
-- the change data capture dynamic management view.  
DECLARE @parameters xml;  
DECLARE @collection_item_id int;  

SELECT @parameters = CONVERT(xml,
    N'<TSQLQueryCollector>  
        <Query>  
          <Value>SELECT * FROM sys.dm_cdc_log_scan_sessions</Value>  
          <OutputTable>cdc_log_scan_data</OutputTable>  
        </Query>  
      </TSQLQueryCollector>');  

EXEC dbo.sp_syscollector_create_collection_item  
@collection_set_id = @collection_set_id,  
@collector_type_uid = N'302E93D1-3424-4BE7-AA8E-84813ECF2419',  
@name = ' CDC Performance Data Collector',  
@frequency = 5,
@parameters = @parameters,  
@collection_item_id = @collection_item_id output;

GO

W programie SQL Server Management Studio rozwiń węzeł Management, a następnie rozwiń węzeł Zbieranie danych. Kliknij prawym przyciskiem myszy moduł zbierający dane wydajności usługi CDC, a następnie kliknij Uruchom zestaw zbierania danych.
W magazynie danych skonfigurowanym w kroku 1 znajdź tabelę custom_snapshots.cdc_log_scan_data. Ta tabela zawiera historyczną migawkę danych z sesji skanowania dzienników. Te dane mogą służyć do analizowania opóźnień, przepływności i innych miar wydajności w czasie.

Tryb uaktualniania skryptu

Po zastosowaniu aktualizacji zbiorczych lub dodatków Service Pack do instancji, po ponownym uruchomieniu może ona przejść w tryb aktualizacji skryptu. W tym trybie program SQL Server może uruchomić krok do analizowania i aktualizowania wewnętrznych tabel CDC, co może spowodować ponowne utworzenie obiektów, takich jak indeksy w tabelach przechwytywania. W zależności od ilości danych, ten krok może zająć trochę czasu lub spowodować wysokie użycie dziennika transakcji dla baz danych CDC z włączoną obsługą.

Udostępnij za pośrednictwem