In-Memory Is New #2: New Transaction Log

In SQL Server 2014, transaction log has also been enhanced to provide better performance for memory-optimized tables. Let’s see some real cases.

 

First thing is to create a test db. If you read my previous blog post, this is the same script I used:

USE MASTER;

GO

CREATE DATABASE DB1;

GO

ALTER DATABASE DB1 ADD FILEGROUP IMOLTP CONTAINS MEMORY_OPTIMIZED_DATA;

GO

ALTER DATABASE DB1 ADD FILE (NAME=‘IMOLTP’,FILENAME=‘C:\DB\IMOLTP’) TO FILEGROUP IMOLTP;

GO

USE DB1;

GO

 

CREATE TABLE T1 (ID INT PRIMARY KEY, MSG CHAR(8000));

GO

CREATE TABLE T1_OLTP (ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000), MSG CHAR(8000)) WITH (MEMORY_OPTIMIZED = ON)

GO

 

Now DB1 is ready. Before we start testing, let’s insert one record into both tables to initialize a new page (memory-optimized table doesn’t use page, but let’s do it anyway for the sake of fairness).

INSERT INTO T1 VALUES (0,REPLICATE(‘A’,8000));

GO

INSERT INTO T1_OLTP VALUES (0,REPLICATE(‘A’,8000));

GO

 

The test is to insert 5 records into both tables and see how the transaction log behaves.

 

Let’s start with disk-based table T1.

Before we insert, let’s write down the how many log record we have so we can have a delta after:

SELECT * FROM sys.fn_dblog(NULL,NULL)

There are currently 703 log records. We will use these number later.

 

Now, let’s insert:

BEGIN TRAN 

DECLARE @N INT = 1;

WHILE (@N < 6)

BEGIN

    INSERT INTO T1 VALUES (@N,REPLICATE(‘A’,8000))

    SET @N = @N + 1

END

 

COMMIT TRAN

GO

 

This transaction completes successfully. Let’s check transaction log again.

SELECT * FROM sys.fn_dblog(NULL,NULL)

There are 803 log records now, 100 more records generated for inserting 5 rows in T1 table.

 

What happens to memory-optimized table?

Let’s insert 5 records into T1_OLTP table:

BEGIN TRAN

DECLARE @N INT = 1;

WHILE (@N < 6)

BEGIN

    INSERT INTO T1_OLTP VALUES (@N,REPLICATE(‘A’,8000))

    SET @N = @N + 1

END

COMMIT TRAN

GO

 

How many transaction log do we have now?

SELECT * FROM sys.fn_dblog(NULL,NULL)

813 record, which means there were only 10 records generated for memory-optimized table.

 

In this test, memory-optimized table only logs 10% of logs disk-based table does. Which significantly reduces the number of IO and improve overall performance.

In the meantime, if you look at the “Log Record Length” of memory-optimized table, they are also greater than disk-based table:

This is because SQL Server combines the log records into bigger chunks in order to reduce the total number of IO writes.

 

Furthermore, memory-optimize tables don’t generates logs for rolled backs transactions while disk-based tables still do.

Again, let’s test:

CHECKPOINT;

GO

DECLARE @cnt INT, @cnt2 INT;

SELECT @cnt =COUNT(*) FROM SYS.FN_DBLOG(NULL,NULL) 

 

BEGIN TRAN

INSERT INTO dbo.t1 VALUES  (101,REPLICATE(‘a’,8000)),
(102,REPLICATE(‘b’,8000)),

(103,REPLICATE(‘c’,8000)),

(104,REPLICATE(‘d’,8000)),

(105,REPLICATE(‘e’,8000))
ROLLBACK TRAN

SELECT @cnt2=COUNT(*) FROM sys.fn_dblog(NULL,NULL);

SELECT ‘Disk-based Table: # of logs generated for rollback’, @cnt2@cnt AS ROW_CNT

GO

 

DECLARE @cnt INT, @cnt2 INT;

SELECT @cnt =COUNT(*) FROM SYS.FN_DBLOG(NULL,NULL)

BEGIN TRAN
INSERT INTO dbo.T1_OLTP VALUES  (101,REPLICATE(‘a’,8000)),
(102,REPLICATE(‘b’,8000)),

(103,REPLICATE(‘c’,8000)),

(104,REPLICATE(‘d’,8000)),

(105,REPLICATE(‘e’,8000))

ROLLBACK TRAN

SELECT @cnt2=COUNT(*) FROM sys.fn_dblog(NULL,NULL);

SELECT ‘Memory-Optimized Table: # of logs generated for rollback’, @cnt2@cnt AS ROW_CNT

GO

 

This test inserts 5 records in both disk-based table and memory-optimized table and roll them back. It calculates the number of transaction logs generated in both cases. Here are the returned results:

 

As we can see from this screen shot, the memory-optimized table generated no log during transaction rolling back while disk-based table generated 108 log messages. This is another improvement memory-optimized tables have over disk-based tables!

Summary

Memory-optimized tables are using a newly designed, more efficient transaction log. This new transaction log allow memory-optimized table to reduce physical IO significantly and improve performance. Transaction Log in In-memory is new!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s