First of all, I’d like to point to the fact that there is almost always a bottleneck with OLTP database’s performance that is related to disk speed. So in-memory processing can improve the current poor performance with disk-based tables significantly as well as achieving scalability.
In a very simple and quick explanation, Hekaton is new feature coming with SQL Server 2014 to handle in-memory processing for OLTPs. Actually it is something live xVelocity for OLAP. But, how Hekaton can help for improving OLTP performance and scalability? The answer is that SQL Server 2014 is trying to achieve these goals by using
- Optimised algorithms to access memory-resident data
- Eliminating logical locks using a concurrent control method for RDBMS (Relational Database Management Systems) that assumes that multiple transactions can complete without affecting each other, so that, the transactions can commit without locking the source data. This is called Optimistic Concurrency Control (OCC).
- Using lock-free objects to access all data, so threads that perform transactional work do not use locks for concurrency control.
- Using a new concept in SQL Server 2014 called Native Compilation. Native compilation refers converting programming constructs to native code, which consists of processor instructions that can be executed by the CPU without the need for further compilation or interpretation. So, SQL Server can natively compile stored procedures that access memory-optimized tables. Native compilation allows faster data access and more efficient query execution, than traditional, interpreted Transact-SQL.
Microsoft claims that “Use of main memory can result in performance gains from a few percentage points of performance improvement, to 20 times performance improvement.”
There are some important points for those who are thinking of migrating from disk-based tables to in-memory OLTP:
- Some syntax changes to tables and stored procedures will be required
- In addition to the hardware and software requirements for installing SQL Server 2014 you need to keep in mind that to use in-memory OLTPs you need to install 64-bit edition of SQL Server 2014 and you need to have a server with enough memory to host in-memory OLTP data. Note that in-memory OLTP data must NOT use more than 80% of the maximum server memory.
So, before migrating from disk-based OLTP to in-memory OLTP Microsoft recommends to use “Analysis, Migrate and Report Tool” (AMR) that is installed when you select “Management Tools- Complete” when installing SQL Server 2014.
If you want to test Hekaton you can see excellent sample codes here.