1 SQL2014: In Memory OLTP (6): Memory optimization advisor and report

In order to help developer & DBA migrate to memory optimized table, SQL Server come with

• Table Memory Optimization advisor
• Native Compilation Advisor

Table Memory Optimization advisor

Click the data and click Memory Optimization advisor

It will display the error and we need to fix it offline and rerun the advisor again.

Once we fix all the error, we can continue to proceed

The warming pages display the limitation once we convert . 

We can also generate the script .

USE [tpch]

GO

EXEC dbo.sp_rename @objname = N'[dbo].[region]', @newname = N'region_old', @objtype = N'OBJECT'

GO

USE [tpch]

GO

SET ANSI_NULLS ON

GO

CREATE TABLE [dbo].[region]

(

    [r_regionkey] [int] NOT NULL,

    [r_name] [char](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [r_comment] [varchar](152) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [region_primaryKey] PRIMARY KEY NONCLUSTERED HASH 

(

    [r_regionkey]

)WITH ( BUCKET_COUNT = 8)

)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

GO

INSERT INTO [tpch].[dbo].[region] ([r_regionkey], [r_name], [r_comment]) SELECT [r_regionkey], [r_name], [r_comment] FROM [tpch].[dbo].[region_old] 

GO

Complete successfully.

Native Compilation Advisor

Another wizard is to migrate the store procedure with native complied store procedure.

Click store procedure and run Native compilation advisor. 

This advisor just provide any violation on the T-SQL . It wont actually help convert it. We have to get the context for the SP and modify it ourselves base on the advisor result .