Memory optimized tables a dziennik transakcyjny w SQL Server- seequality

Adrian Chodkowski July 10, 2017 0 Comments

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

--check if database exists

DROP DATABASE IF EXISTS Hekaton_Demo

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

)

--add special filegroup to database

ALTER DATABASE Hekaton_demo

ADD FILEGROUP AWMemGroup CONTAINS MEMORY_OPTIMIZED_DATA

--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]

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

USE Hekaton_demo

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)

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)

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)

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)

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)

CREATE TABLE dbo.HeapDiskTable (

TableId INT IDENTITY(1,1),

AdditionalId int NOT NULL,

LoginGUID nvarchar(50),

CreatedDate DATETIME2 NOT NULL,

)

WITH (MEMORY_OPTIMIZED=OFF)

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

USE Hekaton_demo

SET NOCOUNT ON

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

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

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

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

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

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

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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

USE Hekaton_demo

with HeapDiskTable

(

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

(

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

(

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

(

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

(

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

(

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'
)

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'
)

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'

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'

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'
)

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'
)

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