Tag Archives: Introduction to In-Memory OLTP

Introduction to In-Memory OLTP (a.k.a. Hekaton) – Sql Server 2014

Hekaton, In-Memory OLTP, Sql Server, Sql Server 2014, , , , , , , , ,

This is the first article in the series of the articles on the topic “In-Memory OLTP (a.k.a. HEKATON a.k.a. eXtreme Transaction Processing) and its Internals – Sql Server 2014”. Below is the list of other articles in this Series:

For In-Depth detailed information on the In-Memory OLTP(a.k.a. Hekaton), you may like to download the In-Memory OLTP White Paper written by Kalen Delaney or you can visit MSDN. You can as well download Sql Server 2014 CTP2.

WHAT is In-Memory Online Transaction Processing (a.k.a. Hekaton)?

In-Memory Online Transaction Processing formerly know as Hekaton is a database engine component which is optimized for accessing Memory resident tables. This component is fully integrated into Sql Server 2014 relation database engine, it is not a separate database product. It facilitates creation of Memory resident Tables (i.e. Memory Optimized Tables) and Indexes. It also provides the option to compile the Transact-Sql Stored Procedure accessing Memory Optimized Tables to Machine code. These Natively compiled stored procedures referencing Memory Optimized Tables provide better performance compared to the Transact-Sql Statement or Stored Procedure accessing Memory Optimized Tables.

Memory Optimized Tables provide better performance as the core engine uses the lock free algorithm (i.e. MultiVersion Optimistic Concurrency Control) which doesn’t require any lock and latches when the Memory optimized tables are referenced during the transaction processing. And for supporting Data Durability it still writes to the transaction log but the amount of data which is written to the log is reduced considerably. As the Indexes of the Memory Optimized Tables are only in Main Memory they will not be persisted on the disk, so to the log only the committed table data is written.

On an average it improves the performance of OLTP applications by 10x without having to re-write entire application (i.e. Just by migrating frequently used disk based tables by the OLTP application to Memory optimized table).

WHY In-Memory OLTP (a.k.a. Hekaton)?

Sql Server database engine was designed in the days when Main Memory was very costly. As per this design data is stored on the disk and is loaded to the main memory as required for the transaction processing and any changes to the In-Memory data is written back to the disk. This disk IO is main bottle neck for the OLTP applications having huge number of concurrent users, as it involves waiting for locks to be released, latches to be available, waiting for the log writes to complete.

As per the current trend Main Memory prices are less expensive and enterprises can easily afford to have production database servers with Main Memory sizes in TB’s. And this declining Memory prices made Microsoft to re-think on the initial database engine which is designed in the days when Main Memory was costly. And the result of this re-think is the In-Memory OLTP (a.k.a. Hekaton) Database engine component which supports memory resident Tables and Index. In-Memory OLTP engine uses the lock free algorithm (i.e. MultiVersion Optimistic Concurrency Control) which doesn’t require any lock and latches when the Memory optimized tables are referenced during the transaction processing. And for supporting data durability it still writes to the transaction log but the amount of data which is written to the log is reduced considerably.

Key Features of In-Memory OLTP (a.k.a. Hekaton)
Memory Optimized Tables
  • Data of the Tables which are declared as Memory Optimized will reside in the Main Memory.
  • Access to the Memory Optimized Table data doesn’t require the data to be loaded from disk to memory as complete Table and Indexes data is already present in the Memory.
  • In-Memory OLTP doesn’t use the data pages and extents for storing Memory Optimized Table data. Instead it uses a special row structures. Each Row will have a Row Header, which contains the row’s Begin and End time. This Begin and End time is used to determine the validity of the row. Apart from this each row will have Index Pointers and the count of the number of indexes that are referencing it. All the tables data is linked by the indexes, so the requirement of minimum one Index for Memory Optimized Table.
  • Memory optimized Tables Schema modification is not Supported.
  • No Foreign Key and Check Constraints Supported.
  • LOB Data Types are not Supported.
  • Doesn’t Support Identity and Calculated Columns.
