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 blogs, 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 SQL Server transaction log will behave.

 

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 in SQL Server 2014 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 insert 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, 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!

In-Memory Is New #1 – New Transaction Isolation Level

SQL Server 2014 supports 4 types of transaction isolation levels:

  • READ COMMITTED
  • SNAPSHOT
  • REPEATABLE READ
  • SERIALIZABLE

READ COMMITTED transaction isolation level is only supported in auto-commit transactions and cannot be used within an explicit transaction. On the surface, the other 3 transaction isolations types look exactly like same as transaction isolation levels in disk-based tables with same names. Really?

 

Let’s take a look at how memory-optimized tables in snapshot isolation.

 

Disk-Based tables

First thing, create a test database – db1 and a test table table1:

CREATE DATABASE db1;
go
ALTER DATABASE db1 SET ALLOW_SNAPSHOT_ISOLATION ON
go
use db1;
go
CREATE TABLE t1 (id INT, msg varchar(20));
go
INSERT t1 VALUES (1,‘abc’);
go

 

Step 1: start transaction TX1 in session 1 and update table t1 (id = 1)

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
go
BEGIN TRAN TX1
UPDATE t1 SET msg =‘session 1′ WHERE id = 1

 

 

Step 2: start transaction TX2 in session 2 and update table t1 (id =1)

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
go
BEGIN TRAN
UPDATE t1 SET msg =‘session 2′ WHERE id = 1

 

TX2 is now being blocked by TX1

 

Step 3: commit transaction TX1 in session 1

COMMIT TRAN TX1

 

In disk-based environment, TX2 is rolled back immediately after TX1 commits. Note TX2 could continue if TX1 rolls back instead of commits.

 

Following picture shows how TX1 and TX2 behave in a update conflict scenario:

 

 

Memory-Optimized Tables

Now, let’s take a look at how memory-optimized tables behave in snapshot transaction isolation level.

First thing (again) – update database and create a memory-optimized table:

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
create table t1_oltp
(id int not null primary key nonclustered hash with (bucket_count = 000000),
msg char(20)) with (memory_optimized = on)
go

 

Step 1: begin transaction TX1 and update row (id = 1)

begin tran TX1
update t1_oltp with (snapshot) set msg=‘TX1′ where id = 1

 

Step 2: begin transaction TX2 and update same row (id=1)

begin tran TX2
update t1_oltp with (snapshot) set msg=‘TX2′ where id = 1

 

SQL Server detects a write-write conflict and rolls back TX2 immediately:

Memory-optimized tables in snapshot transaction isolation:

Summary

Memory-optimized tables are using a newly designed, lock-free optimistic transaction isolation level which works differently comparing to traditional disk-based transaction isolation levels. Transaction isolation level in In-memory is new!

Memory Optimized Table – Insert Test …Again

Last time my tests showed no major difference on simple inserting between disk-based table and memory-optimized table. On the opposite side, there is a Microsoft published script and test process to prove how much performance gain we can get using memory-optimized tables. This test, shows positive performance gains. Below is the test result I got from my VM:

 

 

This test had been done under different workload (5K, 10K and 15K) and different parallel connections (10 threads, 20 threads and 30 threads). The test result of this test shows memory-optimized table is actually faster than disk-based table, and it’s much more scalable than disk-based table.

 

Where did the difference come from? In Microsoft’s testing script, all the inserts are done through stored procedures, either native compiled procedures or traditional stored procedures. The performance gain we saw in this test is actually coming from compiled procedures. It also demonstrates traditional stored procedure is less memory-friendly!

 

Microsoft’s testing script can be downloaded here: http://msftdbprodsamples.codeplex.com/releases/view/114491

 

Enjoy!

Memory-Optimized Tables – Insert Test (continued)

Memory-optimized tables didn’t show much performance gain during the test last time. So I have decided to do more tests – just inserting tests and see how well memory-optimized tables perform.

 

Last time my VM had 2GB RAM and memory-optimized table could only hold 150K rows. To be able to test more cases, I added another 6GB and made it 8GB this time. In the meantime, I have also added 3 more logical CPU cores.

 

The tests I’m going to do this time is to compare insert performance of memory-optimized tables and disk-based tables under different workload: 50K, 100K, 150K, 200K, 250K and 300K row inserts. And I’m going to run these tests 3 times:

 

First run:

Second run:

 

Third run:

 

The results seem to be consistent – memory-optimized table showed performance gain only under 200K to 300K workload. For less heavy workload (<200K), disk-based table can perform just as well as memory-optimize table.

 

From this test, memory-optimized table seems to be working better under heavy workload. To prove this, I once again, added 4 GB to the testing VM and expand the test row set up to 450K. Again, 3 times:

 

First run:

 

Second run:

Third run:

 

The results are close – disk-optimized table can perform just as well as memory-optimized table under given workload. Unfortunately, I don’t have a power server handy for more tests. For now, I will consider memory-optimized table has no apparent performance gain over traditional disk-based table.

Disk_based Table_Memory_based_table

Memory-optimized Table – Day 1 Test

I had been checking Microsoft’s website since 12:00AM April 1st. Finally downloaded and installed it. After my SQL Server 2014 Enterprise Edition is up and running on my Windows Server 2012R2, I have decided to do some quick test:

 

