Toying with In-Memory OLTP

26 Mar

In six days the bits for SQL 2014 RTM will be available for download. I decided to fling myself into its hot new feature of In-Memory OLTP with the CTP2 release. I’ve attended one user group that gave an overview of the feature set ( Thanks @brian78 ) but other than that I have not read much technical information about In-Memory OLTP.

One advantage point that seems to pop up in literature surrounding the release is the ease of implementation. Not all tables in a database have to be In-Memory and a single query can seamlessly access both classic disk based tables and In-Memory tables. Since the product isn’t released yet, the information available on the featureset is heavily weighted towards sales. I wanted to see if achieving the 5x-20x performance boost was really as easy as it sounds. Instead of my usual approach of collecting lots of information and reading tutorials, I decided to blaze my own trail.

The first thing to do is create a new database. I noticed a setting that I heard referenced in the overview called delayed durability.


Scripting the new database out in T-SQL also shows this new setting. I’m assuming this will make things faster since they don’t have to be persisted to disk right away.


Before I run that script I decide to poke around a bit more. I see some In-Memory settings over on filestream. I’m not sure if that is a necessary requirement or not, but I am going to add a filegroup and file just in case.



Now that the database is created I want to create a table. There is a special option in the Script-to menu for In-Memory optimized tables. I’ll create a few dummy columns and try to run it.


There seems to be a problem with my varchar column. “Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables.” Well that is unfortunate, I suppose I will change the collation in this test but that won’t be easy in real life.


After changing the collation I am able to create my memory optimized table.


I wondered if there would be any way to tell in my query plan if I’m actually optimized. It doesn’t appear so…


Was that a 5x performance boost?? I’m I doing it right?? Not sure, but for now I need to take a break.

I’m hoping ISVs start supporting this feature but it might be a lot more work than advertised. After getting that error I found a list of many things that are not supported on these tables and in the compiled stored procedures.

This list does not encourage me to blaze new trails and start testing this as soon as it comes out. I prefer to wait a bit and let the other trail blazers blog about the issues they have.

Leave a comment

Posted by on March 26, 2014 in SQL Admin


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: