SQL Server 2014 CTP2 – Memory Optimization Advisor

Here I am going to discuss one of the amazing new features, Memory Optimization Advisor of SQL Server 2014 CTP2. I discussed architectural details of SQL Server 2014 In-Memory Optimizer in my last blog post here.

We will discuss Memory Optimization Advisor implementation with a demo in this blog post. This tool helps customers in migrating disk-based tables to memory optimized tables with ease.

Memory Optimization Advisor – In Action

I will use AdventureWorks2008 database .For the demo purpose I have created a copy of table Employee named EmpTempTbl with columns – OrganizationNode, SalariedFlag, CurrentFlag removed.

Let us explore the wizard step by step.

Step # 1: Open Management Studio, connect to instance and the database AdventureWorks2008 and Right click the table EmpTempTbl . Now choose Memory Optimization Advisor.

Step # 2: The Advisor tool will be launched with a page describing feature of the tool. Click Next.

Step #3: On this screen the tool will check if the selected table is fit for migration as a Memory Optimized Table. The tool will report immediately if anything is wrong with the table which is preventing it from migrating as a Memory Optimized Table.

We have the option of generating Report using Generate Report button.

If you click on Generate button then wizard will provide with an option to save the report (HTML File) anywhere on the local disk. In my case, all is green so Click Next

Step #4: This screen shows migration warnings about the limitations of memory optimized object, and a link which will explain the limitations in detail. Click Next.

Step #5: Wizard will take us to below screen  which let us select the options for memory optimization.

• Memory-Optimized Filegroup :  you can have just one per instance and must create one before moving disk based table to memory optimize table else you will get error.
• Logical file name Here you can change the logical file name.
• Path: Points to the location where you will save your logical file.
• Estimated Current Memory Cost (MB): The estimated current memory cost for this table.

We have two checkboxes on this page and are described as below

• Option to copy Data from the disk table to the new memory optimized table during the migration process
• Option to be able to change the durability of the table just Schema (schema_only), in this case data will be lost after each SQL Server service restart. However by default Schema and Data(schema_and_data) is applied.

Step #6: Next screen in the wizard allow you to decide the name of the primary key, its members, its type. You can choose between nonclustered Hash index and nonclustered index here.

I have selected one integer column and one character column for Primary Key.

I selected Char Data Type to show here that we don’t have any other option then BIN2 collation for memory optimized tables. Click Next.

Step #7: This screen of the wizard will list out the summary Migration Actions. You have the option to script those operations by clicking the Script button .I scripted it out. Click Migrate.

Step #8: The migration process can take longer as it depends on number of objects. In this case, it succeeded. Click OK

Now our table is In-Memory. Let’s check the properties of the table to verify it. On SSMS you would be able to see the old table which is now renamed as EmpTempTbl_old and the new table is created under the Tables folder of the database.

Right Click on the newly created table and Go to Properties. You can see that the option Memory Optimized is set to true and the Durability is set to SchemaAndData.

This is a very user friendly tool with explanations and warnings, which will help users to streamline issues well before implementing In-Memory technology. As this blog is written with CTP version of SQL Server 2014 thus things might change during future release of SQL Server 2014.