MemoryOptimizedTables_TransactionLog00

Memory Optimized Tables a wykorzystanie dziennika transakcyjnego

Written by on in MSSQL, SQL Server database

Memory Optimized Tables jako jeden z kluczowych elementów technologii In-memory OLTP pod kątem wydajności potrafi dawać świetne rezultaty. Oprócz całkowicie nowego systemu składowania danych dla tych tabel zmieniony został sposób logowania informacji – to właśnie w MOT mamy możliwość implementacji całkowitej rezygnacji z informacji zawartych w dzienniku transakcyjnym! Chciałbym tutaj podkreślić całkowitego braku logowania, a nie minimalnego logowania – w tradycyjnym podejściu niemal wszystko było w jakiś sposób logowane czy w przypadku nietrwałych tabel In-memory jest podobnie? Mamy jakieś ukryte logowania? Sprawdzimy to w niniejszym artykule, zapraszam do lektury.

Tradycyjnie na samym początku stworzymy sobie testową bazę danych wraz z odpowiednią grupą plików na obiekty In-memory:

--check if database exists
DROP DATABASE IF EXISTS Hekaton_Demo
GO

--create database
CREATE DATABASE [Hekaton_demo]
 ON  PRIMARY 
( 
	NAME = N'Hekaton_demo', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Hekaton_demo.mdf' , 
	SIZE = 524288KB , 
	FILEGROWTH = 65536KB 
)
 LOG ON 
( 
	NAME = N'Hekaton_demo_log', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Hekaton_demo_log.ldf' , 
	SIZE = 131072KB , 
	FILEGROWTH = 65536KB 
)
GO


--add special filegroup to database
ALTER DATABASE Hekaton_demo
ADD FILEGROUP AWMemGroup CONTAINS MEMORY_OPTIMIZED_DATA
GO

--add "file" to filegroup
ALTER DATABASE [Hekaton_demo] 
ADD FILE ( 
	NAME = N'Hekaton_demo_inmem', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Hekaton_demo_inmem' 
	) 
TO FILEGROUP [AWMemGroup]
GO

Dodatkowo stwórzmy sobie sześć tabel, które posłużą nam do testów:

  • NonDurableTableWithHashIndex – nietrwała tabela in-memory z indeksem HASH
  • DurableTableWithHashIndex – trwała tabela in-memory z indeksem HASH
  • NonDurableTableWithRangeIndex – nietrwała tabela in-memory z indexem Nonclustered (Range)
  • DurableTableWithRangeIndex – trwała tabela in-memory z indexem Nonclustered (Range)
  • ClusteredDiskTable – tradycyjna tabela dyskowa z indeksem klastrowanym
  • HeapDiskTable – tradycyjna tabela dyskowa będąca stertą (bez indeksu)
 USE Hekaton_demo
 GO
 
 CREATE TABLE dbo.NonDurableTableWithHashIndex (   
   TableId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=100000),   
   AdditionalId int NOT NULL, 
   LoginGUID nvarchar(50),  
   CreatedDate DATETIME2 NOT NULL,    
 )   
 WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)   
 GO

 CREATE TABLE dbo.DurableTableWithHashIndex (   
   TableId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=100000),   
   AdditionalId int NOT NULL, 
   LoginGUID nvarchar(50),  
   CreatedDate DATETIME2 NOT NULL,    
 )   
 WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)   
 GO

  CREATE TABLE dbo.NonDurableTableWithRangeIndex (   
   TableId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,   
   AdditionalId int NOT NULL, 
   LoginGUID nvarchar(50),  
   CreatedDate DATETIME2 NOT NULL,    
 )   
 WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)   
 GO

 CREATE TABLE dbo.DurableTableWithRangeIndex (   
   TableId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,   
   AdditionalId int NOT NULL, 
   LoginGUID nvarchar(50),  
   CreatedDate DATETIME2 NOT NULL,    
 )   
 WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)   
 GO


  CREATE TABLE dbo.ClusteredDiskTable (   
   TableId INT IDENTITY(1,1) PRIMARY KEY,   
   AdditionalId int NOT NULL, 
   LoginGUID nvarchar(50),  
   CreatedDate DATETIME2 NOT NULL,    
 )   
 WITH (MEMORY_OPTIMIZED=OFF)   
 GO

   CREATE TABLE dbo.HeapDiskTable (   
   TableId INT IDENTITY(1,1),   
   AdditionalId int NOT NULL, 
   LoginGUID nvarchar(50),  
   CreatedDate DATETIME2 NOT NULL,    
 )   
 WITH (MEMORY_OPTIMIZED=OFF)   
 GO

