SQL Server -indeks kolumnowy + partycjonowanie

SQLServer_Columnstore_Partitioning_00

W świecie hurtowni danych niezwykle istotnym czynnikiem jest wydajność zarówno wstawiania danych jak i ich odczytu. W SQL Server 2012 wprowadził niezwykle istotną konstrukcję, która pomaga nam osiągnąć złoty środek jeśli chodzi o INSERT jak i SELECT jaką jest bez wątpienia indeks kolumnowy czyli COLUMNSTORE. Jeszcze wcześniej wprowadzona w naszym ulubionym silniku bazodanowym została technologia pozwalająca partycjonować dane. Na przestrzeni lat obie technologie stały się niezwykle istotne i znalazły szerokie zastosowania w hurtowaniach danych i nie tylko. Czy mogą one współistnieć razem i przy okazji się uzupełniać? Oczywiście, że tak! W ramach niniejszego artykułu chciałbym pokazać wybrany przeze mnie scenariusz użycia obu technologii – zapraszam do lektury!

Na samym wstępie kilka słów niezbędnej teorii o indeksie kolumnowym, którego schemat możecie zobaczyć na poniższej grafice.

Dane w indeksie kolumnowym składowane są w odróżnieniu od standardowego indeksu opartego o B-Drzewo kolumnowo. Oznacza to, że jeżeli w naszych zapytaniach odpytujących wybierzemy kolumnę c1 oraz c2 to pozostałe kolumny nie zostaną pobrane z dysku. Mechanizm ten nazywamy eliminacją kolumn. Z drugiej zaś strony w ramach pojedynczej kolumny dane są przechowywane w pewnego rodzaju “porcjach” nazwanych segmentami. Pojedynczy segment może mieć od 102 400 do 1 048 576 wierszy. Warto w tym miejscu zwrócić uwagę na to, że im większy nasz indeks tym lepsze efekty kompresji jesteśmy w stanie uzyskać (ze względu na fakt, że dane kompresowane są per segment). Jeżeli nieosiągniemy minimalnego pułapu to nasze dane nie utworzą segmentu przez co nie będziemy czerpać krorzyści z kompresji i eliminacji. Ponadto gdy wstawiamy dane poniżej progu to użycie dziennika transakcyjnego nie jest już zaoptymalizowane pod kątem wstawiania danych. Przechodząc dalej powiedzmy, że każdy segment jest opisany metadanymi, upraszczając możemy powiedzieć, że mamy informacje o minimalnej i maksymalnej wartości jaka znajduje się w segmencie. Wyobraźmy sobie sytuację gdzie mamy w kolumnie daty, skąd wiadomo które daty są w których segmentach? Właśnie na podstawie metadanych!

Poniżej możecie zauważyć przykładową konfigurację gdzie zakładamy, że kolumna c1 zawiera datę i tak:

  • pierwszy segment zawiera dane od 1 czerwca 2019 do 30 czerwca 2019,
  • drugi segment zawiera dane od 1 lipca 2019 do 31 lipca 2019,
  • trzeci segment zawiera dane od 1 sierpnia 2019 do 31 sierpnia 2019.

Odpowiadające sobie segmenty z różnych kolumn tworzą tzw. grupę wierszy. Porządek danych wewnątrz Columnstore’a determinuje jak będzie działać eliminacja segmentów dla poszczególnych kolumn. Oczywiście jeśli dane będą uporządkowane po kolumnie z datą to prawdopodobnie dla tej właśnie kolumny uzyskamy najlepszą eliminację, a nieco gorszą dla innych kolumn. Z tego też powodu porządek danych w indeksie powinien być przemyślany tak aby uzyskać najlepsza eliminację dla najczęściej odpytywanej kolumny. Do tego jeszcze wrócimy za chwilę, na ten moment powiedzmy sobie co się stanie gdy naszą tabelę z indeksem kolumnowym będziemy chcieli partycjonować.

Na poniższej grafice przedstawiłem przykładowy, partycjonowany indeks kolumnowy:

Każda partycja tak naprawdę dzieli nam indeks na osobne struktury. Na powyższej grafice możecie zauważyć, że klucz partycjonujący podzielił Columnstore na cztery osobne wystąpienia indeksu. Na obrazku można również zauważyć dwa klucze umownie przeze mnie nazwane Loading Key oraz Optimization Key. Pierwszy z nich oznacza kolumnę (lub kilka kolumn) po której przeładowujemy nasze dane. Zazwyczaj mamy jakiś punkt zaczepienia po którym przeładowujemy dane najczęściej jest to jakiś wyznacznik czasu jak chociazby rok, miesiąc, dzień itp. Biznesowo gdy mamy pewność, że w tabeli faktów może zmienić się jedynie ostatni rok kalendarzowy naszych danych to przeładowanie poprzednich lat nie ma żadnego sensu i na tym opiera się nasza strategia przeładowań w przedstawianym scenariuszu.

