New in SQL Server 2014 is Microsoft’s much touted In Memory OLTP engine, previously code-named Hekaton. Microsoft promises incredible OLTP performance improvements (10x performance on average and up to 30x performance on transactions) with little code change. On top of it all, Microsoft claims to have eliminated locking and latching entirely on in memory objects. These all sound so incredible not to mention simple. Can it be true? In this series of blog posts, I plan to walk through an overview of this new and exciting technology as well as some of the nuances and gotcha’s.
In this first blog post, I’m going to walk through some of the high level requirements for using SQL Server’s In Memory (Memory Optimized) engine, as well as a very short performance test.
First and foremost, SQL Server 2014 64 Bit Enterprise edition is required for production use. As the Developer and Evaluation editions are identical feature-wise to Enterprise edition, they may also be used for their appropriate roles. The Memory Optimized engine is the crowned jewel, the star of SQL Server 2014 so naturally it’s restricted to the highest edition. Secondly, and this should go without saying, your server should have enough memory to hold memory optimized objects. Third, you should plan on twice as much free disk space as the size of your durable in memory data. We will talk about durability in later blogs. Lastly, your server’s processor needs to support the instruction cmpxchg16b to use In-Memory OLTP.
Now that we have some initial requirements out of the way, I’ve set up a quick demo of some performance improvements Memory Optimized objects. I’ve set up a database that contains both a disk based table as well as a memory optimized table and set up a simple script to insert 1 million records into each while recording total time. Bare in mind, this is an incredibly simple example performed on a local SQL Server 2014 instance on my laptop. Lets first take a look at loading 1 million records into a disk based table.
As we can see in the example above, it took 15579 ms to load data into a disk based table. Not too bad. Lets see what kind of performance we get on a memory optimized table.
Whoa. We dropped that down to 6831 ms! That’s pretty impressive. We dropped processing time down to a little less than 1/2 the time. Can we call it a day? Unfortunately, not just yet. As with most things in life, nothing is quite this simple. Stay tuned, we’ll explore the ins and outs of SQL Server In Memory engine and see exactly what it takes to get the touted performance improvements and as well as more in depth explorations of the different aspects of this new technology.
Jeff Shurak is an Anexinet SQL Server Architect focused on Disaster Recovery and Cloud Migration with over 14 years of data technology experience in ETL development, three-dimensional cube development, and database administration.