In-memory OLTP – Memory Optimized Table Variables

Użycie zmiennych tabelarycznych i tabel tymczasowych w SQL Server jest niezwykle popularne. Ludzie na co dzień pracujący z kodem czy to TSQL czy też innym językiem dedykowanym do wytwarzania oprogramowania przywykli do konceptu zmiennej. Obiekt tego typu oprócz przechowywania danych skalarnych pozwala również zachować rezultat w postaci tabeli. W SQL Server tabelaryczna forma zmiennych okryta jest złą sławą i chyba każdy z nas chociaż raz uczestniczył w sesji lub czytał artykuł, który porównywał zmienne tabelaryczne z tabelami tymczasowymi. Wad zmiennych tego typu z całą pewnością jest sporo jednakże nie oznacza to, że nie sprawdzą się one w żadnej sytuacji. Nie wgłębiając się dalej w ten temat warto powiedzieć, że zarówno zmienne tabelaryczne jak i tabele tymczasowe mogą czasem powodować problemy związane ze swoją dyskową naturą (tak zmienne też są przechowywane na dysku) jak chociażby blokady na stronach systemowych w tempdb (PFS, GAM, SGAM). Jakiś czas temu, a dokładnie w SQL Server 2014 Microsoft dał nam do dyspozycji technologię In-memory OLTP, która daje nam pewne możliwości dzięki którym możemy ograniczyć lub w niektórych warunkach całkowicie wyeliminować użycie tradycyjnych zmiennych i tabel tymczasowych. O memory optimized tables już było (tutaj) tak więc w ramach niniejszego artykułu chciałbym Wam przedstawić Memory Optimized Table Variables (pisałem o nich bardzo wstępnie w artykule o procedurach natywnie kompilowanych).

Na samym początku kilka faktów o Memory Optimized Table Variable:

• jest stuprocentową strukturą In-memory i nigdy nie dotyka dysku ani w operacjach IO ani w żadnym innym kontekście,
• w żaden sposób nie używa bazy tempdb,
• musi posiadać chociaż jeden index (hash lub range),
• można ją przekazać jako parametr do procedury.

Z powyższej listy szczególnie interesujące mogą okazać się uniezależnienie od tempdb i dysku co odróżnia je od tradycyjnych zmiennych tabelarycznych. Myślę, że kiedyś powszechny mit, że tradycyjne zmienne są obiektami in – memory już minął ponieważ struktury tego typu z całą pewnością dotykają dysku i mają wiele do czynienia z bazą systemową SQL Servera. Sytuacja zmienia się nieco gdy mówimy właśnie o MEMORY OPTIMIZED TABLE VARIABLE, które rzeczywiście z operacjami IO nie mają nic wspólnego. Zobaczmy jak to działa na przykładzie. Pomijając fakt jak stworzyć bazę danych obsługującą obiekty In memory OLTP (tą informację znajdziesz tutaj) przejdźmy dalej. Aby móc stworzyć zmienną in-memory musimy najpierw stworzyć swój własny tabelaryczny typ danych zoptymalizowany do pracy w pamięci operacyjnej, a zrobimy to komendą CREATE TYPE:

CREATE TYPE dbo.FISdata_inmem
AS TABLE
(
	[SalesOrderNumber] [nvarchar](20) NOT NULL INDEX IX_FIS NONCLUSTERED,
	[ProductKey] [int] NOT NULL,
	[OrderDateKey] [int] NOT NULL,
	[DueDateKey] [int] NOT NULL,
	[ShipDateKey] [int] NOT NULL,
	[CustomerKey] [int] NOT NULL,
	[PromotionKey] [int] NOT NULL,
	[CurrencyKey] [int] NOT NULL,
	[SalesTerritoryKey] [int] NOT NULL,
	[SalesOrderLineNumber] [tinyint] NOT NULL,
	[RevisionNumber] [tinyint] NOT NULL,
	[OrderQuantity] [smallint] NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[ExtendedAmount] [money] NOT NULL,
	[UnitPriceDiscountPct] [float] NOT NULL,
	[DiscountAmount] [float] NOT NULL,
	[ProductStandardCost] [money] NOT NULL,
	[TotalProductCost] [money] NOT NULL,
	[SalesAmount] [money] NOT NULL,
	[TaxAmt] [money] NOT NULL,
	[Freight] [money] NOT NULL,
	[CarrierTrackingNumber] [nvarchar](25) NULL,
	[CustomerPONumber] [nvarchar](25) NULL,
	[OrderDate] [datetime] NULL,
	[DueDate] [datetime] NULL,
	[ShipDate] [datetime] NULL
	)        WITH  
            (MEMORY_OPTIMIZED = ON);  
GO

