SQLServer 2014 CTP2 came with an inbuilt tool called Memory Optimization Advisor which will help you in migrating your normal tables to memory optimized tables.
Where can I find this tool ?
All you need to do is right click the table you want to migrate, and choose the option Memory Optimization Advisor.
The tool will launch with a detailed description of what its capable of
I decided to play around with this tool, and here are my observations –
Note – This is still CTP, so things can change during RTM/GA phase.
I started of with a normal table named Employee which has 3 Col and 3 rows data on it. Nothing big, pretty simple.
Launched the advisor, and the initial checks were all green. [Be sure to carefully analyze the checks, they are all interesting.]
The wizard also has the ability to export a report (Who doesn’t like a report these days !)
I clicked next to proceed, and the wizard gave me some information about the limitations of memory optimized object, and a link which will explain the limitations in detail.
I liked these warnings, because its telling me well in advance about the limitations so that I will be more careful on what I’m up to.(Everything has a cost associated with it !)
Next up is some interesting stuff. The wizard is forcing me to select the options for memory optimized objects. I have the option to mention memory optimized file group,name and the file path.
I also have the option to re-name the original table, copy data from the original table to the memory optimized table and a check box to mention if the table needs to be moved with no data durability.(Default being both schema/data durability).
I decided to go with all defaults as this was a test case.
One of the other cool option which the above wizard window gave is this value –
I presume that this value will be the cost of size in memory which will be needed when the table is moved as a memory optimized object. I might not be right at this point, but I will update this post in case this is not true.
Next screen in the wizard talks about primary key and index creation. I decided to make column ID as the primary key with a NON-CLUSTERED HASH Index and a bucket count of 1024.
The final screen provided me a summary of my selections.
Yet another cool feature in the wizard is that it allowed me to script everything before I finalize my selections.
I decided to hit Migrate, and wanted to see how it goes.
Viola,all clean and green !
The wizard was smart enough to rename the old table,and created a memory optimized one for me.
I had scripted out everything before hitting migrate and that file looked like this –
This is a very neat feel good to have tool and those warnings and pre-checks will definitely help users to streamline issues well before they are IN MEMORY !
Thanks for reading and keep watching this space for more !