Dear Friends,
I hope you enjoyed my last week’s blog post on In-Memory OLTP. Very recently, Microsoft released SQL Server 2014 CTP2 with some cool set of new features. SQL Server Memory Optimization Advisor tool is introduced to implement In-memory technology as part the release which I believe is one of the coolest features to have in place. Today I’ll explore this tool and find out how we can implement memory optimized tables using the same. For demo, I’ve created a database MemOptAdvisor and a standard table tblEmpoyee which will be converted to memory optimized one with the help of this tool.
You just need to right click on the table here in my case it is tblEmpoyee to launch this tool;
Once you click on Memory Optimization Advisor, tool will launch with detailed description on what it capable of doing;
Clicking next takes you to the next screen with an analysis on the selected object. In case of anything which prevents you in converting the object to a memory optimized table, tool is capable enough to report the same, make sure you analyze them very carefully. In my case it is green;
When you click next, tool shows up the migration warnings which you may want to FIX up which again very nice to have and I did FIX warning shown in my case by selecting different collation later part of this post;
Next step force you to select options for your memory optimized objects i.e. filegroup name, logical name, file path. You also have the option to rename original table, copy data to new memory optimized object and a checkbox which enables you to specify if the table needs to be moved as no data durability, by default it is both schema/data durability. It also displays estimated current memory cost in MB.
Next screen lets you decide on name for primary key, type of index, HASH bucket, change column level collation etc. (I fixed my warning here however you may decide based on your environment);
Summary screen is quite self-explanatory of what your selection is when you click next. You can generate script here if you want to;
You are done when you click Migrate button but this process may take a while to finish based on size of object and number of records;
Now, when you expand Tables node on respective database, see that you have two tables, one with suffix _old and new one that we created as part of the demo;
I’ve tried this using SQL Server 2014 CTP2 and final release of the product may introduce us with more advanced options. Overall, this tool is very user friendly with explanations, warnings on what it is capable of and definitely help users to FIX issues before implementing In-Memory technology.
Regards
Kanchan Bhattacharyya
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
