SQL Server – minimalne logowanie

Na to czy nasze rozwiązanie jest dostatecznie wydajne ma wpływ bardzo dużo różnych czynników. W przypadku procesów ładujących ETL czy też ELT jednym z kluczowych aspektów na jakie trzeba zwrócić uwagę jest minimalne logowanie. Co to jest i jak należy to testować? Postaram się to wytłumaczyć w ramach niniejszego artykułu – serdecznie zapraszam.

Jak wszyscy wiemy baza danych aby zachować konsystencję i spełnić wszelkie postulaty bycia transakcyjną wykorzystuje dziennik transakcyjny (ang. transaction log). To właśnie w tym miejscu znajdują się informacje o niemal wszystkich operacjach modyfikujących jakie zostały wykonane na bazie danych. Jest to niezwykle istotne ze względu na fakt, iż w przypadku wystąpienia awarii do przywrócenia bazy potrzebujemy nie tylko kopii bazy danych ale również dziennika transakcyjnego.

Jeśli chodzi o wstawianie danych do tabeli (bo na tej konkretnej operacji się skupimy) w standardowej konfiguracji bazy danych do dziennika trafiają wstawiane dane oraz wszelkie inne informacje związane z alokacją stron i ekstentów czyli podstawowych jednostek przechowywania danych. Warto dodać, że dane umieszczone w dzienniku nie muszą od razu znaleźć się ponownie w pliku danych bo po prostu sam wpis w dzienniku wystarczy (dopiero po jakimś czasie zmodyfikowane dane są zrzucane do pliku procesem checkpoint). Nieco problematyczne może się okazać wstawianie nieco większych zbiorów danych np. przy 100 milionach wierszy dziennik może po prostu osiągnąć ogromne rozmiary, a sama operacja wstawiania może być wolniejsza niż byśmy tego oczekiwali.

W takich konkretnych przypadkach może nam pomóc minimalne logowanie czyli takie wykorzystanie dziennika aby znalazły się w nim tylko niektóre informacje niezbędne do utrzymania konsystencji i transakcyjności. Kwintesencja minimalnego logowania to fakt, że w dzienniku nie są umieszczane same dane czyli inserty, a jedynie informacje o alokacjach i metadane. Jak więc zachowane są zasady związane z transakcyjnością? Do dziennika trafia informacja o alokacjach, a zaraz po zatwierdzeniu (commit) batcha (czyli porcji danych) wszystkie dane są zrzucane do pliku. To zachowanie również ma wpływ na wydajność i dosyć jasno uwypukla dlaczego rozmiar batcha w jakim wstawiamy dane jest również istotny. Jak więc możecie zauważyć, minimalne logowanie w żadnym wypadku nie oznacza braku logowania w ogóle! Jeśli chcielibyśmy mieć brak logowania to warto zapoznać się z nietrwałymi tabelami in-memory (tutaj artykuł o tabelach in-memory i wykorzystaniu dziennika transakcyjnego).

W tym miejscu warto zwrócić uwagę na jeden istotny fakt – mówimy tutaj o optymalizacji wykorzystania dziennika transakcyjnego co może mieć znaczący wpływ na wydajność wstawiania danych. Aspektów mających ogólny wpływ jest zdecydowanie więcej, a przedstawiony tutaj jest tylko jednym z nich, bardzo ważnym ale z całą pewnością nie jedynym.

No dobrze, co zatem muszę zrobić aby moje wstawianie danych wykorzystywało dziennik w minimalnym stopniu, a nie w pełnym? Odpowiedź już nie jest jednoznaczna ponieważ wpływ na to ma szereg różnych czynników m.in:

• wersja SQL Server,
• ustawiony tryb Recovery model,
• sposób ładowania danych,
• rozmiar batcha,
• struktura tabeli docelowej,
• czy tabela jest pusta czy nie,
• plan wybrany przez optymalizator.

Trochę tego dużo prawda? Ale myślę, że warto nad tym przysiąść i powiedzieć kilka słów ze względu na to, że osiągalny efekt może być naprawdę dobry. Pierwszą rzeczą na jaką musimy zwrócić uwagę jest oczywiście Recovery model. Ustawienie to wprost determinuje w jaki sposób nasza baza będzie używać dziennika transakcyjnego, a co z tym idzie czy nasza operacja będzie minimalnie logowana czy też nie. W trybie FULL operacje są w pełni logowane i mamy możliwość przywrócenia bazy do określonego punktu w czasie, w tym trybie minimalnie logowane inserty nie są możliwe. Tryb BULK-LOGGED działa bardzo podobnie z tym, że wspomniane inserty są możliwe jednak powinniśmy traktować ten tryb jako “tymczasowy” – ustawiamy go gdy musimy np. załadować jednorazowo dużą ilość danych, a nie chcemy utracić logowania innych aktywności. Ostatni i zarazem najbardziej nas interesujący tryb to SIMPLE, który z punktu widzenia hurtowni danych czy też jakichkolwiek innych rozwiązań analitycznych opartych o SQL Server zazwyczaj jest tym czego szukamy. Ustawienie tego trybu powoduje, że przede wszystkim po zatwierdzeniu transakcji kolejna transakcja może zacząć nadpisywać dziennik od początku, a po drugie umożliwia on minimalne logowanie wstawień danych. Wadą jest tutaj fakt, że nie możemy przywrócić naszej bazy do konkretnego punktu w czasie ze względu właśnie na zachowanie dziennika transakcyjnego – co w większości przypadków nie jest istotne dla rozwiązań takich jak hurtownia danych.