First thing first, create a test environment:

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

 

2 Tables are created, one is disk-based table called t1 and another one is memory-optimized table called t1_oltp. They have same definition so the results will be comparable.

 

Now, let’s run some tests:

Test #1: Insert Operation:

 

declare  @start datetime2;select @start = getdate(); set statistics time on

go

declare @n int = 1;

while (@n < 1000000)

begin

    insert into t1 values (@n,replicate(‘a’,8000)) 

    set @n = @n +

end

select datediff(ms,@start,getdate())

go

declare @start datetime2; 

select @start = getdate();

declare @n int = 1;

while (@n < 1000000) 

begin    

insert into t1_oltp values (@n,replicate(‘a’,8000)) 

set @n = @n + 1

end

select datediff(ms,@start,getdate())

GO

 

The plan was to insert 1M rows and compare the performance. However, it turned out to be I was way too optimistic about the capability of my VM. SQL Server showed this error while inserting:

 

It’s good that SQL Server actually has a hard limit (80%) on how much memory memory-optimized tables could use. This helps SQL Server to ensure memory-optimized tables won’t consume all SQL Server has and leave other tables starving to death.

 

Now I will have to reduce the amount of record for this test, I’m going to be conservative this time – 50K records is what I will be testing on.

 

The results are:

Disk-base table: 33323ms

Memory-optimized table: 124017ms

 

Surprisingly, memory-optimized table is much slower compare to disk-based table. MSDN explicitly mentioned high insert rate is one of the scenarios we should consider memory-optimized table:

http://msdn.microsoft.com/en-us/library/dn614087(v=sql.120).aspx

“High data insert rate

One of the critical needs of high performance applications is an extremely high data loading rate. The typical pattern is a need to ingest data where due to either high rate fluctuations and spikes, the workload requirements exceed the capabilities of a traditional RDBMS. Some business cases which may use this solution include smart metering, or repositories for telemetry or performance data ingestion.”

 

This test makes no sense. Before I start investigating what happened to my insert test, I decided to continue finish the rest of my test.

 

Test #2: Select Operation – Table Scan:

set  statistics time onselect * from t1
select * from t1_oltp
set statistics time off

 

 

Result:

(49999 row(s) affected)

 

SQL Server Execution Times:

CPU time = 2047 ms, elapsed time = 27617 ms.

 

(49999 row(s) affected)

 

SQL Server Execution Times:

CPU time = 1703 ms, elapsed time = 59434 ms.

 

Here we see some performance improvement.

 

 

Test #3 Select – Seek:

set  statistics time on select * from t1 where id = 25000
select * from t1_oltp where id = 25000
set statistics time off

 

Result:

(1 row(s) affected)

 

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 44 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

 

(1 row(s) affected)

 

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 37 ms.

 

Hard to tell, both are lightning fast! However, notice that there is difference, disk-based table has an entry called “parse and compile” time while memory-optimized table doesn’t. This is one of the benefits of compile object SQL Server 2014 brings.

 

Test #4: Delete:

set  statistics time on delete from t1 delete from t1_oltp

set statistics time off

Result:

SQL Server Execution Times:

CPU time = 13328 ms, elapsed time = 24345 ms.

 

(49999 row(s) affected)

 

SQL Server Execution Times:

CPU time = 219 ms, elapsed time = 208 ms.

 

(49999 row(s) affected)

 

Here we see a big difference, 98% less in CPU time, WOW!

 

Test #5: Bulk load:

 

Create a staging table for bulk load:

–staging table
create table t2 (id int primary key, msg char(8000));godeclare @n int = 1;

while (@n < 50000)

begin

    insert into t2 values (@n,replicate(‘a’,8000))

    set @n = @n + 1

end

 

Now let’s load them:

set statistics time on
insert into t1
select * from t2 insert into t1_oltp

select * from t2

set statistics time off

 

Result:

SQL Server Execution Times:

CPU time = 9765 ms, elapsed time = 18497 ms.

 

(49999 row(s) affected)

 

SQL Server Execution Times:

CPU time = 1235 ms, elapsed time = 39432 ms.

 

(49999 row(s) affected)

 

To review the tests we have done so far:

Disk_based Table_Memory_based_table

Except for my suspicious insert operation and my miscalculation about the number of 50K rows (49,999 rows instead), all results are positive and performance friendly. Just keep in mind memory-optimized tables are memory monsters and they do consume A LOT memory and default resource pool has a hard limit on how much memory-optimized tables can use, that is 80%.

 

And, I will look into what happened to my insert test in my next blog.

Is your hard drive ready for SQL Server

Back in the age of Windows Server 2003, there was a best practice to align disk partitions for SQL Server for performance gain: http://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx

 

This seems old, right? Come on, we are using SQL Server 2014 on Windows Server 2012 R2 now!

 

Well, yesterday I happened to come across this white paper again and wanted to check one of our new servers and guess what – the cluster size on this new box is using default 4096 bytes instead of 64KB which is a best practice:

(Screenshot was taken on my VM, not the actual production server)

I have decided to blog this so I can remind myself in the future – change the cluster size for SQL Server!