Without a doubt, the most significant feature in the upcoming SQL Server 2014 release is the new In-Memory OLTP Engine (formerly code-named Hekaton). The past couple of SQL Server releases have had a strong focus on business intelligence, which makes the new In-Memory OLTP Engine a refreshing change for relational database professionals, with its promises of performance improvements.
I covered Hekaton when it was first announced, and with the recent SQL Server 2014 CTP coming, it’s time to look again in more detail. Here are some of the interesting FAQs about SQL Server 2014’s In-Memory OLTP Engine:
Q: What kind of performance gains can I get from the In-Memory OLTP Engine?
A: As you might expect, the real answer to this is “it depends.” Clearly, the actual improvements will depend a lot on the application. In the SQL Server Blog, the SQL Server Team stated that most organizations can expect a 5x – 20x performance improvement. Even better improvements are possible: Microsoft demonstrated a 30x performance improvement in its PASS 2012 presentation of bwin’s on-line gaming application.
Q: What are the requirements for the In-Memory OTLP Engine?
A: The In-Memory OLTP Engine requires the 64-bit edition for SQL Server and therefore also requires a 64-bit hardware platform. That’s a pretty low bar these days. It doesn’t have any other special requirements.
However, you do need adequate memory to take advantage of the new in-memory technologies. Microsoft recommends that the memory-optimized data not exceed 80 percent of the available server memory.
Q: How does the In-Memory OTLP Engine improve performance?
A: The short answer is that in-memory data access is an order of magnitude faster than disk-backed data access. As its name implies, the In-Memory OLTP Engine lets you move selected tables into memory. The memory-optimized tables are fully durable and transactional. The In-Memory OTLP Engine is designed for high concurrency, and a new optimistic concurrency control mechanism handles locking issues.
Q: What about indexes and stored procedures?
A: Tables used by the In-Memory OLTP Engine table can have several indexes. There are two index types: hash indexes and range indexes.
In addition, the In-Memory OLTP Engine enables stored procedures to be compiled into native code, resulting in significant reduction in the engine’s code path. In case you were wondering, natively compiled stored procedures can access only memory-optimized tables.
Q: Do I need to make any code changes to applications?
A: For the most part there are no significant code or application changes that you need to make. However, there are some steps that you may need to do to perform to move to the In-Memory OTLP Engine.
To start you should run the Analysis Migrate and Report Tool which will report any migration problem. For instance, there are several unsupported T-SQL features. For more information you should check out Migrating to In-Memory OLTP.
Q: Where can I find more information about the In-Memory OLTP Engine?
A: You can find more detailed information about how the In-Memory OLTP Engine is designed and how it works in the Microsoft Research white paper “Hekaton: SQL Server’s Memory-Optimized OLTP Engine.” You might also check out SQL Server In-Memory OLTP Internals Overview for CTP1.
Q: Can I download the SQL Server 2014 release?
A: You can download the SQL Server 2014 CTP1 from the TechNet Evaluation Center. This CTP doesn’t support upgrading from an earlier release of SQL Server, and it’s not meant to run in a side-by-side installation.
You need to do your testing on a clean system or virtual machine (VM). Remember that although the new In-Memory OLTP Engine might offer some compelling performance benefits, CTPs are meant for evaluation only, and you shouldn’t use them for production workloads.