Monday, October 13, 2014

0 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

image_thumb[2][1]

image

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

image

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

image

The warming pages display the limitation once we convert . 

image

image

image

We can also generate the script .

image

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.

image

Native Compilation Advisor

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

Click store procedure and run Native compilation advisor. 

image

image

image

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 .

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates