SQLServer_InsertWhereExistsMinimalLogging_00

INSERT WHERE NOT EXISTS i minimalne logowanie

W ostatnim okresie w projekcie który aktualnie realizuję natrafiłem na dosyć ciekawy i specyficzny przypadek. Wiązał się on z procedurą napisaną w TSQL, która ładowała nowe rekordy do tabeli docelowej. Co w tym takiego ciekawego? A no to, że zachowywała się ona w dosyć nieprzewidywalny sposób i razem z kolegą (pozdrawiam Krzysiek!) próbowaliśmy rozszyfrować to dziwne zachowanie. O co w tym wszystkim chodzi? Zapraszam serdecznie do lektury.

Nie wchodząc w szczegółowy opis przypadku biznesowego przejdźmy bez owijania w bawełnę do praktyki. W pierwszej kolejności stworzymy sobie bazę danych o niezwykle kreatywnej nazwie tj. “TEST”. Nie będziemy szczegółowo jej konfigurować ponieważ do naszych celów wystarczą domyślne ustawienia – jedyne co ustawilimy jawnie to RECOVERY MODEL, który w tym przypadku przyjmie wartość SIMPLE:

Po co nam Simple? Ponieważ nasza baza danych jest bazą analityczną dla której kluczowe jest dostarczenie w jak najszybszy sposób danych, a nie wysoka dostępność i możliwość odtworzenia bazy do konkretnego punktu w czasie. W dalszej kolejności stworzymy sobie dwie tabele, które są kopią tabeli Fact.Sale z WideWorldImporters:

W dalszej kolejności uzupełniamy tabelę Source danymi z WideWorldImporters – robimy to dwudziestokrotnie abyśmy mieli trochę wierszy do testowania (pętla powinna wygenerować nieco ponad 4.5mln wierszy):

To co będzie nas interesowało w opisywanym teście to dziennik transakcyjny – chcemy aby nasze operacje były minimalnie logowane co powinno zwiększyć wydajność i zmniejszyć zapotrzebowanie na miejsce na dysku dla dziennika ( o minimalnym logowaniu pisałem już trochę tutaj).

Żeby mieć pewność co do logowania w naszym teście zrobimy shrinka dziennika bazy testowej za pomocą odpowiedniej komendy DBCC:

Następnie sprawdzimy sobie rozmiar dziennika komendą DBCC SQLPERF:

Otrzymany rezultat wygląda następująco:

Tak więc 3.875MB zajmuje plik na dysku z czego wewnętrznie około 8.5% jest zajęte. Pierwszym testem będzie wstawienie danych z tabeli źródłowej do tabeli docelowej z przełącznikiem TABLOCK. Ze względu na fakt, iż:

  • tabela docelowa jest bezindeksową stertą,
  • wyspecyfikowaliśmy TABLOCK,
  • nasza baza testowa ma Recovery Mode ustawiony na SIMPLE.

to nasze polecenie INSERT SELECT powinno być minimalnie logowanie – czy tak się stało? Już sprawdzamy.

Dziennik troszeczkę urósł do 323MB i ze względu na specyfikę działania trybu SIMPLE zajęte miejsce po zatwierdzeniu transakcji może być natychmiast użyte przez kolejne transakcje dlatego taka niska wartość procentowego użycia. W naszym teście będziemy zatem sprawdzać przede wszystkim rozmiar loga, a nie procentową wartość “użytej przestrzeni”.

Powiedzmy, że to jest nasz “baseline” czyli wartość do której będziemy dążyć w naszych testach. Aby uzmysłowić czytelnikom jak dużo miejsca będzie zawierało pełne logowanie wykonam analogiczną operację wstawiania tylko bez wskazówki TABLOCK (oczywiście żeby nie było wątpliwości stworzyłem bazę od nowa i wykonałem DBCC SHRINKFILE, a dla czytelności pominąłem ten kod – będę to robił za każdym razem):

Wynik może niektórych zaskoczyć ponieważ dziennik urósł aż do 2499MB:

Mamy więc do czynienia z pełnym logowaniem. W dalszej kolejności przechodzimy do finalnego testu gdzie będziemy ładować tylko te wiersze, których nie ma w tabeli docelowej, a są w tabeli źródłowej. Aby to sprawdzić użyjemy konstrukcji INSERT SELECT WHERE EXISTS. Kluczem po którym będziemy sprawdzać istnienie wiersza jest Sales Key:

Oczywiście w momencie wykonywania powyższego skryptu tabela docelowa jest pusta ale przy każdym kolejnym uruchomieniu sprawdzenie będzie miało jak najbardziej sens. Po sprawdzeniu dziennika dostaliśmy dosyć zaskakującą informację:

Dostaliśmy pełne logowanie! Okazuje się, że jeżeli w użytej przez nas konstrukcji tabela docelowa istnieje w źródłowym zapytaniu SELECT to nie mamy optymalizacji w postaci minimalnego logowania. Ciekawe prawda? Gdy podejrzymy plan wykonania to wygląda on w następujący sposób:

