In memory OLTP – natywnie kompilowane obiekty i typy tabelaryczne

Pare dni temu powiedzieliśmy sobie czym jest In memory OLTP oraz jak stworzyć tabele in-memory (artykuł znajdziecie tutaj). Dziś kontynuujemy naszą przygodę z tą technologią i powiemy sobie czym są natywnie kompilowane obiekty i typy tabelaryczne in memory zapraszam do lektury.

Jak już wspomniałem poprzednio cała technologia In-memory opiera się na trzech filarach tj. struktury in-memory , struktury wolne od locków/latchy oraz natywnie kompilowane obiekty właśnie. Bardzo często utożsamiało się obiekty tego typu z procedurami składowanymi – obecnie to oczywiście nie wszystko gdyż do dyspozycji kilka innych rodzajów o czym z całą pewnością sobie powiemy.

Czym zatem procedura natywna czy też inny obiekt tego typu różni się od tradycyjnego podejścia? Obie pełnią tą samą rolę z tym, że obiekt natywny kompilowany jest do kodu maszynowego i jego uruchomienie wiąże się z tym, że silnik in-memory wywołuje zewnętrzny kod w postaci biblioteki DLL – dzięki temu podejściu nie potrzeba kompilować, parsować i optymalizować obiektu za każdym razem gdy go uruchomimy co jak możecie się domyśleć wiąże się z pewnym skokiem wydajnościowym. Ważne jest to aby pamiętać, że tabele memory-optimized również są natywnie kompilowane i mają swoje DLL do pracy z danymi zawartymi w tych tabelach. Tworząc jakikolwiek moduł kompilowany to biblioteki tego modułu mogą współdziałać z bibliotekami tabel do których się odwołują.

Tak więc zaczynając nasz przegląd stwórzmy sobie pierwszy obiekt, którym tradycyjnie będzie procedura składowana. Będziemy bazować na standardowej bazie WideWorldImporters, która posiada grupę plików odpowiednią dla obiektów in-memory. Na samym początku zlokalizujmy tabele memory-optimized, których będziemy mogli użyć:

SELECT 
	SCHEMA_NAME([schema_id])+'.'+[name] AS MemoryOptimizedTable 
FROM sys.tables 
WHERE is_memory_optimized=1

W ramach wybranej przez nas bazy mamy do dyspozycji dwie takie tabele. Dlaczego to takie ważne? Ponieważ obiekty natywnie kompilowane mogą działać tylko i wyłącznie na tabelach memory-optimized. Jak już zostało wspomniane tabele również podlegają kompilacji do kodu źródłowego i posiadają szereg bibliotek DLL służących do ich obsługi – możemy je monitorować odpytując specjalny widok systemowy sys.dm_os_loaded_modules:

SELECT
	name,
	description
FROM
	sys.dm_os_loaded_modules
WHERE
	description='XTP Native DLL'

Ogólnie rzecz biorąc nie musimy przejmować się utrzymaniem tych obiektów – całość zadań bierze na siebie SQL Server, który potrafi usunąć czy też zamienić te obiekty w momencie rekompilacji czy też restartu serwera.

Przechodząc dalej napiszmy naszą pierwszą natywnie kompilowaną procedurę – będzie ona miała za zadanie zwrócenie średniej temperatury dla sensora podanego jako parametr:

CREATE PROCEDURE dbo.GetAverageTemperatureBySensor
	@ChillerSensorNumber tinyint
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH
(
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english'
)
SELECT  
      AVG([Temperature]) AS AvgTemperature
  FROM [Warehouse].[VehicleTemperatures] 
  WHERE ChillerSensorNumber=@ChillerSensorNumber
END
GO

Samo zapytanie tworzące nie jest niczym specjalnym oprócz paru sekcji, które mogą wydać się interesujące. Pierwszą z nich jest niedostępna wcześniej klauzula NATIVE_COMPILATION, która jak sama nazwa wskazuje odpowiada za to, że dany obiekt (w tym wypadku procedura) poddana jest kompilacji do kodu maszynowego. Drugi element to SCHEMABINDING, który możemy kojarzyć z widoków – w tym wypadku pełni dokładnie taką samą funkcję to znaczy zabrania modyfikacji obiektów z których procedura korzysta. Póki co SCHEMABINDING jest możliwy tylko dla procedur natywnych (chociaż zdarzyło mi się użyć tego przełącznika dla tradycyjnych procedur – SQL Server nie prostestował co samo w sobie było bugiem bo przełącznik ten jest niedostępny dla tych obiektów jednakże samo jej dodanie do definicji nie dawało absolutnie żadnego efektu o czym opowiem innym razem).

