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:
- Myths and Facts about In-Memory OLTP (a.k.a. Hekaton) – Sql Server 2014
- Working with In-Memory OLTP (a.k.a. Hekaton) enabled Databases, Memory Optimized Tables and Natively Compiled Stored Procedures and it’s Internals with extensive list of Examples – Sql Server 2014
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
|Natively Compiled Stored Procedures
|Optimized Transaction Log
|Backup & Restore
|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.
|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.