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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
--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)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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:
1 2 3 4 |
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:
1 2 3 4 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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ć.
- Azure Data Factory – Lookup i Foreach - January 17, 2021
- Nagrania naszych sesji z SQLDay 2019! - December 29, 2020
- SQLDay 2020! - November 28, 2020
Ostatnie komentarze