W dalszej części mamy sekcję BEGIN ATOMIC który jest dosyć ciekawym konstruktem tj. powoduje on, że albo cały kod w ramach bloku zostanie wykonany albo zostanie wycofany. W momencie gdy procedura z blokiem ATOMIC zostanie wywołana to tworzona jest nowa transakcja zatwierdzana pod sam koniec. W przypadku gdy takową procedurę wywołamy w ramach innej procedury to stworzony zostanie SAVEPOINT. Ogólnie rzecz biorąc moduły natywne potrzebują tego bloku aby zapewnić, że w całości zostanie on wykonany bądź nie. Wymaganym elementem deklaracji obiektów tego typu jest podanie poziomu izolacji transakcji, który może być ustawiony w parametrze TRANSACTION ISOLATION LEVEL. Domyślnym poziomem izolacji jest SNAPSHOT ale możemy również użyć REPEATABLE READ czy też SERIALIZABLE. Ostatnim obligatoryjnym elementem jest ustawienie języka czyli LANGUAGE, dzięki niemu możemy m.in zwracać komunikaty procedur w odpowiednim dla nas języki.

Wywołanie procedury jest analogiczne do tego co mieliśmy wcześniej w przypadku tradycyjnych obiektów – w gruncie rzeczy użytkownicy końcowi mogą nie mieć świadomości czy korzystają z obiektów natywnie kompilowanych czy też nie:

EXEC dbo.GetAverageTemperatureBySensor 1

Ktoś z Was może zadać pytanie – co z planami wykonania i całą fazą optymalizacji dla obiektów tego typu? Odpowiedź jest stosunkowo łatwa do przewidzenia – cała faza optymalizacji ma miejsce w czasie szeroko pojętej kompilacji. Plan wykonania sam w sobie jest kompilowany do DLL – co jak można się domyślać uniemożliwia automatyczną rekompilację planów. Możemy wymusić rekompilację przy następnym wywołaniu używając procedury sp_recompile:

sp_recompile 'dbo.GetAverageTemperatureBySensor'

Dodatkowo rekompilację powoduje wywołanie komendy ALTER TABLE przy zmianie listy parametrów itp. – warto tutaj zaznaczyć, że w czasie kompilacji nic nie jest blokowane – stara wersja przed kompilacją jest dostępna dla użytkowników na czas tworzenia nowej – po wygenerowaniu nowych struktur stare są nimi zastępowane i usuwane.

Standardowym zachowaniem tradycyjnych procedur jest tzw. parameter sniffing – czyli cache’owanie planów wykonania dla specjalnych wartości parametrów.  W przypadku procedur natywnie kompilowanych plan wykonania jest tworzoy w czasie tworzenia tworzenia obiektu lub rekompilacji wspomnianej powyżej, a wszystkie parametry są rozpatrywane jako UNKNOWN, czyli element niejako “uśredniony”.

W pierwszej implementacji omawianej technologii (czyli w SQL Server 2014) musieliśmy również dodać frazę EXECUTE AS OWNER gdyż możliwe było wykonanie procedury tylko i wyłącznie w kontekście jej właściciela jednakże obecnie możemy również użyć użytkownika wywołującego procedurę tj. EXECUTE AS CALLER, który jest opcją domyślną.Aby sprawdzić to zachowanie stwórzmy przykładową procedurę:

CREATE PROCEDURE dbo.GetUser
WITH NATIVE_COMPILATION, SCHEMABINDING,
EXECUTE AS CALLER
AS BEGIN ATOMIC WITH
(
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english'
)
SELECT  
      SUSER_NAME()
END
GO

Następnie wywołajmy ją z naszego obecnego użytkownika( w moim przypadku jest to użytkownik o nazwie Administrator):

EXEC GetUser

Teraz zmieńmy kontekst na użytkownika testowego:

EXECUTE AS USER= 'test'
GO

EXEC GetUser

Wszystko działa zgodnie z oczekiwaniami i za każdym razem procedura zwraca nazwę użytkownika, który ją uruchomił.

Jak już wspomniałem procedury to niejedyne obiekty natywnie kompilowane – mamy równiez do dyspozycji skalarne funkcje użytkownika – dla przykładu poniższa funkcja zwraca część dziesiętną liczby podanej jako parametr:

CREATE FUNCTION [dbo].[ufnReturnScale](@Value DECIMAL(18,2))   
RETURNS varchar(8)   
WITH NATIVE_COMPILATION, SCHEMABINDING  
AS   
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')  

    DECLARE @ReturnValue varchar(8);  
    SET @ReturnValue = ABS(CAST(@Value AS INT)-@Value)
    RETURN (@ReturnValue);  

END

Deklaracja jest analogiczna do procedur kompilowanych do kodu maszynowego. Sprawdźmy wykorzystanie powyższej procedury w praktyce:

SELECT 
	Temperature,
	[dbo].[ufnReturnScale](Temperature)
FROM 
	[Warehouse].[VehicleTemperatures]

Jak widać powyżej funkcja działa bez zarzutu, a samo jej stworzenie nie powinno nam przysporzyć żadnych trudności. Funkcje tego typu mają te same zalety i ograniczenia co procedury składowane.

Ostatnią funkcjonalnością na jaką chciałbym zwrócić uwagę jest typ tabelaryczny działający w pamięci operacyjnej. Funkcjonalność ta jest analogiczna do znanej nam wszystkim zmiennej tabelarycznej. Tworzenie omawianego typu wygląda następująco:

 CREATE TYPE dbo.SensorsType AS TABLE  