W powyższym zapisie nie znajdziemy nic szczególnego oprócz zapisu charakteryzującego wszystkie obiekty In-memory OLTP czyli MEMORY_OPTIMIZED=ON oraz tego, że założyłem na tym typie danych wymagany indeks. W tym konkretnym przypadku wybrałem indeks typu RANGE (NONCLUSTERED) ale nic nie stoi na przeszkodzie aby użyć HASH jeśli nasz scenariusz tego wymaga. Zmienne
in-memory używają tych samych struktur co tabele in-memory z tym, że pamięć przez nie zajmowana jest czyszczona zaraz po tym jak zapytanie znajduje się poza zasięgiem w którym stworzona została zmienna. Podobnie jak inne obiekty wchodzące w skład In-memory OLTP tak i zmienne są natywnie kompilowane przez co przy użyciu tychże obiektów oszczędzamy czas potrzebny na interpretacje i powstają DLL obsługujące wszelkie potrzebne operacje.

Kiedy mamy już zdefiniowany typ to można zadeklarować już zmienną tegoż typu i wstawić do niej jakieś dane:

DECLARE @varInmem_FIS dbo.FISdata_inmem

INSERT INTO   @varInmem_FIS
([ProductKey], [OrderDateKey], [DueDateKey], [ShipDateKey], [CustomerKey], [PromotionKey], [CurrencyKey], [SalesTerritoryKey], [SalesOrderNumber], [SalesOrderLineNumber], [RevisionNumber], [OrderQuantity], [UnitPrice], [ExtendedAmount], [UnitPriceDiscountPct], [DiscountAmount], [ProductStandardCost], [TotalProductCost], [SalesAmount], [TaxAmt], [Freight], [CarrierTrackingNumber], [CustomerPONumber], [OrderDate], [DueDate], [ShipDate])
SELECT [ProductKey], [OrderDateKey], [DueDateKey], [ShipDateKey], [CustomerKey], [PromotionKey], [CurrencyKey], [SalesTerritoryKey], [SalesOrderNumber], [SalesOrderLineNumber], [RevisionNumber], [OrderQuantity], [UnitPrice], [ExtendedAmount], [UnitPriceDiscountPct], [DiscountAmount], [ProductStandardCost], [TotalProductCost], [SalesAmount], [TaxAmt], [Freight], [CarrierTrackingNumber], [CustomerPONumber], [OrderDate], [DueDate], [ShipDate]
FROM dbo.FactInternetSales

SELECT * FROM @varInmem_FIS

Wszystko wykonało się oczywiście bez błędu, a plan wykonania dla zapytania SELECT przedstawia się następująco:

Oczywiście estymowana liczba wierszy jak to zawsze bywa ze zmiennymi nie posiadającymi statystyk wynosi 1. Łatwo to naprawimy dodając rekompilację poprzez OPTION (RECOMPILE) – po tej operacji szacunki wyglądają już zdecydowanie lepiej:

Właściwie to by było na tyle jeśli chodzi o zmienne tabelaryczne typu in-memory. Ich użycie jest proste i intuicyjne i nie powinno nikomu sprawić problemów. Sprawdźmy jakie są różnice przy wstawianiu i odczycie danych do obu typu zmiennych. Poniżej można zobaczyć plany wstawiania do obu zmiennych (na górze zmienna in-memory, a na dole tradycyjna):

Różnica jest dosyć znaczna, wstawianie danych do tradycyjnej zmiennej, która posiadała indeks trwało dłużej ponieważ dane musiały zostać poukładane w odpowiedniej kolejności. W zmiennej in – memory ze względu na całkowicie odmienną budowę indeksów nie było to konieczne. Samo wstawienie danych bezpośrednio do pamięci jest znacznie szybsze niż do tradycyjnej zmiennej będącej strukturą w bazie tempdb. Jeśli chodzi o odczyt to plany w obu przypadkach wyglądają dokładnie tak samo jednakże nie jest to reguła i zazywczaj zmienne in-memory będą szybsze:

Wspominałem już, że zmienne tego typu mogą zostać przekazane również jako parametry do procedury – sprawdźmy to na przykładzie. Stwórzmy sobie prosty typ dla zmiennej tabelarycznej, która ma zadanie przechowywać kolory produktów:

CREATE TYPE dbo.Colors_inmem AS TABLE 
( [Color] [nvarchar](20) NOT NULL INDEX IX_Color NONCLUSTERED
)

W kolejnym kroku definiujemy procedurę, która przyjmie za parametr zmienną typu, który stworzyliśmy przed chwileczką. Tworzenie tego typu procedury nie odbiega od standardu i po prostu podajemy zmienną oraz jej typ. Dodatkowo musimy dodać zapis READONLY, który jest wymagany gdy chcemy użyć TABLE VALUED PARAMETERS:

CREATE PROC dbo.usp_Test @ColorParam dbo.Colors_inmem READONLY
AS
BEGIN
	SELECT 
		 dp.Color
		,dp.EnglishProductName AS Product
		,sum(FR.OrderQuantity)
		,sum(F.OrderQuantity) AS Qty
	FROM dbo.FactInternetSales AS F
	JOIN dbo.DimProduct AS DP
	ON DP.ProductKey=F.ProductKey
	JOIN dbo.FactResellerSales AS FR
	ON FR.ProductKey=DP.ProductKey
	WHERE
		EXISTS(
			SELECT NULL
			FROM @ColorParam AS col
			WHERE DP.Color=col.Color
			) 
	GROUP BY 		
		 dp.Color
		,dp.EnglishProductName			
	OPTION (RECOMPILE)