Zgodnie z oczekiwaniami wywołany został algorytm Right Anti Semi Join przy pomocy operatora Hash Match. W rzeczywistości dbo.Target nie posiada żadnych wierszy, ale mimo to tabela hashowa do porównania została stworzona (estymowana liczba wierszy z tabeli docelowej równa była 1 dlatego też operator złączenia nie został pominięty). W tym miejscu ktoś może powiedzieć, że inna konstrukcja mogłaby być lepsza np. odfiltrowanie poprzez JOIN  – spróbujmy zatem:

Jednakże po planie zapytania możemy zauważyć, że jest to zdecydowanie gorsze podejście bo filtracja następuje bardzo późno tj. po wykonaniu złączenia HASH MATCH czyli po zbudowaniu tabeli hashy:

Nie dość, że straciliśmy na wydajności pod kątem algorytmu użytego do wstawienia danych to jeszcze użycie dziennika wcale nie zostało zoptymalizowane:

Podobne rezultaty otrzymamy w momencie gdy użyjemy takich konstrukcji jak NOT IN, którego opis w tym miejscu pominę. Ciekawą alternatywą może być również użycie EXCEPT do wyekstrahowania ze źródła tylko tych wierszy, których nie ma w tabeli docelowej:

Problem z tym podejściem jest taki, że nie zawsze jest tak, że chcemy sprawdzać to czy całe wiersze różnią się między sobą, a jedynie kolumny kluczowe. Dla niektórych przypadków może to jednak być jakieś wyjście – plan też wygląda interesująco:

Operacja już nieco “cięższa” bo mamy wykonywanie na wielu wątkach (widać to we właściwościom, a na załączonym obrazu można to dostrzec dzięki występowaniu operatora Parallelism i “strzałkach” przy poszczególnych operatorach). Dodatkowo widzimy, że tabelka hashy jest budowana dwukrotnie co może mieć wpływ na wydajność. Gdy spojrzymy na dziennik transakcyjny to ciekawostką jest zdecydowanie zoptymalizowane jego użycie:

Ostatnią konstrukcją jaką chciałbym przetestować jest niesławny MERGE (który często powoduje dużo problemów jednakże na ten moment pozwolę sobie ten fakt pominąć). Jego zapis wygląda następująco:

Mimo spełnienia warunków związanych z minimalnym logowaniem to z tej perspektywy nie wygląda to najlepiej:

Plan raczej niczego nam tutaj nie rozwiąże ale gwoli ścisłości go umieszczam:

Dla nieprzekonanych mam jeszcze zapytanie odpytujące funkcję sys.fn_dblog sprawdzające jakie operacje znalazły się w dzienniku:

Operacje minimalnie logowane z definicji nie powinny umieszczać w dzienniku informacji o wstawieniu wierszy, a jak widać powyżej tak właśnie się stało. Jak to możliwe, że nie mamy minimalnego logowania mimo, że scenariusz jest naprawdę prosty i spełniliśmy wszystkie wymogi? A no właśnie. W moim przekonaniu aby osiągnąć pożądany stan użycia dziennika zawsze powinniśmy testować bo nie zawsze złote zasady działają. W tym przypadku nie najgorsze rezultaty osiągnęliśmy używając EXCEPT jednak nie jest on rozwiązaniem w każdym przypadku. Oczywiście w ramach procesu ETL możemy w jakiś sposób upewnić się, że wstawiamy tylko nowe dane i całą logikę zawrzeć właśnie w narzędziu za to odpowiedzialnym. Jeśli dziennik nie jest dla nas kluczowy to możemy również wrzucać dane w batchach dzięki czemu poszczególne porcje zaczną nadpisywać go od początku i nie urośnie on do niebotycznych rozmiarów. W przypadku jednak INSERT SELECT nie mamy możliwości wyspecyfikowania rozmiaru batcha i wszystko ładowane jest niejako jako pojedynczy batch. Rozwiązań tego problemu z całą pewnością jest kilka jak np. partition switching albo użycie tabelki stagingowej – postaram się to pokazać używając tej drugiej techniki:

Tabela może być standardowa lub tymczasowa – ja użyłem tej drugiej opcji. Następnie w NOT EXISTS użyłem tego właśnie obiektu tymczasowego:

Wiecie co? Rezultat jest dokładnie taki jakbym tego oczekiwał:

Taką tabelkę tymczasową możemy odpowiednio podrasować dodając np. indeks i kompresję jednakże to już pozostawiam poszczególnym implementacjom. Tak jak wspomniałem rozwiązań jest wiele jednakże najważniejsze dla mnie było pokazanie dosyć ciekawego przypadku który nie jest do końca oczywisty. Pozdrawiam!

1 Comment

Leave a Reply

Your email address will not be published. Required fields are marked *