SQL Server 2016 – usprawnienia In-memory OLTP

SQLServer2016_InmemoryOLTPImprovements000

Seria Poznaj SQL Server 2016!

Dziś powiemy sobie parę słów na temat głównego produktu wprowadzonego w SQL Server 2014 tj. In-memory OLTP aka Hekaton aka XTP (eXtreme Transaction Processing), a właściwie o ulepszeniach tej technologii dostępnych w SQL Server 2016. Funkcjonalność ta to nic innego jak kompilowane do kodu maszynowego tabele, procedury składowane, funkcje itp. przechowywane w pamięci operacyjnej. Struktury tego typu dawały bardzo duży wzrost wydajności ze względu na zmniejszenie wpływu operacji IO związanych z pobieraniem danych z dysku twardego, nowe sposoby indeksacji, optymalizacji logowania operacji oraz całkowicie optymistyczny model współbieżności tzn. brak struktur typu lock czy latch. Jednakże pierwsze wcielenie tego mechanizmu było bardzo ograniczone i w moim odczuciu znalazło zastosowanie tylko w bardzo specyficznych warunkach. Sytuacja zmieniła się w wersji SQL Server 2016 gdzie bardzo wiele limitów zostało usuniętych o czym chciałbym dziś parę słów napisać.

Na samym początku warto wspomnieć o tym, iż Microsoft przyznaje, że niektóre struktury wewnętrzne przetwarzania zapytań zostały zoptymalizowane, dzięki czemu wydajność bazy danych powinna wzrosnąć tylko poprzez poprzez migrację do SQL Server 2016. Ponadto to w wersji 2014 rekomendowana wielkość pamięci dostępnej dla memory-optimized tables (bo tak nazywały się tabele w hekaton) to 256GB. Limit ten nie był sztywnym ograniczeniem, a jedynie zaleceniem które wynikało ze struktur plików przechowujących dane na potrzeby procesu odzyskiwania bazy danych – obecnie został on zwiększony do 2TB (pamiętajmy przy tym, że musimy mieć odpowiednią ilość pamięci operacyjnej aby te wszystkie dane tam pomieścić).

Kolejnym limitem jaki występował w wersji SQL Server było COLLATION, które musiało być ustawione na BIN2.Było to o tyle problematyczne, że BIN2 jest case sensitive czyli rozróżnia wielkość znaków co naprawdę mogło być dla nas jako administratorów, programistów, deweloperów niemałym bólem głowy. Obecnie wspierane jest każde COLLATION dostępne w SQL Server. Sprawdźmy to na konkretnym przykładzie – na początku stwórzmy sobie bazę danych:

Bazę tworzymy standardowo tak jak w poprzedniej wersji (czyli pamiętajmy o tym, że w przypadku InMemory OLTP jedna grupa plików musi posiadać zapis CONTAINS MEMORY_OPTIMIZED_DATA).

Następnie stwórzmy sobie przykładową tabelę i prostą natywnie kompilowaną procedurę zwracającą dane z tejże tabeli:

Następnie wstawmy sobie przykładowy wiersz i odpytajmy go prostym zapytaniem SELECT:

Wszystko zostało zwrócone poprawnie – bez konieczności jawnego podawania COLLATION ani na poziomie zapytania, ani na poziomie definicji bazy danych. Bardzo dobra wiadomość ponieważ wcześniej było to naprawdę uciążliwe ograniczenie. Co bardziej wnikliwi zauważyli zapewne ograniczenie UNIQUE przy kolumnie Comment – tego typu konstrukcja również jest nową funkcjonalnością tabel in-memory gdyż wcześniej jedyną możliwością wymuszenia unikalności wartości w kolumnie był constraint PRIMARY KEY. W tym miejscu warto również wspomnieć o tym, że statystyki w ramach tabel tego typu nie były automatycznie aktualizowane co zostało zmienione w najnowszej wersji – jest to coś czego bardzo brakowało wcześniej i bardzo często mogło niwelować inne zalety mechanizmu.

