In-Memory OLTP (Hekaton)

In-Memory technology in SQL Server 2014

(Code name Hekaton)

 

Like the other players in the database space, Microsoft has started evolving in the In-Memory database technology a way back in 2009. After 4years they started working for it, we have now got the initial release of it released with SQL Server 2014. Microsoft has announced the release of SQL Server 2014 in the TechED2013 and you can download the CTP1 version of it today. I have downloaded my copy and started learning this as this concept can address many concerns that the previous releases can't address. I would like to take an opportunity to share my experience and knowledge about it.

 

What is In-Memory technology ?

In simple words, I can say that this technology felicitates the possibility to hold the data of a database completely with in the memory (RAM). Having the whole data available in the RAM, we can overcome the latency issues of the IO Disk subsystem.

 

How Hekaton is different from normal SQL Server database engine ?

The moment I started learning the In-Memory, I was scratching my head to understand how this is really benefit if we have more amount of RAM available in the server compared to the database size. Well, even if we have more amount of RAM available for SQL Server compared to the database size, we still need to operate with the 8KB sized data page(s) even in bufferpool after it is completely read in to the buffer. Imagine if we need to search for a row from a table which satisfies my search condition, SQL Server needs to retrieve couple of data pages from Disk to bufferpool and get the row displayed. In doing so, SQL Server would uses locks and latches to maintain the logical and physical consistency of the data. So, even if we have more amount of RAM available, we have no option to mitigate the lock and latch contention. Also, when a page is locked or latched just to operate with some portion of it, the other session gets blocked which needs to operate the different portion of the data in the same page. The Hekaton addresses these concerns mainly. With Hekaton, the database engine becomes lock and latch free.

 

Is this In-Memory a new separate release of SQL Server ?

Microsoft has introduced this technology as a feature in SQL Server 2014 similar to any other features like AlwaysON AGs etc. Which means that, we can choose tables which are to be memory optimized.

 

What is memory optimized ?

Now with SQL Server 2014, we have two types of tables available which are nothing but disk based and memory optimized. The memory optimized tables are used by Hekaton and not maintained in 8KB allocation units. The disk based tables are the conventional tables which are stored in 8KB allocation units on the disk. I will talk more about this in a separate blog.

 

Can I have both disk based and memory optimized tables for a single database?

Yes, we have the option to migrate selective tables to Hekaton of a database.

 

Which editions of SQL Server provides this feature?

This would be made available in Enterprise, Developer and Evaluation editions of SQL Server 2014.

Any other features available in Hekaton ?

Yes, the Stored Procedures can be now native which means the SPs can now be compiled and executed as C programs which requires less CPU cycles compared to the normal SP execution process by SQL Server. Under the hoods, the SP gets converted as a C program and execute the login we specify in the SP.

 

What is the benefit of Native SPs ?

The CPU execution speed is not growing aggressively like before. So, we may have CPU bottleneck when we deal with complex SP execution. Using the native SPs, the logic can be executed with less no. of CPU cycles with which we can overcome the issue of CPU bottleneck.

 

Will I see a guaranteed benefit by moving tables to Memory optimized or using Hekaton?

Well it depends. If there is no latch or lock contention exists for the tables with the regular workload, there may not be a benefit of migrating them to memory optimized.

 

What is the impact of Paging with Hekaton ?

No Paging.
 

What happens if there is no free memory available for new inserts ?

Well, SQL Server just rejects the new transactions. It is like a transactionlog file when there is no free space available in the Transactionlog, new modifications are not allowed.

 

Any maximum size limit exists for a table which is memory optimized ?

Yes as of CTP1, SQL Server 2014 supports a max of 512GB.

 

Is there any change for the max length of the row allowed with Hekaton ?

Like the normal database engine, Hekaton supports up to 8060 Bytes per ROW.

 

Does Hekaton uses the BufferPool ?

No, Hekaton uses non bufferpool area of memory portion.