New in SQL Server 2014 is Microsoft
much touted In Memory OLTP engine, previously codenamed 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