Kolejnym limitem z kategorii “najbardziej uciążliwych” był brak możliwości edycji obiektów Hekatonu przy pomocy ALTER TABLE czy ALTER PROCEDURE – sprawdźmy czy w SQL Server 2016 możemy już te operacje wykonywać:

Zapytanie zakończyło się sukcesem:

Zobaczmy jak to wygląda w przypadku procedur składowanych:

Tutaj również operacja zakończyła się sukcesem – całkiem fajnie prawda? Jednakże z operacją ALTER wiąże się kilka haczyków na które trzeba zwrócić szczególną uwagę. Przede wszystkim przy ALTER TABLE tak naprawdę tworzona jest nowa tabela i do tej nowej tabeli wstawiane są dane, a co za tym idzie nasza pamięć operacyjna musi pomieścić wszystkie dane – te które z oryginalnej tabeli i te w nowej tabeli. Ponadto polecenie ALTER blokuje dostęp do tabeli przez co jest ona nie dostępna dla zapytań. ALTER TABLE wykonywany jest na wielu wątkach jednakże niektóre operacje mogą powodować, iż równoległe przetwarzanie ustąpi przetwarzaniu seryjnemu. Determinantami tego zachowania są następujące operacje wchodzące w skład ALTER TABLE:

  • Dodanie kolumny typu LOB (nvarchar(max),varchar(max), varbinary(max)
  • Dodanie Columnstore Index
  • Wszelkie operacje z kolumnami off-row (oprócz zwiększania ich rozmiaru)

Dlatego też przy operacjach tego typu należy uważać aby nie zablokować sobie tabeli na dłuższy okres poprzez przetwarzanie jednowątkowe lub też by nie wyczerpać dostępnej pamięci. Polecenia ALTER są bardzo przydatne i mogą pomóc w utrzymaniu indeksów co było niemal niemożliwe w pierwszym wcieleniu tabel przechowywanych w pamięci. Indeksy typu HASH mają swoją właściwość BUCKET_COUNT, która powinna przyjmować wartość jak najbardziej podobną do ilości unikalnych wartości kolumny. Oczywiście czasem bardzo ciężko przewidzieć nam na jaką wartość BUCKET_COUNT ustawić – dlatego też możemy skorzystać ze składni ALTER aby w naszym oknie utrzymania dostosować ten rozmiar do obecnych danych. Możemy to osiągnąć edytując indeks w tabeli i tutaj UWAGA nie jest dostępna samodzielna składnia ALTER INDEX wszystkie operacje na indeksach musimy wykonać poprzez ALTER TABLE tak jak zostało to przedstawione poniżej:

Składnia ta też jest nowością i jest dostępna tylko i wyłącznie dla MEMORY OPTIMIZED TABLES. Swoją drogą bardzo brakowało czegoś takiego – teraz mamy możliwość utrzymania naszych indeksów opartych na strukturach in-memory. W wersji 2014 kolejnym czasem bardzo poważnym ograniczeniem był brak możliwości tworzenia indeksów na kolumnach przyjmującymi wartości NULL – obecnie ograniczenie to zostało zniesione:

To oczywiście nie koniec zmian dostępnych w SQL Server 2016 które wprost dotykają technologii Hekaton. Wspomniałem powyżej o ograniczeniach wykonania na jednym wątku dla kolumn typu LOB i off-row. Wcześniej nasz wiersz miał ograniczony do 8KB rozmiar – aktualnie możemy mieć kolumny o większej długości, a nawet właśnie kolumny LOB:

Coś co było również często postrzegane (całkiem słusznie) jako wada to bardzo ograniczony wachlarz możliwości jeśli chodzi o TSQL w ramach procedur natywnie kompilowanych brak było m.in takich struktur jak OUTER JOINS, DISTINCT, OR, NOT, IN,EXISTS czy chociażby podzapytania czy funkcje matematyczne wszelakiego typu. Obecnie SQL Server 2016 znosi większość tych ograniczeń – sprawdźmy to:

Jak widać użyłem większość zakazanych wcześniej struktur – obecnie możemy ich bez żadnego problemu używać i przy ALTER PROCEDURE jedynym słusznym rezultatem jest:

Należy sobie zdawać sprawę, że SQL Server to nie tylko liczne usprawnienia ale również całkowicie nowe twory jak natywnie kompilowane skalarne funkcje użytkownika – składnia jest analogiczna do tej przedstawionej dla procedur składowanych:

Oprócz tego możemy stworzyć wyzwalacze DML opierające się na tabelach in-memory:

Bardzo dobrze, że powiększa się liczba obiektów natywnie kompilowanych. Myślę, że w przyszłości wszystkie obiekty będą natywnie kompilowane i stanie się to standardem w bazach OLTP. Co prawda to jeszcze nie wszystko czego możemy oczekiwać bo funkcje takie jak IF,CASE, SUBSTRING czy te związane z JSON nadal nie są zaimplementowane ale jest iskierka w tunelu bo pojawią się one w SQL Server 2017!

Ostatnim ulepszeniem dotyczącym in-memory OLTP w SQL Server 2016, które chciałbym szerzej omówić jest możliwość otrzymania wielowątkowego planu zapytania wykonując zapytanie interpretowanego TSQL. Zobrazujmy to na przykładzie, na samym początku stwórzmy przykładową tabelę i wstawmy do niej milion wierszy:

Następnie wykonajmy proste zapytanie i podejrzyjmy plan zapytania:

 

Na powyższym skróconym planie możemy zauważyć operatory związane z równoległością (m.in Parallelism Gather Streams)) tak więc nasz plan jest w pełni równoległy.