END

W ostatnim kroku możemy procedurę wywołać i otrzymać pożądany przez nas wynik:

DECLARE @ColorParam AS dbo.Colors_inmem

INSERT INTO @ColorParam
VALUES
('Red'),('Green'),('Black')

EXEC dbo.usp_Test @ColorParam

Działa bez zarzutu i zgodnie z przewidywaniami, w zasadzie nie różni się niczym od tego co znamy z wykorzystania tradycyjnych zmiennych tabelarycznych. Tutaj może warto wspomnieć o tym, że KIEDYŚ (przed jednym z fixów dla SQL Server 2016 – więcej tutaj) użycie tabeli lub zmiennej in-memory powodowało to, że nasz plan był jednowątkowy. Na ten moment nie jest to już aktualna informacja. Wystarczy spojrzeć na plan jaki otrzymaliśmy z wykonania procedury, którą stworzyliśmy wyżej (to oczywiście tylko część planu ale wystarcza aby zobaczyć, że z całą pewnością nie jest on jednowątkowy):

Czy warto zatem używać zmiennych tego typu? Odpowiedź jest niejednoznaczna. W porównaniu do tradycyjnych konstruktów tego typu mamy kilka zalet i migracja nie powinna sprawić problemu. Z drugiej strony to nadal zmienne, czyli są jak najbardziej w porządku dla mniejszych zbiorów danych, przy większych zbiorach zużycie pamięci w indeksie założonym na takiej zmiennej rośnie zdecydowanie szybciej niż w analogicznej tabeli in-memory. Dodatkowo nie warto używać zmiennych do przechowywania dużego zbioru, który będzie mocno modyfikowany np. usuwany, wstawiany itd. Uruchomiłem w ramach jednego batcha następującą sekwencję kroków:

Deklaracja zmiennej (bazując na przedstawionym wcześniej typie):

DECLARE @varInmem_FIS dbo.FISdata_inmem

Wstawienie danych do zmiennej:

INSERT INTO   @varInmem_FIS
([ProductKey], [OrderDateKey], [DueDateKey], [ShipDateKey], [CustomerKey], [PromotionKey], [CurrencyKey], [SalesTerritoryKey], [SalesOrderNumber], [SalesOrderLineNumber], [RevisionNumber], [OrderQuantity], [UnitPrice], [ExtendedAmount], [UnitPriceDiscountPct], [DiscountAmount], [ProductStandardCost], [TotalProductCost], [SalesAmount], [TaxAmt], [Freight], [CarrierTrackingNumber], [CustomerPONumber], [OrderDate], [DueDate], [ShipDate])
SELECT [ProductKey], [OrderDateKey], [DueDateKey], [ShipDateKey], [CustomerKey], [PromotionKey], [CurrencyKey], [SalesTerritoryKey], [SalesOrderNumber], [SalesOrderLineNumber], [RevisionNumber], [OrderQuantity], [UnitPrice], [ExtendedAmount], [UnitPriceDiscountPct], [DiscountAmount], [ProductStandardCost], [TotalProductCost], [SalesAmount], [TaxAmt], [Freight], [CarrierTrackingNumber], [CustomerPONumber], [OrderDate], [DueDate], [ShipDate]
FROM dbo.FactInternetSales

Odczytanie konsumpcji pamięci:

SELECT  memory_consumer_id
       ,memory_consumer_type_desc
       ,memory_consumer_desc
       ,object_id
       ,OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) [Table_Name]
       ,index_id
       ,CAST(allocated_bytes / 1024. AS NUMERIC(15, 2)) [allocated_kb]
       ,CAST(used_bytes / 1024. AS NUMERIC(15, 2)) [used_kb]
FROM sys.dm_db_xtp_memory_consumers
WHERE memory_consumer_desc = '64K page pool'

Usunięcie danych ze zmiennej:

DELETE FROM @varInmem_FIS

Ta sekwencja została puszczona pięciokrotnie i wiecie jakie były odczyty użycia pamięci? Dosyć ciekawe:

Wygląda na to, że jak dane usuwane są ze zmiennej to pamięć i tak nie jest czyszczona. Całość pamięci zostaje zwrócona w momencie gdy zakończy się batch w którym wykorzystywana. Warto o tym pamiętać aby nie popaść w tarapaty. Podsumowując powiem, że nigdy nie przepadałem za takimi konstrukcjami jak zmienne tabelaryczne, parametry tabelaryczne czy też Multistatement Table Valued Functions. Jeśli tylko mogłem to wykorzystywałem substytuty i unikałem większości problemów z nimi związanych. Zmienne tabelaryczne in-memory są ciekawe w użyciu i mogą stanowić lepszą alternatywę do zmiennych tradycyjnych i myślę, że jeśli ktoś z Was ich używa to warto  testować i pomyśleć czy warto czy nie warto użyć obiektów In-memory OLTP.