Następnie wypełnijmy tabele danymi przy pomocy pętli. Każda pętla będzie w transakcji po to abyśmy mogli zidentyfikować wpisy w dzienniku i przypisać je do każdej z wykonanych przez nas operacji. Do każdej z tabel wstawimy po 100 tysięcy wierszy:

 USE Hekaton_demo
 GO

 SET NOCOUNT ON
 GO

 BEGIN TRAN NonDurableTableWithHashIndex
 DECLARE  @Counter INT=0
 DECLARE  @NoOfRows INT=100000
 
WHILE @Counter<@NoOfRows
BEGIN
	 INSERT dbo.NonDurableTableWithHashIndex(AdditionalId,LoginGUID,CreatedDate)
	 VALUES (CAST(RAND()*10000 AS INT), CAST(NEWID() AS NVARCHAR(50)),DATEADD(DAY,-CAST(RAND()*1000 AS INT),GETDATE()))   
SET @Counter=@Counter+1
END

COMMIT TRAN NonDurableTableWithHashIndex
GO

BEGIN TRAN DurableTableWithHashIndex
 DECLARE  @Counter INT=0
 DECLARE  @NoOfRows INT=100000
 
WHILE @Counter<@NoOfRows
BEGIN
	 INSERT dbo.DurableTableWithHashIndex(AdditionalId,LoginGUID,CreatedDate)
	 VALUES (CAST(RAND()*10000 AS INT), CAST(NEWID() AS NVARCHAR(50)),DATEADD(DAY,-CAST(RAND()*1000 AS INT),GETDATE()))   
SET @Counter=@Counter+1
END

COMMIT TRAN DurableTableWithHashIndex
GO

BEGIN TRAN DurableTableWithRangeIndex
 DECLARE  @Counter INT=0
 DECLARE  @NoOfRows INT=100000
 
WHILE @Counter<@NoOfRows
BEGIN
	 INSERT dbo.DurableTableWithRangeIndex(AdditionalId,LoginGUID,CreatedDate)
	 VALUES (CAST(RAND()*10000 AS INT), CAST(NEWID() AS NVARCHAR(50)),DATEADD(DAY,-CAST(RAND()*1000 AS INT),GETDATE()))   
SET @Counter=@Counter+1
END

COMMIT TRAN DurableTableWithRangeIndex
GO

BEGIN TRAN NonDurableTableWithRangeIndex
 DECLARE  @Counter INT=0
 DECLARE  @NoOfRows INT=100000
 
WHILE @Counter<@NoOfRows
BEGIN
	 INSERT dbo.NonDurableTableWithRangeIndex(AdditionalId,LoginGUID,CreatedDate)
	 VALUES (CAST(RAND()*10000 AS INT), CAST(NEWID() AS NVARCHAR(50)),DATEADD(DAY,-CAST(RAND()*1000 AS INT),GETDATE()))   
SET @Counter=@Counter+1
END

COMMIT TRAN NonDurableTableWithRangeIndex
GO

BEGIN TRAN ClusteredDiskTable
 DECLARE  @Counter INT=0
 DECLARE  @NoOfRows INT=100000
 
WHILE @Counter<@NoOfRows
BEGIN
	 INSERT dbo.ClusteredDiskTable(AdditionalId,LoginGUID,CreatedDate)
	 VALUES (CAST(RAND()*10000 AS INT), CAST(NEWID() AS NVARCHAR(50)),DATEADD(DAY,-CAST(RAND()*1000 AS INT),GETDATE()))   
SET @Counter=@Counter+1
END

COMMIT TRAN ClusteredDiskTable
GO

BEGIN TRAN HeapDiskTable
 DECLARE  @Counter INT=0
 DECLARE  @NoOfRows INT=100000
 
WHILE @Counter<@NoOfRows
BEGIN
	 INSERT dbo.HeapDiskTable(AdditionalId,LoginGUID,CreatedDate)
	 VALUES (CAST(RAND()*10000 AS INT), CAST(NEWID() AS NVARCHAR(50)),DATEADD(DAY,-CAST(RAND()*1000 AS INT),GETDATE()))   