Oczywiście to nie wszystkie ulepszenia w ramach omawianego mechanizmu – oprócz wspomnianych wprowadzone/poprawione zostały następujące funkcjonalności:

  • Możliwość występowania kluczy obcych pomiędzy MEMORY OPTIMIZED TABLES
  • Możliwość zakładania CHECK CONSTRAINTS
  • Wielowątkowość przy odpytywaniu MEMORY OPTIMIZED TABLES
  • Zwiększono maksymalny rozmiar trwałej tabeli in-memory z 256GB do 2TB
  • Wsparcie dla Transparent Data Encryption
  • Wsparcie dla Multiple Active Result Sets(MARS)
  • usprawniony Garbage Collector
  • Możliwość rekompilacji procedur natywnie kompilowanych przy użyciu sp_recompile
  • Możliwość wywołania w ramach procedur natywnie kompilowanych innych procedur tego typu
  • wiele w wątków odczytujących dziennik transakcyjnych

Tak więc In memory OLTP przeszedł ogromną zmianę i z narzędzia przeżywającego chorobę “pierwszego realeasu” czy też chorobę wieku dziecięcego stał się pełnoprawnym mechanizmem, który z każdym kolejnym ulepszeniem i zniesionym limitem jest bliżej stania się standardem jeśli chodzi o workload OLTP.

Adrian Chodkowski
Follow me

Adrian Chodkowski

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

2 Comments

  1. Gość

    Plusy rozwiązań In-Memory są oczywiste. A jakie są minusy? Albo inaczej, w jakich przypadkach nie należy tego stosować, albo gdzie się nie sprawdza In-Memory?

    Reply
    1. Adrian ChodkowskiAdrian Chodkowski (Post author)

      W niedługim czasie planuje kilka artykułów poświęconych temu zagadnieniu i tam wskażę plusy i minusy. Ogólnie minusem jest to, że nie wszystkie mechanizmy wbudowane w SQL Server mogą wykorzystywać obiekty in-memory czy chociażby to, że nie wszystkie konstrukcje TSQL możemy wykorzystać w obiektach natywnie kompilowanych – oczywiście niuansów jest o wiele wiele więcej.

      Reply

Leave a Comment

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