I recently read Microsoft’s research paper on Hekaton, the new in-memory database engine in SQL Server 2014. When I first heard about it, I was pretty excited, but after reading the deep dive specs, I’m ecstatic. I highly recommend checking out the research paper in its entirety, but for those who prefer a faster read, I’ll highlight some of the key points that made me tilt my head like a perplexed labrador retriever.
Hekaton? Yes, Vince, Hekaton.
What is it?
Hekaton is an in-memory, fully integrated database engine that is new in SQL Server 2014. Hekaton sits alongside the traditional RDBMS and creating a table in this new engine is as simple as declaring a table ‘memory optimized’.
Key Design Features
Nice to know you, locking and waiting
If you’re a DBA, then you’re probably in a constant battle with reducing wait times and blocking. By using a new row multiversoning concurrency system, Hekaton makes latching and locking a thing of the past. Maintaining versions of rows internally for all data access may sound expensive on the surface, however all version management and garbage collection occur soley in memory.
Machine code stored procedures
When you execute a stored procedure today, that stored procedure is converted into instructions behind the scenes that the server can actually make sense of. This is great for us because we don’t have to learn to write machine code, but this flexibility means longer run times. Hekaton was designed with stored procedure workloads in mind. When a stored procedure that references a table stored in the Hekaton engine is created, it is compiled as machine code upon creation. By offloading this activity to creation time instead of run time, the engine already has exactly what it needs when the stored procedure is executed. What about ad hoc queries, you ask? These are still supported via a query interop component that provides operators to handle them. You’ll still see the benefit of querying a table that lives entirely in memory, you just won’t be double dipping on the performance gains.
High performance without partitioning
Many in memory database solutions out there today use partitioning across CPU cores to yield high performance and if you don’t have a partitioned optmized workload, you’re out of luck. Hekaton uses a non-partitioned approach, which makes it an option for a larger audience.
In-Memory Doesn’t Mean Sacrificing Durability
Another huge part of a DBA’s core duties is ensuring integrity and availability of the database. Taking advantage of a table stored in memory sounds great, but if that came at the risk of data loss, it would have very few applications. The SQL Server development staff didn’t disappoint.
Logging, checkpointing, and AlwaysOn compatible
Hekaton uses a combination of the SQL Server transaction log and checkpoint streams stored on the storage subsystem to keep track of changes. The combination of these operations ensures that SQL Server can recover an in-memory table to a transactionally consistent state. Changes are recorded as inserts and deletes of row versions, labeled with the table they belong to. AlwaysOn can also be utilized without fear of impacting tables stored in the Hekaton engine.
Just How Fast Is it?
Fast, real fast. In Microsoft’s experiment results, a transaction performing 10,000 lookups saw 9.85 million CPU cycles used when querying a table stored in Hekaton, vs 201 million CPU cycles when using the regular SQL Server engine. That’s a 20x performance increase. In a test of 10,000 updates, it got even better – a 30x performance increase was realized.
Final thoughts
Although it may be a while before I’m supporting a production environment that is taking advantage of Hekaton, I can’t wait to get my hands on it. I love using new technology to make things perform better, and Hekaton sounds like a dream feature in that regard. In a future post, I’ll dive into the latest CTP release of SQL Server 2014 and set up a memory optimized table in Hekaton.