SET @Counter=@Counter+1
END

COMMIT TRAN HeapDiskTable
GO

W kolejnym kroku porównamy sobie ile wpisów trafiło do dziennika oraz ile miejsca w MB zajmują. Wykorzystamy do tego celu nieudokumentowaną funkcję fn_dblog:

USE Hekaton_demo
GO

with HeapDiskTable
AS
(
SELECT [Current LSN], 
[Operation], [Context], [Transaction ID], [AllocUnitName], [Page ID], 
[Transaction Name], [Parent Transaction ID], [Description],[Log Record Length] FROM fn_dblog(NULL, NULL)
where [Transaction ID] = 
(
SELECT TOP 1 [transaction id] FROM fn_dblog(NULL, NULL)
WHERE [transaction name] = 'HeapDiskTable'
)

), NonDurableTableWithHashIndex
AS
(
SELECT [Current LSN], 
[Operation], [Context], [Transaction ID], [AllocUnitName], [Page ID], 
[Transaction Name], [Parent Transaction ID], [Description],[Log Record Length] FROM fn_dblog(NULL, NULL)
where [Transaction ID] = 
(
SELECT TOP 1 [transaction id] FROM fn_dblog(NULL, NULL)
WHERE [transaction name] = 'NonDurableTableWithHashIndex'
)), DurableTableWithHashIndex
AS
(
SELECT [Current LSN], 
[Operation], [Context], [Transaction ID], [AllocUnitName], [Page ID], 
[Transaction Name], [Parent Transaction ID], [Description],[Log Record Length] FROM fn_dblog(NULL, NULL)
where [Transaction ID] = 
(
SELECT TOP 1 [transaction id] FROM fn_dblog(NULL, NULL)
WHERE [transaction name] = 'DurableTableWithHashIndex'
)), DurableTableWithRangeIndex
AS
(
SELECT [Current LSN], 
[Operation], [Context], [Transaction ID], [AllocUnitName], [Page ID], 
[Transaction Name], [Parent Transaction ID], [Description],[Log Record Length] FROM fn_dblog(NULL, NULL)
where [Transaction ID] = 
(
SELECT TOP 1 [transaction id] FROM fn_dblog(NULL, NULL)
WHERE [transaction name] = 'DurableTableWithRangeIndex'
)), NonDurableTableWithRangeIndex
AS
(
SELECT [Current LSN], 
[Operation], [Context], [Transaction ID], [AllocUnitName], [Page ID], 
[Transaction Name], [Parent Transaction ID], [Description],[Log Record Length] FROM fn_dblog(NULL, NULL)
where [Transaction ID] = 
(
SELECT TOP 1 [transaction id] FROM fn_dblog(NULL, NULL)
WHERE [transaction name] = 'NonDurableTableWithRangeIndex'
)), ClusteredDiskTable
AS
(
SELECT [Current LSN], 
[Operation], [Context], [Transaction ID], [AllocUnitName], [Page ID], 
[Transaction Name], [Parent Transaction ID], [Description],[Log Record Length] FROM fn_dblog(NULL, NULL)
where [Transaction ID] = 
(
SELECT TOP 1 [transaction id] FROM fn_dblog(NULL, NULL)
WHERE [transaction name] = 'ClusteredDiskTable'
))

 SELECT 
	'HeapDiskTable',
	count(*) AS NumberOfLogRecords,
	SUM([Log Record Length]) AS [Log Record Length in Bytes],
	SUM([Log Record Length])/1000000 AS [Log Record Length in MB]
FROM
 HeapDiskTable
 UNION ALL
 SELECT 
	'NonDurableTableWithHashIndex',
	count(*) AS NumberOfLogRecords,
	SUM([Log Record Length]) AS [Log Record Length in Bytes],
	SUM([Log Record Length])/1000000 AS [Log Record Length in MB]
FROM
 NonDurableTableWithHashIndex
  UNION ALL
 SELECT 
	'DurableTableWithHashIndex',
	count(*) AS NumberOfLogRecords,
	SUM([Log Record Length]) AS [Log Record Length in Bytes],
	SUM([Log Record Length])/1000000 AS [Log Record Length in MB]