In-Memory Indexes
  • Index for the Memory Optimized Table exists only in Main Memory and they are never stored on the disk. Index’s are updated only in Memory and it will not be logged/written to the Transaction Log.
  • On System restart they are re-created as the table data is streamed into the memory.
  • Hash index and Range Index are the types of indexes supported by memory optimized tables.
  • And Each memory optimized table need to have at least one Non-Clustered Index. As index is the one with which complete Table data is linked.
  • A Memory Optimized Table can have Maximum of 8 Indexes.
Natively Compiled Stored Procedures
  • The Memory Optimized Tables and the Stored Procedure referencing them can be compiled to Machine code via C Code generator and Visual C Compiler.
  • Aggressive optimization is done at compile time i.e. when creating the Natively compiled Stored Procedures.
  • Call to Such Stored Procedure is just a dll entry Point.
  • Natively compiled stored procedures referencing Memory Optimized Tables provide better performance compared to the Transact-Sql Statement or Stored Procedure accessing Memory Optimized Tables.
High Concurrency
  • In-Memory OLTP uses True MultiVersion Optimistic Concurrency Control, which doesn’t require any Lock and Latches when the Memory Optimized Tables are referenced during the transaction processing.
  • In-Memory OLTP maintains the multiple versions of the Row’s. Each Row will have a Row Header, which contains the row’s Begin and End time. This Begin and End time is used to determine the validity of the row.
  • For instance any request for the Data Row, the In-Memory OLTP engine gives the version of the row which is current at the time it is requested. Consider a scenario where two transactions get the same version of the record and trying to update it with different values. In such a Write-Write conflict scenario the transaction which is updating last will fail as the record which it is manipulating is not a current version of the record while committing the transaction.
Data Durability
  • In-Memory OLTP provides an option to create Memory Optimized Tables which can have Durable Data. If tables are created with this option, data will not be lost even upon System Restart/Shutdown/Crash.
  • Data Durability is achieved by the Data and Delta files in the Memory Optimized file group and by writing to the transaction log but the amount of data which is written to the log is reduced considerably. Here Data file of 128 MB size will have the inserted rows, whereas Delta file will have the ID’s of the Deleted rows.
Optimized Transaction Log
  • There will not be any transaction logging for the Memory Optimized Tables which are created without Data Durability option.
  • And for the Memory Optimized Tables which are created with Data Durability option, will write to the same Transaction Log which is used by the Disk Based tables.
  • But the amount of data which is written to the log is reduced considerably. It is because: 1) Index for the Memory Optimized Table exists only in Main Memory and they are never stored on the disk. Index’s are updated only in Memory and it will not be logged/written to the Transaction Log. 2) Only Committed data is written to the Transaction Log and UNDO transactions are not logged.
Backup & Restore
  • Sql Servers Database Backup option, back’s up the memory optimized file group.
  • Transaction Log backup includes Memory Optimized Table log records.
  • Differential Backup is not supported.
No Buffer Pool In-Memory OLTP Engine doesn’t use the Buffer Pool, because the Memory Optimized Tables and Indexes data is already in Memory.
Sql Server Integration In-Memory OLTP database engine component is fully integrated into Sql Server 2014 Relation Database engine, it is not a separate database product. The Same SSMS which is used for the Disk Based Tables/Indexes is used for the Memory Optimized Table’s and Indexes. In that way, it provides the same development, Management and administration experience.
Garbage Collection As Update and Delete operation on the Memory Optimized Table creates multiple version of the rows. And the rows which becomes stale will unnecessarily consuming the Main Memory. For this In-Memory OLTP engine has a Garbage Collection System, which scans periodically and removes such stale rows.

Note: The learning experience expressed in this article are based on the Sql Server 2014 – CTP2, it may change in the final release of the Sql Server 2014.

Please correct me if any of my understanding is not correct.