(  
   id int not null,
   SensorNumber tinyint not null  

   PRIMARY KEY NONCLUSTERED (id,SensorNumber)  
) WITH (MEMORY_OPTIMIZED=ON)  
GO  

Wszystko jest analogiczne do tego co mieliśmy już wcześniej z tym wyjątkiem, że musimy podać klauzulę MEMORY_OPTIMIZED=ON. Technologia typów in-memory jest analogiczna do tabel in-memory dlatego też również tutaj musimy podać minimum jeden indeks HASH lub NONCLUSTERED, który będzie w stanie powiązać wiersze w ramach zmiennej pożądanego typu. Następnie zadeklarujmy zmienną której jako typ wskażemy wcześniej utworzony dbo.SesnorType. Następnie wstawmy do niej dane i odpytajmy ją tak jak standardową zmienną tabelaryczną:

DECLARE @vSensors dbo.SensorsType

INSERT INTO @vSensors (id,SensorNumber)
SELECT ROW_NUMBER() OVER(ORDER BY ChillerSensorNumber), ChillerSensorNumber
FROM
(
SELECT DISTINCT ChillerSensorNumber FROM [Warehouse].[VehicleTemperatures] 
) t

SELECT * FROM @vSensors

W porównaniu do tradycyjnych zmiennych tabelarycznych mamy w tym przypadku wiele zalet tj:

• zmienne tabelaryczne utworzone na typie MEMORY_OPTIMIZED są zawsze przechowywane w pamięci operacyjnej dzięki czemu ich użycie jest tak samo wydajne jak tabel MEMORY_OPTIMIZED
• ze względu na istnienie w pamięci operacyjnej żadne zasoby bazy systemowej tempdb nie są wykorzystywane
• wyeleminowanie z użycia tempdb skutkuje brakiem jakichkolwiek rywalizacji i blokad o strony systemowe takie jak SGAM czy PFS

No dobrze, zalet użycia omawianych obiektów jest sporo i są one stosunkowo oczywiste – jakie są natomiast wady? Największą z nich jest to, że obiekty tego typu wspierają tylko część składni języka TSQL – wszystko zależy od wersji SQL Server. W wersji SQL Server 2014 bardzo wiele funkcjonalności nie było wspieranych jak chociażby brak możliwości implementacji OUTER JOIN, w 2016 jest już o wiele lepiej jednakże nadal brakuje funkcji tekstowych SUBSTRING.LEFT,RIGHT czy chociażby CROSS APPLY, 2017 znosi bardzo wiele ograniczeń w tym te, które nadal występują w SQL Server 2016 – całą listę wspieranych i niewspieranych funkcjonalności znajdziecie tutaj.

Łatwym sposobem na sprawdzenie tego czy nasze obecne obiekty mogą być poddane natywnej kompilacji są wbudowane narzędzia migracyjne dostępne z poziomu Management Studio. Wybierzmy sobie tabelę, którą chcemy sprawdzić, kliknijmy na nią prawym przyciskiem myszy i z menu kontekstowego wybierzmy Memory Optimization Advisor (w poniższym przykładzie sprawdzę tabelę Purchasing.PurchaseOrderLines dostępną w WideWorldImporters) – naszym oczom ukaże się prosty kreator migracyjny:

Po kliknięciu Next kreator sprawdzi czy w definicji naszego obiektu nie ma czegoś nieobsługiwanego przez tabele MEMORY OPTIMIZED:

Jak można zauważyć tabelę można migrować bez większych problemów – jednakże kreator nie zrobi tego za nas ze względu na ograniczenia FOREIGN KEY, które muszą być usunięte przed migracją i przywrócone po migracji. Oczywiście czasem zdarza się, że otrzymamy szereg różnych błędów, które wymagają nieco większej analizy takich jak występowanie CHECK constraints czy też kolumny SPATIAL.

W przypadku procedur składowanych mamy do dyspozycji analogiczny kreator – w tym wypadku jednak jest on jednak nieco mniej użyteczny gdyż zazwyczaj gdy występują jakieś niezgodności otrzymujemy mało treściwy komunikat taki jak na poniższym zrzucie:

Dlatego też sami musimy znaleźć, które struktury w ramach naszej procedury nie są wspierane.

Podsumowując natywna kompilacja obiektów dosyć znacznie rozszerza nasz wachlarz możliwości dostępnych w SQL Server. W bardzo wielu przypadkach obiekty tego typu znajdą zastosowanie – szczególnie, że Microsoft postawił na tą technologię i każda kolejna edycja SQL Server będzie ją dodatkowo rozszerzała i znosiła kolejne limity. Ponadto SQL Server 2016 mamy możliwość definiowania odpowiednika zmiennych tabelarycznych co może rozwiązywać nam szereg problemów jakie do tej pory mieliśmy z TempDb. W niedługim czasie postaram się powiedzieć nieco więcej o modelu transakcyjności w in-memory OLTP.