FROM
 DurableTableWithHashIndex
   UNION ALL
 SELECT 
	'DurableTableWithRangeIndex',
	count(*) AS NumberOfLogRecords,
	SUM([Log Record Length]) AS [Log Record Length in Bytes],
	SUM([Log Record Length])/1000000 AS [Log Record Length in MB]
FROM
 DurableTableWithRangeIndex
   UNION ALL
 SELECT 
	'NonDurableTableWithRangeIndex',
	count(*) AS NumberOfLogRecords,
	SUM([Log Record Length]) AS [Log Record Length in Bytes],
	SUM([Log Record Length])/1000000 AS [Log Record Length in MB]
FROM
 NonDurableTableWithRangeIndex
    UNION ALL
 SELECT 
	'ClusteredDiskTable',
	count(*) AS NumberOfLogRecords,
	SUM([Log Record Length]) AS [Log Record Length in Bytes],
	SUM([Log Record Length])/1000000 AS [Log Record Length in MB]
FROM
 ClusteredDiskTable

Wyniki mogą być całkiem interesujące – dla przejrzystości umieściłem je w Excelu:

Jak można było przewidzieć tabele nietrwałe nie dotykają dziennika transakcyjnego w ogóle. Ciekawszym elementem jest fakt, że tabelki in-memory zapisały jedynie po 459 rekordów w dzienniku w porównaniu do ponad 100 tysięcy wpisów dla tabel dyskowych! Dane te potwierdziły nam jeszcze jedną zależność, a mianowicie to, że dla logowania tabel in-memory nie ma znaczenia typ indeksu na tabeli ponieważ indeksy same w sobie są jedynie strukturami pamięciowymi i w ogóle nie podlegają logowaniu. Spójrzmy na kolejną statystykę tym razem zobaczymy ile MB w dzienniku zajęło zalogowanie wykonanych przez nas operacji:

Tutaj różnice pomiędzy tabelami dyskowymi, a tabelami in-memory są około dwukrotne. Jak to możliwe, że SQL Server potrzebował dwa razy mniej miejsca aby zalogować tą samą liczbę operacji? Podejrzyjmy jak to wygląda w przypadku trwałej tabeli in-memory:

 SELECT 
	[Current LSN], 
	[Operation], 
	[Context], 
	[Transaction ID], 
	[Description],
	[Log Record Length] 
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = 
(
	SELECT TOP 1 
		[transaction id] 
	FROM 
		fn_dblog(NULL, NULL)
	WHERE 
		[transaction name] = 'DurableTableWithRangeIndex'
)

I dla porównania spójrzmy na dane w logu dla tabeli dyskowej(w tym przypadku z indeksem klastrowym):

SELECT 
	[Current LSN], 
	[Operation], 
	[Context], 
	[Transaction ID], 
	[Description],
	[Log Record Length] 
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = 
(
	SELECT TOP 1 
		[transaction id] 
	FROM 
		fn_dblog(NULL, NULL)
	WHERE 
		[transaction name] = 'ClusteredDiskTable'
)

Rozpoczęcie transakcji możemy rozpoznać zapisem LOP_BEGIN_XACT, a jej zatwierdzenie LOP_COMMIT_XACT. Pierwsza różnica jaką możemy zauważyć to fakt, że dla tabel in-memory mamy przy wstawieniu wierszy LOP_HK (Logical Operation Hekaton), a dla tabel tradycyjnych LOP_INSERT_ROWS. To nie nazewnictwo jest tutaj najważniesze ale długość rekordu podana w kolumnie Log Record Length – dla tabeli tradycyjnej wynosi ona 200, a dla in-memory 23596 (nie licząc ostatniego wpisu). Oznacza to, że sposób logowania został całkowicie zmieniony i dla tabel in-memory mamy  w ramach pojedynczego wpisu “upchane” więcej zmian. Dzięki temu odkryciu wiemy już dlaczego zalogowanie tabeli in-memory zajmuje tak mało wierszy.

Dodatkowo możemy sprawdzić ile wierszy zostało zalogowanych w ramach pojedynczego wpisu – do tego wykorzystamy kolejną nieudokumentowaną funkcję o nazwie sys.fn_dblog_xtp, którą przefiltrujemy wybranym numerem LSN:

select count(*) AS NumberOfLoggedInserts 
from
sys.fn_dblog_xtp(null,null)
where [Current LSN]='00000022:000053df:0003'

Wiemy również, że ostatni wiersz dziennika był nieco mniejszy więc sprawdźmy również ile tam zostało zawartych operacji INSERT:

select Count(*) AS NumberOfLoggedInserts
from
sys.fn_dblog_xtp(null,null)
where [Current LSN]='00000023:0000274c:0001'

Użycie dziennika transakcyjnego z memory optimized tables jest procesem dużo wydajniejszym niż ma to miejsce w przypadku tabel dyskowych. In-memory OLTP nie wykorzystuje mechanizmu Write-Ahead Logging czyli zapisu do loga zanim coś zostanie zapisane na dysku i jakakolwiek aktywność w dzienniku pojawia się dopiero w momencie wywołania COMMIT – dlatego też “brudne dane” nie są nigdy zapisywane w logu sprawdźmy to na przykładzie. Wstawmy rekord w trakcie trwania transakcji i sprawdźmy czy coś zostało zapisane w logu przed zatwierdzeniem i po zatwierdzeniu transakcji:

BEGIN TRAN DirtyDataLoadTest

 INSERT dbo.DurableTableWithHashIndex(AdditionalId,LoginGUID,CreatedDate)
	 VALUES (CAST(RAND()*10000 AS INT), CAST(NEWID() AS NVARCHAR(50)),DATEADD(DAY,-CAST(RAND()*1000 AS INT),GETDATE())) 

SELECT count(*) AS NoOfRecordsBeforeCommit FROM fn_dblog(NULL, NULL)
where [Transaction ID] = 
(
SELECT TOP 1 [transaction id] FROM fn_dblog(NULL, NULL)
WHERE [transaction name] = 'DirtyDataLoadTest'
)
COMMIT 

SELECT count(*) AS NoOfRecordsAfterCommit FROM fn_dblog(NULL, NULL)
where [Transaction ID] = 
(
SELECT TOP 1 [transaction id] FROM fn_dblog(NULL, NULL)
WHERE [transaction name] = 'DirtyDataLoadTest'
)

Zróbmy teraz analogiczny test na tabeli nie będącej memory-optimized:

BEGIN TRAN DirtyDataLoadTest2

 INSERT dbo.ClusteredDiskTable(AdditionalId,LoginGUID,CreatedDate)
	 VALUES (CAST(RAND()*10000 AS INT), CAST(NEWID() AS NVARCHAR(50)),DATEADD(DAY,-CAST(RAND()*1000 AS INT),GETDATE())) 

SELECT count(*) AS NoOfRecordsBeforeCommit FROM fn_dblog(NULL, NULL)
where [Transaction ID] = 
(
SELECT TOP 1 [transaction id] FROM fn_dblog(NULL, NULL)
WHERE [transaction name] = 'DirtyDataLoadTest2'
)
COMMIT 

SELECT count(*) AS NoOfRecordsAfterCommit FROM fn_dblog(NULL, NULL)
where [Transaction ID] = 
(
SELECT TOP 1 [transaction id] FROM fn_dblog(NULL, NULL)
WHERE [transaction name] = 'DirtyDataLoadTest2'
)

Dwa rekordy zostały wprowadzone do dziennika jeden zaiwerający informacje o otwarciu transakcji oraz drugi o wstawieniu wiersza. Po zatwierdzeniu transakcji pojawił się jeden dodatkowy wiersz z informacją o tym własnie zatwierdzeniu. Tak więc mamy potwierdzenie co do tego, że w przypadku tabel memory optimized do dziennika trafia tylko to co zostało już zatwierdzone.

Jak widzicie dziennik transakcyjny zachowuje się inaczej w stosunku do technologii in-memory OLTP. W tym przypadku jest to mechanizm dużo wydajniejszy dzięki licznym usprawnieniom, dodatkowo działa on z takimi technologiami jak np. Delayed Durability czyli tak naprawdę mamy do dyspozycji trzy scenariusze: brak logowania z tabelami nietrwałymi, logowanie opóźnione z tabelami trwałymi z włączonym Delayed Durability oraz logowanie pełne z tabelami trwałymi bez Delayed Durability. Całkiem sporo możliwości i myślę, że to bardzo dobra wiadomość bo im więcej mamy możliwości tym większa szansa, że technologia sprosta naszym oczekiwaniom i scenariuszom w jakich chcielibyśmy jej użyć.

Adrian Chodkowski
Follow me
Latest posts by Adrian Chodkowski (see all)

Leave a Reply