Optimization Key to klucz porządku danych wewnątrz Columnstore, który zapewnia eliminację segmentów. Jest to kolumna lub grupa kolumn najczęściej używanych do odpytywania tej tabeli przy założeniu, że dane mają być uporządkowane od atrybutu najbardziej selektywnego. Może to być np. numer transakcji, klienta bądź cokolwiek innego co jest dostatecznie selektywne i jednocześnie najczęściej wybierane w zapytaniach odpytujących. Warto wspomnieć, że przy tworzeniu Columnstore nie wskazujemy sortowania, ale oczywiście również na to mamy sposób. Przejdźmy zatem do demonstracji i przedstawmy kilka linijek kodu.

Na początku stwórzmy sobie testową bazę danych. Sam kod nie wyróżnia się niczym specjalnym, jednakże warto zwrócić uwagę, że nasza baza danych jest w trybie Simple – umożliwiamy zatem osągnięcie minimalnego logowania:

W kolejnym kroku przejdziemy do partycjonowania naszej tabeli. Stwórzmy zatem funkcję partycjonującą po roku:

Mając już funkcję przejdźmy do stworzenia schematu partycjonującego, który w tym konkretnym przypadku przypiszemy wszystkie partycje do jednej grupy plików (nic nie stoi na przeszkodzie żeby oczywiście rozmieścić pliki na różnych dyskach – będzie to oczywiście wartość dodana).

Mając już dostępne struktury pod partycjonowanie stwórzmy naszą tabelę testową, która będzie partycjonowaną stertą:

Powyższa tabela ma taką samą strukturę jak tabela z przykładowej bazy WideWorldImportersDW i na jej podstawie ją zasilimy ( jest to specjalna wersja tabeli w WideWorldImportersDw która zawiera nieco więcej wierszy – możecie to zrobić wstawiając te same dane kilka razy lub użyć techniki przedstawionej np. tutaj):

Powyższej użyłem wskazówki TABLOCK aby osiągnąć minimalne logowanie, a co za tym idzie stosunkowo dobrą wydajność(pisałem już o tym w ostatnim czasie, artykuł znajdziecie tutaj). W następnym kroku zrobimy coś co jest kluczowe w stosunku do indeksu kolumnowego czyli posortujemy znajdujące się tam dane – zrobimy to tworząc indeks zgrupowany na najczęściej używanej kolumnie w zapytaniach odpytujących (wspomniany wcześniej Optimization Key):

Następnie jak już mamy dostępne uporządkowane dane to możemy indeks zgrupowany na kolumnowy:

Wyłączyliśmy wielowątkowość używając hinta MAXDOP 1 ze względu na fakt, iż wiele równoległych wątków mogłoby nam “pomieszać” uporządkowane już dane. Wszystkie powyższe operacje oczywiście trochę trwają, ale jest to inicjalne ładowanie i dla 30 milionów danych na moim laptopie zajęło to kilkanaście minut. Oczywistym jest fakt, że wszystko zależy od logiki zawartej w ładowaniu, wolumenu i dostępnych zasobów sprzętowych, jednakże ładowanie inicjalne charakteryzuje się tym, że robimy go tylko raz 🙂

Jak już mamy wszystko gotowe możemy podejrzeć metadane, zacznijmy od partycji:

Jak można zauważyć na powyższym zrzucie ekranowym, powstało 5 partycji z czego ostatnia jest pusta(otwarta). Jako algorytm kompresji wskazany został COLUMNSTORE co jest oczywiście zgodne z naszym założeniem. Przejdźmy dalej i podejrzyjmy metadane związane z grupami wierszy wewnątrz Columnsore:

W tym przypadku zwróćcie uwagę na kolumnę partition_number, row_group_id oraz total rows. Mamy grupy wierszy per partycja i każda grupa wierszy ma maksymalny rozmiar oprócz ostatniej, która zawiera wszystkie wiersze które “zostały” w danej partycji. najważniejsze jest to aby dane w każdej grupie wierszy miały status COMPRESSED to znaczy że było ich na tyle dużo, że zostały skompresowane do grupy wierszy, a nie zostały w tzw. Delta store. Powyższy zrzut ekranowy potwierdza wcześniej przedstawioną teorię mówiącą o tym, że mamy “tyle columnstore ile partycji”.

No dobrze ale jakie mamy w tym momencie zalety takiej struktury? Zobaczmy to na własne oczy! Dla testu uruchomimy nasze zapytanie, dodatkowo włączmy plan zapytania oraz statystyki IO:

Plan zapytania nie wygląda jakoś szczególnie ale zawiera bardzo istotne informacje:

