SQL Server 2016 – usprawnienia In-memory OLTP

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:

CREATE DATABASE [InMemoryOLTP]
 CONTAINMENT = NONE
 ON  PRIMARY 
( 
	NAME = N'InMemoryOLTP', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL16\MSSQL\DATA\InMemoryOLTP.mdf' , 
	SIZE = 128MB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 65536KB ), 

FILEGROUP [TicketReservations_mod] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
( 
	NAME = N'InMemoryOLTP_mod', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL16\MSSQL\DATA\InMemoryOLTPs_mod' , 
	MAXSIZE = UNLIMITED)

LOG ON 
( 
	NAME = N'InMemoryOLTP_log', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL16\MSSQL\DATA\InMemoryOLTP_log.ldf' , 
	SIZE = 1024MB , 
	MAXSIZE = 2048GB , 
	FILEGROWTH = 65536KB 
)
GO

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:

CREATE TABLE [dbo].[Comments]
(
	[ID] [bigint] NOT NULL,
	[Comment] [nvarchar](50) UNIQUE COLLATE Polish_CI_AS NULL,

 CONSTRAINT [PK_ID]  PRIMARY KEY NONCLUSTERED HASH 
(
	[ID]
)WITH ( BUCKET_COUNT = 10)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO

CREATE PROCEDURE [dbo].[usp_GetComments]
WITH NATIVE_COMPILATION,
         SCHEMABINDING,
         EXECUTE AS CALLER
AS
  BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT,
   LANGUAGE = N'us_english' )
        SELECT [Id], [Comment] 
        FROM [dbo].[Comments]
END;

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

INSERT INTO [dbo].[Comments]
VALUES
(
1,
N'Testowy zestaw znaków'
)
GO

SELECT *
FROM
	[dbo].[Comments]
WHERE 
	[Comment] LIKE N'T%'

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ć:

ALTER TABLE [dbo].[Comments]
ADD [CommentDate] datetime default GETDATE()

Zapytanie zakończyło się sukcesem:

Command(s) completed successfully.

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

ALTER PROCEDURE [dbo].[usp_GetComments]
WITH NATIVE_COMPILATION,
         SCHEMABINDING,
         EXECUTE AS CALLER
AS
  BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT,
   LANGUAGE = N'us_english' )
        SELECT [Id], [Comment] ,[CommentDate]
		FROM [dbo].[Comments]
END;

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:

ALTER TABLE [dbo].[Comments]
ALTER INDEX [PK_ID] 
REBUILD WITH (BUCKET_COUNT=100)

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:

ALTER TABLE [dbo].[Comments]
ADD INDEX IX_Comment NONCLUSTERED([Comment] )

Command(s) completed successfully.

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:

ALTER TABLE [dbo].[Comments]
ADD [Comments2] nvarchar(max) NULL

Command(s) completed successfully.

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:

ALTER PROCEDURE [dbo].[usp_GetComments]
WITH NATIVE_COMPILATION,
         SCHEMABINDING,
         EXECUTE AS CALLER
AS
  BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT,
   LANGUAGE = N'us_english' )
        SELECT [Id], [Comment] ,[CommentDate]
		FROM [dbo].[Comments] C
		LEFT JOIN
		(SELECT 1 AS TableKey) D
		ON D.TableKey=C.ID
		
		UNION ALL
		
		SELECT DISTINCT [Id], [Comment] ,[CommentDate]
		FROM [dbo].[Comments]
		WHERE Id IN (1,2) OR Id IN (3,4)

		UNION ALL

				SELECT DISTINCT [Id], [Comment] ,[CommentDate]
		FROM [dbo].[Comments]
		WHERE Id IN (1,2) OR Id IN (3,4)

END;

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:

Command(s) completed successfully.

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:

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

    DECLARE @ReturnValue varchar(8);  
    SET @ReturnValue = LTRIM(RTRIM(@Value)); 

    RETURN (@ReturnValue);  

END

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

CREATE TRIGGER  [dbo].[TestTrigger]
ON dbo.Comments
WITH NATIVE_COMPILATION, SCHEMABINDING
FOR INSERT
AS BEGIN ATOMIC WITH
(
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english'
)
INSERT INTO [dbo].[Comments]
(ID, Comment, CommentDate, Comments2)
SELECT ID, Comment, CommentDate, N'TriggerInsert' AS Comments2 FROM INSERTED
END
GO

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:

SET NOCOUNT ON

CREATE TABLE [dbo].[UserSession]
(
	[SessionId] [int] IDENTITY(1,1) NOT NULL,
	[UserId] [int] NOT NULL,
	[CreatedDate] [datetime2](7) NOT NULL,

 PRIMARY KEY NONCLUSTERED HASH 
(
	[SessionId]
)WITH ( BUCKET_COUNT = 1000000)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
GO

DECLARE @Counter INT =1

WHILE @Counter<=1000000
BEGIN
	INSERT INTO [dbo].[UserSession]
	(
	[UserId],
	[CreatedDate]
	)
	VALUES
	(
	@Counter,
	GETDATE()
	)
SET @Counter=@Counter+1
END

Następnie wykonajmy proste zapytanie i podejrzyjmy plan zapytania:

SELECT
	[CreatedDate],
	COUNT(DISTINCT [UserId])
FROM
	[dbo].[UserSession]
GROUP BY
	[CreatedDate]

 

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.