Wykrywanie zmian w tabeli SQL Server

W mojej aplikacji z bazą danych działającą na SQL Server 2012 mam zadanie (zaplanowane zadanie), które okresowo wykonuje drogie zapytanie i zapisuje wyniki w tabeli, do której aplikacja może później zapytać.

Idealnie chciałbym uruchomić to drogie zapytanie tylko wtedy, gdy coś się zmieniło od ostatniego wykonania zapytania. Ponieważ tabele źródłowe są bardzo duże, nie mogę po prostu wybrać sumy kontrolnej dla wszystkich kandydujących kolumn lub czegoś podobnego.

Mam następujące pomysły:

• Jawnie zapisz ostatnią zmienioną sygnaturę czasową, flagę „musi być zapytaniami” lub coś takiego w tabeli śledzenia, ilekroć zmieniam coś w tabeli źródłowej.
• Użyj wyzwalacza, aby zrobić to samo.

Jednak naprawdę chciałbym wiedzieć, czy istnieje lekki sposób na wykrycie zmian w tabeli bez wyraźnego śledzenia zapisów. Czy mogę na przykład uzyskać „bieżący” ROWVERSIONstół lub coś takiego?

Nie, nie ma żadnych. Każde śledzenie typu „ostatnia aktualizacja o” miałoby poważny problem z wydajnością, ponieważ wszystkie aktualizacje ze wszystkich transakcji próbowałyby zaktualizować jeden rekord śledzący „ostatnią aktualizację o”. To skutecznie oznaczać tylko jedna transakcja może zaktualizować tabelę w dowolnym momencie, a wszystkie inne transakcje muszą czekać na pierwszego do popełnienia . Pełna serializacja. Liczba administratorów / deweloperów gotowych znieść taki spadek wydajności tylko po to, aby wiedzieć, kiedy nastąpiła ostatnia aktualizacja, jest prawdopodobnie niewielka.

Więc jesteś osierocony, aby obsłużyć go za pomocą niestandardowego kodu. Oznacza to wyzwalacze, ponieważ alternatywa (wykrywanie z rejestrów dziennika) jest przywilejem zarezerwowanym tylko dla transakcyjnej replikacji (lub jest to alter ego CDC ). Pamiętaj, że jeśli spróbujesz śledzić go za pomocą kolumny „ostatnia aktualizacja o”, napotkasz dokładnie wspomniany powyżej problem serializacji. Jeśli współbieżność aktualizacji jest ważna, należy użyć mechanizmu kolejki (wyzwalacz używa INSERT, a następnie proces agreguje wstawione wartości w celu sformułowania „ostatniej aktualizacji o”). Nie próbuj oszukiwać za pomocą jakiegoś „sprytnego” rozwiązania, takiego jak skradanie się do bieżącej tożsamości lub wyszukiwanie sys.dm_db_index_usage_stats . A także kolumna „updated_at” na rekord, tak jak znaczniki czasu Railsów,

Czy istnieje jakaś „lekka” alternatywa? Tak naprawdę jest jeden, ale trudno powiedzieć, czy będzie on dla ciebie działał i czy trudno jest go poprawnie wykonać: Powiadomienia o zapytaniach . Powiadomienie zapytania robi dokładnie to, ustawi powiadomienie, jeśli jakieś dane ulegną zmianie i konieczne będzie odświeżenie zapytania. Chociaż większość programistów zna tylko jego inkarnację .Net jako SqlDependency, powiadomienia kwerendy można używać jako długo działający, utrwalony mechanizm wykrywania zmian danych. W porównaniu z prawdziwym śledzeniem zmian, będzie on bardzo lekki, a jego semantyka jest bliższa twoim potrzebom (coś, cokolwiek , zmieniło się, więc musisz ponownie uruchomić zapytanie).

Ale ostatecznie, na twoim miejscu, naprawdę przemyślę ponownie moje założenia i wrócę do tablicy kreślarskiej. Być może możesz użyć wysyłki dziennika lub replikacji, aby skonfigurować bazę danych raportowania na innym serwerze. Czytam między wierszami, że potrzebujesz odpowiedniej linii ETL i hurtowni danych analitycznych ...

Wygląda na to, że spóźniłem się o dwa lata, ale jest naprawdę lekki sposób na zrobienie tego, o co prosisz.

Istnieją dwa mechanizmy SQL Server, które mogą ci pomóc. Twoje najlepsze rozwiązanie może być hybrydą obu.

Zmień śledzenie . SQL Server może obserwować określone tabele, rejestrować tylko te wiersze, które uległy zmianie (według ich wartości klucza podstawowego) i jaka to była zmiana (Wstaw, Aktualizuj lub Usuń). Po skonfigurowaniu wykrywania zmian w zestawie tabel, lekkie zapytanie może stwierdzić, czy w tabeli zostały wprowadzone jakieś zmiany od czasu ostatniego sprawdzenia. Narzut jest mniej więcej taki sam, jak utrzymanie dodatkowego prostego indeksu.

Konwersja / znacznik czasu . Jest to 8-bajtowy typ kolumny varbinary (rzutowany na BigInt), który jest zwiększany, ma szerokość bazy danych, za każdym razem, gdy wiersz zawierający jeden jest wstawiany lub aktualizowany (nie pomaga w usuwaniu). Po zaindeksowaniu tych kolumn można łatwo stwierdzić, czy dane wiersza uległy zmianie, porównując wartość MAX (znacznik czasu) z jej wartością od czasu ostatniej oceny. Ponieważ wartość rośnie monotonicznie, dałoby to wiarygodne wskazanie, że dane uległy zmianie, jeśli nowa wartość jest większa niż podczas ostatniej kontroli.

