SQL Server 2014 CTP2: In-Memory Advisor for Tables and Stored Procedures

One of the best new functionalities of Microsoft SQL Server 2014 is the In-Memory part, which gives you the possibility to load tables and also Stored Procedures in-memory for a very fast response time. The Community Technology Preview 2 from Microsoft SQL Server 2014 went out some days ago with two new Advisors. These Advisors will help you manage the transformation of your disk tables to Memory Optimized Tables as well as your Stored Procedures to Natively Compiled Stored Procedures.

Let see how this work!

Memory Optimization Advisor

Go to Management Studio and select a table you would like to migrate. Right click this table and choose Memory Optimization Advisor.

The Advisor tool will be launched with an explain page that you can skip.

Click Next to begin the script generation process. It will check if your table is able to be migrated as a Memory Optimized Table.

Here, all is green, it means that your table has no blocking features which could avoid a migration process. You have the possibility to Generate a report for this analyze, for the time being it is just an HTML file.

Click Next.

On this page a warning appears. In fact for memory optimized table, only BIN2 collation are available for indexes. My instance has French_CI_AS, I will need to change my index collation later in the migration process.

Click Next to continue.

On this page we have important informations:

• Memory-Optimized Filegroup, you can have just one per instance
• Logical file name that you can enter
• Path where you will save your logical file can be changed
• The estimated current memory cost for this table(if you move the data from the disk table to the new table)
• You have the possibility to select, if you want, to copy Data from the disk table to the new memory optimized table during the migration process
• You are also able to change the durability of the table: by default Schema and Data(schema_and_data) but you could also choose just Schema(schema_only), in this case data will be lost after each SQL Server service restart.

Click Next.

The primary key has to be converted, you can choose between:

• new nonclustered Hash index, which gives best performance for point lookups, needs also bucket_count which should be twice the expected number of rows
• nonclustered index, which gives best performance for range predicates

Click Next.

You have the same choice as for the primary key.

Click Next.

For an index with Char Data Type, we have to select a BIN2 collation because other collations are not accepted with memory optimized tables.

Click Next.

Here you have a resume of all operations that will be performed to migrate your disk table to a new memory optimized table. You have the option to script those operations by clicking the Script button.

Click Migrate to start the migration process.

The migration process succeeded. Now our table is In-Memory!

We can check the new table’s properties.

The table is memory-optimized and the durability is schema and data. At this step, we have migrates our disk table to a memory-optimized table.

Why not also move the Stored Procedure which fills this table to a new natively compiled Stored Procedure. Let’s also try this!

Native Compilation Advisor

In Management Studio, navigate through your database’s objects and select the Stored Procedure you would like to migrate to Natively Compiled Stored Procedure. Right click it and select Native Compilation Advisor.

A welcome screen appears.

This screen explains that this advisor will help us to discover and evaluate Transact-SQL elements in the Stored Procedure that are not supported in Native Compilation…

Click Next.

Here, the Stored Procedure is valid and can become a native one without modification.

My first idea was to click Next to let the advisor migrate my Stored Procedure as for a table, but the Next button is disabled…

In fact, this Advisor just helps you to discover problems that could make your migration impossible, but doesn’t do anything else… You have to migrate your Stored Procedure on your own.

On the other hand, when a part of your Transact-SQL is not cimpliant for Natively Compiled Stored Procedures, this tool advises you:

And when you click the Next button:

Now, you have a list of elements which are not supported for Natively Compiled Stored Procedures.

You also have the possibility to create a report for these unsupported elements.

Conclusion

As we have seen, the Advisor for tables is more interesting than the Advisor for native compilation stored procedures. In both case, it is definitively a good help to improve codes prior to a migration.

To evaluate if In-Memory OLTP will improve you database application’s performance you can use the AMR (Analysis, Migrate and Report) tool. This tool will be covered in a future blog.