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. http://msdn.microsoft.com/en-us/library/dn246937(v=sql.120).aspx
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.