Drugą rzeczą na jaką powinniśmy zwrócić uwagę jest sposób ładowania danych. To czy użyjemy komendy INSERT SELECT czy np. INSERT SELECT z hintem TABLOCK jest niezmiernie istotne (tutaj nieco bardziej szczegółowy artykuł o TABLOCK). Wyobraźmy sobie sytuację gdzie wrzucamy dane do sterty operacją INSERT SELECT, a cała baza będzie w trybie SIMPLE – czy będzie to operacja minimalnie logowana? Odpowiedź brzmi nie. Muszą zostać spełnione określone warunki o których jeszcze zdążę opowiedzieć. Na ten moment kilka bezpiecznych komend, które mogą być minimalnie logowane (wszystko zależy oczywiście od wersji – w tym miejscu skupiam się na SQL Server 2016 i wyżej):

• INSERT SELECT (zależnie od struktury docelowej może być z hintem TABLOCK lub nie)
• SELECT INTO
• BULK INSERT
• Wszelkie narzędzia umożliwiające wykonanie wstawień z BULK (bcp, SSIS, niestandardowy kod np. w C#)

Dodatkowym czynnikiem na który należy zwrócić uwagę jest rozmiar batcha który niekiedy jest istotny – np. gdy lądujemy COLUMNSTORE to nasz batch powinien mieć co najmniej 102 400 wierszy – jeśli będzie mniejszy to już nie będziemy mieli do czynienia z optymalnym logowaniem. No i pozostaje nam struktura tabeli docelowej oraz plan wybrany przez optymalizator, które są istotne i na które powinniśmy zwracać uwagę. Nie będę rozpisywał tutaj wszystkich możliwych kombinacji bo jest ich zdecydowanie za dużo, chodzi mianowicie o to, że np. tabela nietrwała In-memory nie jest w ogóle logowana a dla przykładu Columnstore wymaga innej konfiguracji od sterty czy tabeli z tradycyjnym indeksem opartym o B-Drzewo. Ponadto czasem wpływ ma sam optymalizator, który może wybrać plan, który determinuje minimalne logowanie.

Poniżej możecie znaleźć tabelę, którą opracowałem na podstawie znalezionych materiałów (Polecam zapoznać się z następującym wpisem Pedro Lopeza gdzie wydaje mi się mamy najnowszy wpis traktujący o minimalnym logowaniu właśnie (link)):

Jak możecie zauważyć zmiennych jest bardzo dużo, a w internecie znajdziemy mnóstwo czasem sprzecznych ze sobą informacji. Jak zatem mamy wiedzieć, czy mamy do czynienia z minimalnym logowaniem? Testować! I to właśnie chciałbym wam pokazać w ramach tego wpisu. Warto zwrócić uwagę na sp_tableoption ‘table lock on bulk load’ – jeśli nie mamy możliwości użycia hinta TABLOCK w istniejącym kodzie to możemy użyć właśnie tej opcji, która zadziała dokładnie w ten sam sposób by był wyspecyfikowany TABLOCK.

Przejdźmy zatem do kodu. Na początku stworzymy sobie globalną tabelę tymczasową w której będziemy przechowywać rezultat naszych testów.

SET STATISTICS TIME OFF;
SET NOCOUNT ON;


USE master
GO

DROP TABLE IF EXISTS ##Result
GO

CREATE TABLE ##Result
(
TestGroup BIGINT
,TestName VARCHAR(250)
,Operation VARCHAR(250)
,NoOfRows BIGINT
,LogRecordLength BIGINT
)
GO

Za każdym razem gdy będziemy testować konkretne rozwiązanie będziemy tworzyć od nowa bazę i tabelę o odpowiedniej strukturze. Na pierwszy ogień wstawimy sobie stertę czyli tabelę bez indeksu zgrupowanego:

DROP DATABASE IF EXISTS SQLDay2019
GO

CREATE DATABASE SQLDay2019
GO

ALTER DATABASE SQLDay2019 SET RECOVERY SIMPLE 
WITH NO_WAIT
GO

USE SQLDay2019
GO

CREATE TABLE [dbo].[SourceTable](
	[Movement Key] [bigint] NULL,
	[Date Key] [date] NULL,
	[Stock Item Key] [int] NULL,
	[Customer Key] [int] NULL,
	[Supplier Key] [int] NULL,
	[Transaction Type Key] [int] NULL,
	[WWI Stock Item Transaction ID] [int] NULL,
	[WWI Invoice ID] [int] NULL,
	[WWI Purchase Order ID] [int] NULL,
	[Quantity] [int] NULL,
	[Lineage Key] [int] NULL
) ON [PRIMARY]
GO

Test będzie polegał na wykonaniu komendy BULK INSERT z odpowiednimi przełącznikami. Ładować będziemy dane z pliku płaskiego, który zawiera milion wierszy:

BULK INSERT dbo.SourceTable
	FROM   'C:\Users\adria\Desktop\Files\SQLDay2019\Files\SourceData_1mln.txt'
	WITH
	(
		FIRSTROW=2,
		FIELDTERMINATOR = ',',
		ROWTERMINATOR = '\n' 
	)

Po wykonaniu powyższej komendy odpytamy dziennik transakcyjny funkcją fn_dblog aby sprawdzić ile oraz jakie znalazły się tam wpisy. Rezultat umieścimy w stworzonej wcześniej globalnej tabeli tymczasowej:

INSERT INTO ##Result
SELECT 
	1,
	'HEAP + BULK INSERT'
	,[Operation]
	,COUNT(*) AS NoOfRows

	,SUM([Log Record Length])
FROM fn_dblog(NULL,NULL)
where allocunitname like 'dbo.SourceTable%'
GROUP BY [Operation]
GO

Całą sekwencję kroków możemy powtórzyć tyle razy ile mamy testów, cały skrypt jest dosyć długi i jednocześnie dosyć łatwy do odtworzenia dlatego też nie będę go tutaj przedstawiał. Chciałbym natomiast pokazać jego rezultat, który dla przejrzystości umieściłem w Power BI Desktop. Z tego właśnie powodu dane znajdują się w globalnej tabeli tymczasowej abyśmy mogli się do nich odwołać z poziomu narzędzia zewnętrznego jakim jest wspomniany Power BI Desktop.

Omówmy sobie zatem rezultaty otrzymanych testów. Jeśli chodzi o stertę rezultat wygląda następująco:

Jak możecie zauważyć w tym konkretnym przypadku użycie TABLOCKa spowodowało minimalne logowanie bez względu czy tabela docelowa zawierała docelowe indeksy nieklastrowane czy też nie. Oczywiście należy pamiętać, że podczas wstawiania danych do sterty z indeksami dane musiały być dodatkowo sortowane co również miało wpływ na wydajność. Co ciekawe na wykresie możemy zobaczyć dużo “koloru” czerwonego który oznacza operację LOP_INSERT_ROWS – zgodnie z przedstawioną przez nas definicją możemy powiedzieć, że takich wpisów nie powinno być w przypadku minimalnego logowania i tak też jest w przypadku operacji z tablockiem.

Drugą strukturą na naszej liście do sprawdzenia jest tradycyjny indeks klastrowany:

W tym przypadku przeprowadziłem pięć testów i tutaj również TABLOCK okazał się kluczowy. Proszę zwrócić uwagę na test z BATCHSIZE = 1000. Wrzucanie w tak małych porcjach spowodowało, że prawdopodobnie pierwszy batch był minimalnie logowany, a każdy pozostały już nie. Jest to coś co nie wynika z przytoczonej wcześniej tabeli i podkreśla tylko jak ważne jest testowanie.

Ostatnim przypadkiem, który chciałbym przedstawić jest oczywiście Columnstore:

Oczywiście logowanie w przypadku indeksu kolumnowego jest już trochę inne niż w przypadku indeksu rowstore, jednak i w tym przypadku warto wiedzieć jak to wszystko wygląda. Najlepszy rezultat otrzymaliśmy ustawiając rozmiar batcha na 1048576 czyli na maksymalny rozmiar jaki może osiągnąć segment. Trochę gorsze rezultaty w przypadku minimalnej granicy (102 400) i 102 399. Zdecydowanie najgorszy rezultat w przypadku gdy na tabeli docelowej oprócz COLUMNSTORE mieliśmy również dwa dodatkowe indeksy oparte o b-drzewo, które jak widać były w pełni logowane.

Myślę, że warto znać te wszystkie zależności i testować różne rozwiązania aby osiągnąć docelowy poziom wydajności. Szczególnie warto ze względu na fakt, iż sam proces testowania nie jest zbyt skomplikowany i dosyć łatwo go przeprowadzić. To by było na tyle na dziś – mam nadzieję, że całość przypadła Wam do gustu i będziecie w stanie wyciągnąć coś ciekawego. Pozdrawiam!