Transaction Logging in In-Memory OLTP (Hekaton)

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

In today’s blog posting I want to talk in more detail about how In-Memory OLTP logs transaction log records into the transaction log. As you might know, In-Memory OLTP offers you 2 durability options for your Memory Optimized Tables:

  • SCHEMA_ONLY
  • SCHEMA_AND_DATA

I don’t want to talk more about SCHEMA_ONLY today, because with this option no logging happens at all in the transaction log (your data doesn’t survive a SQL Server restart). Today we will concentrate on the durability option SCHEMA_AND_DATA.

SCHEMA_AND_DATA

With the durability option SCHEMA_AND_DATA SQL Server always has to log your transactions into the transaction log, because every In-Memory OLTP transaction must always be durable. It’s the same basic concept as with traditional Disk Based Tables. But writing to the transaction log in In-Memory OLTP is much more optimized than with traditional tables. In-Memory OLTP supports multiple concurrent log streams (which is currently not implemented in SQL Server 2014), and In-Memory OLTP just logs the logical transaction that happened.

What do I mean by logical transaction? Imagine you have a traditional Clustered Table with 5 Non-Clustered Indexes defined on it. If you insert a record into that table, SQL Server has to log the insert into the Clustered Index, and also the 5 additional inserts into the Non-Clustered Indexes. The more Non-Clustered Indexes you define on your table, the more SQL Server has to log. And SQL Server can be only as fast as the transaction log.

Things change with In-Memory OLTP. In-Memory OLTP SQL Server just logs that a logical modification has happened in your transaction. SQL Server does not log the modifications that happened in your Hash- or Range-Indexes. So it’s just one log record that describes the logical INSERT/UPDATE/DELETE statement that has happened. As a result, In-Memory OLTP writes less data to your transaction log, and therefore your transactions can be committed much faster.

Let’s prove it!

I want to show you with a simple example how much data SQL Server writes to your transaction log when you insert 10000 records first into a traditional Disk Based Table and then into a Memory Optimized Table. The following code creates a simple table, and inserts the 10000 records in a while loop. Afterwards I use the (undocumented) system function sys.fn_dblog to look into the transaction log.

As you can see from the output of the system function, you have a little bit more than 20000 log records. This makes complete sense, because we have 2 indexes defined on that table (1 Clustered Index, 1 Non-Clustered Index).

A Disk Based Table gives you around 20000 transaction log records

Now let’s have a look at how the logging changes with a Memory Optimized Table. The following listing shows the code that is necessary to prepare our database for In-Memory OLTP: we just add a new Memory Optimized File Group, and add a container to it.

In the next step I create a new Memory Optimized Table. I choose here a bucket count of 16384 on the hash index to avoid possible hash collisions. And in addition I create 2 Range Indexes on the columns Col2 and Col3.

In sum we have 3 indexes on that table (1 Hash Index and 2 Range Indexes). When you then insert 10000 records into the table, a traditional table would generate around 30000 log records – one log record for every insert in every index. But now when you look again at the transaction log, you can see that the 10000 inserts have generated just a few log records – 17 in our case!

With a Memory-Optimized Table we need only 17 transaction log records

The magic happens inside the LOP_HK log record. Inside this specific log record In-Memory OLTP bundles multiple changes to your Memory Optimized Table. You can also break down LOP_HK log records by using the system function sys.fn_dblog_xtp:

As you can see from the following picture, In-Memory OLTP has only generated around 10000 LOP_HK log records – one log record for every logical insert that has happened on this table.

With sys.fn_dblog_xtp you can break down LOG_HK log records

Summary

In-Memory OLTP provides you with amazing performance improvements, because it is based on completely new principles like MVCC and Lock-Free Data Structures. In addition it also generates fewer transaction log records, because only logical changes are logged into the transaction log. I hope that I have given you with this blog posting a better understanding how In-Memory OLTP improves your transaction log throughput.

Like or share to get the source code.

Thanks for your time,

-Klaus

It`s your turn

Your email address will not be published. Required fields are marked *

SQLpassion

Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top