Widzimy, że odczytana była tylko jedna partycja o numerze 2. Miała zatem miejsce eliminacja partycji o której pisałem już wcześniej (artykuł znajdziecie tutaj). Ale to jeszcze nie wszystko! Gdy zajrzymy na zakładkę Messages zobaczymy następująco informację:

O co w tym wszystkim chodzi? Na podstawie metadanych najpierw zostały wyeliminowane partycje (na podstawie zapisie w naszym zapytaniu WHERE [Order Date Key] BETWEEN ‘20140101’ AND ‘20141230’), a następnie w tej partycji która pozostała w ramach Columnstore wyeliminowane zostały odpowiednie segmenty (osiem z nich, odczytany został tylko jeden – spowodowane zostało to zapisem [Customer Key]=1). Czyli jak możemy zauważyć, tylko na podstawie metadanych odrzuciliśmy znaczną część wierszy i odczytaliśmy tylko jeden segment! Jak długo ten odczyt trwał? Dosyć szybko bo w 1-2 sekundy zwrócone zostało 16 tysięcy wierszy z 30 milionów znajdujących się w tabeli. Efekt możecie zobaczyć poniżej:

Nasze dane zostały zasilone inicjalnie, jak zatem należy je doładowywać w sposób przyrostowy? Pokażmy to na konkretnym przykładzie. Na wstępie stwórzmy sobie tabelę przejściową (stagingową) która będzie przechowywała dane przyrostowe:

Powyższa tabela ma taką samą strukturę jak tabela docelowa – jest partycjonowana ale nie posiada indeksu kolumnowego. Mamy zatem do czynienia z sytuacją podobną do ładowania inicjalnego. Różnicą tutaj jest to, że ładujemy dane przyrostowe, które w moim przypadku ograniczą się do wszystkich danych z roku 2016:

W tym miejscu możemy zaimplementować dowolną logikę biznesową jaką mam potrzebę wdrożyć. W tym konkretnym przypadku mój “przyrost” zawiera niecałe 4 miliony wierszy. Dalsze kroki już znamy bo są analogiczne do ładowania incjalnego i sprowadzają się one do:

1) Stworzenia indeksu klastrowanego na kolumnie wybranej jako Optimization Key:

2) Zastąpienie wcześniejszego indeksu indeksem kolumnowym z wykorzystaniem MAXDOP=1

Kiedy już mamy do dyspozycji tabelę z przyrostem oraz tabelę docelową o tej samej strukturze możemy przejść do rzeczy i wykonać podmianę partycji czyli partition switching (artykuł tutaj).

Przy tego typu operacji partycja docelowa musi być pusta, my mamy w stagingu dane za rok 2016 skąd mamy wiedzieć która partycja docelowa powinna być wyczyszczona? Wystarczy użyć specjalnej konstrukcji którą przedstawiłem już w artykule o partition switchingu:

Z tego zapytania wynika nam, że partycja z danymi za rok 2016 to partycja numer 4:

Wyczyśćmy ją zatem komendą TRUNCATE:

Teraz nie pozostaje nam nic innego jak podejrzeć struktury tabeli stagingowej oraz docelowej:

W rezultacie tych zapytań dostaniemy pełną informację na temat metadanych partycji w tabelach które nas interesują:

Nie pozostaje nam nic innego jak wykonać switch obu partycji:

Ze względu na fakt, iż powyższa operacja jest operacją na metadanych wykonuje się naprawdę szybko. Nic teraz nie stoi na przeszkodzie aby sprawdzić raz jeszcze metadane:

Wszystko działa tak jak należy i dane znalazły się w tabeli docelowej. Wspomnę tylko, że nasz przyrost powinien zawierać minimum tyle wierszy ile jest wymagane aby utworzyć skompresowaną grupę wierszy czyli 102 400. Jeżeli nie jesteśmy osiągnąć tego pułapu w naszym przyroście to zwiększmy zakres aby go osiągnąć czyli np. przesuńmy zakres przyrostu z dziennego na tygodniowy itd. Dzięki temu podejściu powinniśmy osiągnąć najlepsze efekty. Tutaj może pojawić się również pytanie jak utrzymywać indeks kolumnowy w takiej konfiguracji. Odpowiedź jest prosta – jeżeli zadbamy o to aby nasze grupy wierszy były odpowiednio duże to już mamy dobrą strukturę. Jedyne o co możemy zadbać to fakt, że możemy przebudowywać grupy wierszy jak już zamknięmy okres przeładownia (np. koniec miesiąca) tak aby miały maksymalny rozmiar przez co lepszą kompresję jednakże wszystko zależy od konkretnego przypadku i wolumenu danych. Mam nadzieję, że ten artykuł Wam się spodobał i pokazał jak w prosty sposób można radzić sobie z dużymi zbiorami danych w hurtowniach danych łącząc wydajność wstawiania danych jak i ich odczytu przy pomocy indeksu kolumnowego i partycjonowania.

Leave a Comment

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