Partition switching w SQL Server

W przypadku hurtowni danych podczas ładowań danych do tabeli faktów bardzo często zdarza się, że chcemy zastąpić dane za określony okres (np. miesiąc)  świeżymi danymi z systemów źródłowych. Może to być wykonane na wiele sposobów, dla przykładu możemy usunąć z tabeli ostatni miesiąc komendą DELETE i wstawić nowe dane. Wiąże się to z wieloma niedogodnościami, po pierwsze operacja usunięcia będzie w pełni logowana, po drugie samo wyszukanie danych do usunięcia może być problematyczne. Dodatkowo możemy mieć problemy związane z lockami i blokadami. Powszechnie znanym faktem jest to, że jednymi z najszybszych operacji w SQL Server są operacje na metadanych, które oprócz tego, że nie “dotykają” danych są w optymalny sposób logowane w dzienniku transakcji. Czy możemy wykorzystać jakiś mechanizm tego typu podczas ładowania danych? Oczywiście, że tak! Odpowiedzią jest partition switching o którym chciałem parę słów napisać w ramach niniejszego artykułu.

Czym jest partition switching? Jest to nic innego jak podmiana partycji – przykładowy rysunek koncepcyjny możecie znaleźć poniżej:

Do tabeli A ładowane są dane za rok 2017 – składa się ona z jednej partycji przeznaczonej właśnie na dane z roku 2017. Po załadowaniu danych następuje przełączenie metadanych tj. metadane z tabeli B nie wskazują już pustej partycji tylko partycję wypełnioną danymi za rok 2017:

Oczywiście fizycznie nie występuje żaden ruch danych dzięki czemu ta operacja jest bardzo szybka, żeby nie powiedzieć natychmiastowa. Abu móc w ten sposób przełączać partycje musi być spełnione kilka wymogów – najważniejsze z nich to:

  • docelowa partycja musi być pusta (nie ma możliwości użycia tego mechanizmu jeśli tabela docelowa zawiera dane)
  • tabela/partycja źródłowa muszą być partycjonowane po tym samym kluczu i mieć tą samą strukturę
  • tabela/partycja źródłowa muszą znajdować się w tej samej grupie plików

Tak jak wspomniałem nie wszystkie wymogi jednakże na ten moment to nam wystarczy. Sprawdźmy omawiany mechanizm na konkretnym przykładzie – stwórzmy sobie dwie tabele będące kopią wybranej tabeli. Nazwijmy je kolejno Source oraz Target:

Następnie stwórzmy schemat oraz funkcję partycjonującą:

Kiedy mamy już składowe nie pozostaje nam nic innego jak stworzyć na obu tabelach indeks klastrowany ze wskazaniem na schemat partycjonujący:

W następnym kroku odpytajmy metadane naszych partycji aby zobaczyć podsumowanie oraz numery partycji przypisanych do konkretnych lat po, których partycjonowaliśmy (oczywiście część zawartą w cte można wyliczyć dynamicznie jednakże póki co wartości statyczne wyliczone w taki sposób nam wystarczą):

Wynik naszego zapytania przedstawia się następująco:

Mamy w tym momencie dokładnie dwie takie same tabele. Przypuśćmy, że z tabeli docelowej (target) chcemy się pozbyć danych z roku 2016. Obecnie w SQL Server 2016 mamy do dyspozycji specjalną odmianę polecenia TRUNCATE TABLE, która potrafi wyczyścić dane pojedynczej partycji, osiągniemy to w następujący sposób:

Numer 5 w powyższym zapytaniu to nic innego jak numer partycji. Odpytajmy nasze metadane raz jeszcze i przekonajmy się czy nasza partycja została wyczyszczona:

Póki co wszystko działa zgodnie z planem. W tym miejscu może pojawić się pytanie co gdy nie mamy SQL Server 2016 i polecenia TRUNCATE czyszczącego partycje? Mamy parę tricków, które dokonają dokładnie tego samego (jeden z nich został opisany przez Kamila Nowińskiego tutaj). Przypuśćmy, że chcemy teraz przełączyć partycję z danymi za rok 2016 z tabeli Source do tabeli Target, osiągniemy to w bardzo łatwy i szybki sposób następującym poleceniem:

Zapytanie wykonało się błyskawicznie – sprawdźmy metadane:

Powyższy zrzut w bardzo fajny sposób obrazuje, że właśnie dokonaliśmy podmiany metadanych. Partycje jak i dane pozostały na swoim miejscu, a zmieniło się jedynie ich przyporządkowanie do tabel.

Oczywiście powyższy scenariusz nie jest jedynym możliwym. Możemy również podmieniać tabelę bez jawnego partycjonowania ze względu na fakt, iż tak naprawdę każda tabela ma minimum jedną partycję. Sprawdźmy to na kolejnym przykładzie – na początku tworzymy dwie tabele, jedna z nich będzie pusta (dbo.Target2), a druga będzie zawierała określone dane (dbo.Source2):

Po wykonaniu powyższych zapytań podejrzyjmy metadane obu tabel:

Potwierdziła się nasza teoria, że każda tabela posiada minimum jedną partycję. Teraz sprawdźmy czy uda nam się podmienić partycje pomiędzy oboma tabelami. W tym przypadku nie musimy podawać konkretnych numerów partycji (chociaż oczywiście możemy) ponieważ dla SQL Servera jest to oczywiste:

Cała operacja przebiegła pomyślnie:

Myślę, że w tym miejscu warto przedstawić kilka scenariuszy, w których możliwe jest przełączanie partycji. Tak więc możemy przełączać z:

  • tabeli niepartycjonowanej do tabeli partycjonowanej
  • tabeli partycjonowanej do tabeli niepartycjonowanej
  • tabeli partycjonowanej do tabeli partycjonowanej
  • tabeli niepartycjonowanej do tabeli niepartycjonowanej

Czyli mamy całkiem spore pole do popisu i pewną swobodę w definiowaniu naszych struktur ponieważ każdy scenariusz jest możliwy do implementacji jeśli tylko wymienione wcześniej wymagania zostały spełnione.

Dodatkowo warto wspomnieć o tym, że podobnie jak operację TRUNCATE, tak również PARTITION SWITCTH  możemy kontrolować w transakcji i w razie potrzeby wycofać:

Zgodnie z tym co zakładaliśmy operację przełączania można bez żadnego problemu wycofać. Właściwość ta jest bardzo ważna gdyż dzięki niej możemy wycofać wszystkie zmiany jakie wprowadziliśmy w hurtowni danych jeśli wystąpił błąd itp. Sam osobiście spotkałem się z systemem gdzie właśnie wycofywanie partition switchingu było podstawą utrzymania integralności danych w hurtowni. Czy można używać  tego mechanizmu w systemach OLTP? Czy ma to jakiś sens? Oczywiście może mieć sens jednakże warto pamiętać, że podczas przełączania partycji zarówno na tabeli źródłowej jak i docelowej zakładany jest schema-modify lock, który w przypadku długo trwających transakcji może być problemem i zwiększyć czasy oczekiwania na rezultat.

To by było na tyle jeśli chodzi o opisywany mechanizm. Całość nie powinna sprawić problemu, wystarczy nawet podstawowa wiedza na ten temat aby sprawnie umieć posługiwać się przełączaniem partycji. Jedyne co warto powiedzieć w tym miejscu to fakt, że najważniejsza w przypadku implementacji jakiegokolwiek mechanizmu związanego z partycjami jest strategia, którą chcemy użyć it jej znalezienie jest najtrudniejszym czynnikiem.

Adrian Chodkowski
Follow me

Adrian Chodkowski

SQL geek, Data enthusiast, Consultant & Developer
Adrian Chodkowski
Follow me

2 Comments

  1. Norbert Kulski

    W “zamierzchłych czasach” mechanizm ten był kluczową techniką umożliwiającą pobicie pewnego rekordu 😉
    https://blogs.msdn.microsoft.com/sqlperf/2008/02/27/etl-world-record/
    i trochę więcej detali:
    https://technet.microsoft.com/en-us/library/dd537533(v=sql.100).aspx

    Kurcze, trochę już czasu minęło, wtedy kiedy MSSQL 2000 miał się całkiem dobrze, taki news robił wrażenie 🙂

    Reply
    1. Adrian Chodkowski (Post author)

      To prawda – robił wrażenie i myślę, że robi nadal 1TB poniżej 30 minut to świetny wynik:) Mechanizm już ma swoje na karku, a i tak jest jedną z najlepszych technik ETL jakie istnieją w SQL Server.

      Reply

Leave a Comment

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