Jeśli źródło jest wstawiane, podaj mu IDENTITYkolumnę. Podczas transferu danych rejestrujesz najwyższą zarejestrowaną wartość. Podczas następnego transferu potrzebne jest tylko zapytanie o wartości większe niż zarejestrowane podczas poprzedniego transferu. Robimy to w celu przeniesienia rekordów dziennika do hurtowni danych.

W przypadku wierszy z możliwością aktualizacji dodaj flagę „brudne”. Będzie miał trzy wartości - czysty, brudny i usunięty. Codzienne zapytania będą musiały pomijać wiersze z flagą ustawioną na „usunięte”. Będzie to kosztowne w utrzymaniu, testowaniu i czasie pracy. Po dużym zapytaniu wspominasz, że wszystkie wiersze oznaczone do usunięcia muszą zostać usunięte, a flaga zresetowana dla wszystkich pozostałych. To nie będzie dobrze skalować.

Lżejszą alternatywą dla przechwytywania zmian danych jest śledzenie zmian . Nie powie ci, jakie wartości się zmieniły, tylko, że wiersz zmienił się od ostatniego zapytania. Wbudowane funkcje ułatwiają wyszukiwanie zmienionych wartości i zarządzanie śledzeniem. Odnieśliśmy sukces przy użyciu CT do przetwarzania około 100 000 zmian dziennie w tabeli 100 000 000 wierszy.

Powiadomienia zapytań działają nadal z wyższą dźwignią - na poziomie zestawu wyników. Koncepcyjnie przypomina definiowanie widoku. Jeśli SQL Server wykryje, że dowolny wiersz zwrócony przez ten widok został zmieniony, uruchamia komunikat do aplikacji. Nic nie wskazuje, ile wierszy się zmieniło lub które kolumny. Jest tylko prosty komunikat „coś się stało”. Zapytanie i reakcja należy do aplikacji. Praktycznie jest to o wiele bardziej skomplikowane, jak można sobie wyobrazić. Istnieją ograniczenia dotyczące sposobu definiowania zapytania, a powiadomienia mogą być uruchamiane w przypadku warunków innych niż zmienione dane. Po uruchomieniu powiadomienia zostanie ono usunięte. Jeśli później nastąpi dalsza aktywność będąca przedmiotem zainteresowania, nie zostanie wysłana żadna dodatkowa wiadomość.

W kontekście pytania PO, QN będzie miał tę zaletę, że będzie wymagał niewielkiego narzutu do konfiguracji i będzie miał niewielkie koszty w czasie wykonywania. Ustanowienie i utrzymanie rygorystycznego reżimu „subskrybuj wiadomość-reaguj” może być znaczącym wysiłkiem. Ponieważ tabela danych jest duża, prawdopodobnie będą w niej często dokonywane zmiany, co oznacza, że ​​powiadomienie może zostać uruchomione w większości cykli przetwarzania. Ponieważ nic nie wskazuje na to, co zmieniło przetwarzanie przyrostowe delt, nie będzie możliwe, podobnie jak w przypadku CT lub CDC. Narzut związany z fałszywym wyzwalaniem jest męczący, ale nawet w najgorszym przypadku kosztowne zapytanie nie musi być uruchamiane częściej niż obecnie.

SqlTableDependency

SqlTableDependency to komponent implementacyjny wysokiego poziomu, który umożliwia dostęp do powiadomień zawierających wartości rekordów tabeli w bazie danych SQL Server.

SqlTableDependency to ogólny komponent C # używany do otrzymywania powiadomień o zmianie zawartości określonej tabeli bazy danych.

Jaka jest różnica z .NET SqlDepenency?

Zasadniczo główna różnica polega na tym, że SqlTableDependency wysyła zdarzenia zawierające wartości dla wstawionego, zmienionego lub usuniętego rekordu, a także operacji DML (wstawianie / usuwanie / aktualizowanie) wykonywanej na tabeli: SqlDepenency nie mówi, jakie dane zostały zmienione na tabela bazy danych, mówią tylko, że coś się zmieniło.

Zapraszamy do obejrzenia projektu GitHub .

Jeśli oczekiwane aktualizacje wpływają na indeks (i tylko wtedy), można użyć tabeli systemowej sys.dm_db_index_usage_statsdo wykrycia ostatniej aktualizacji indeksu w danej tabeli. Użyłbyś tego last_user_updatepola.

Na przykład, aby uzyskać najnowsze aktualizacje tabel:

select
    object_name(object_id) as OBJ_NAME, *
from
    sys.dm_db_index_usage_stats
where
    database_id = db_id(db_name())
order by
    dm_db_index_usage_stats.last_user_update desc

Lub, aby sprawdzić, czy konkretna tabela została zmieniona od określonej daty:

select
    case when count(distinct object_id) > 0 then 1 else 0 end as IS_CHANGED
from
    sys.dm_db_index_usage_stats
where
    database_id = db_id(db_name())
    and object_id = object_id('MY_TABLE_NAME')
    and last_user_update > '2016-02-18'