Help: SQL Server

While trying something new in real life, it would be great to take advice from someone who is expert in that area. On the same lines, SQL Server Management Studio can help us as “advisor” about migrating disk based tables to In-Memory Tables. Here is an earlier blog about various SSMS enhancements to support In-Memory objects handling.

Where is the Wizard?

As always, most of the wizards are in the right click on node of SQL Server Management Studio (SSMS). This option to launch this wizard would come once we right click on a disk based table, as shown below. Note that it won’t be visible if table is already in-memory table.

Step by Step

When we select the “Memory Optimization Advisor” below migration wizard will be launched:

Clicking Next would take us to “Memory Optimization Checklist” screen. This would perform checks for the schema of the table and warn about limitations of in-memory table. My table was a simple DiskBasedTable which we have used in earlier blogs, so all green. Note that my blog posts are on CTP2 and few limitation might be removed in RTM so below checks might change.

In case of any errors, we would not be able to proceed but advisor would give us hints about where to go. For demo purpose, I have created a table with “problems”

CREATE TABLE [Address](
    [AddressID] [int] IDENTITY(1,1)  NOT NULL,
    [AddressLine1] [nvarchar](60) NOT NULL,
    [AddressLine2] [nvarchar](60) NULL,
    [City] [nvarchar](30) NOT NULL,
    [StateProvinceID] [int] NOT NULL,
    [PostalCode] [nvarchar](15) NOT NULL,
    [SpatialLocation] [geography] NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED 
(
    [AddressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [Address] ADD  CONSTRAINT [DF_Address_rowguid]  DEFAULT (newid()) FOR [rowguid]
GO

ALTER TABLE [Address] ADD  CONSTRAINT [DF_Address_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

and if we run wizard for above table, we would get below screen with few red crosses. Try to compare the errors with the definition above.

Because of these errors we can see that “Next>” button is grayed out. We must resolve these problems before this table can be migrated to an In-Memory OLTP table. We can also use “Generate report” and get HTML file which would have hyperlink about each “show me how”.

Let’s get back to the original wizard. Pressing next would present “Memory Optimization Warning” screen

These Information messages allow us to understand the limitation of usage of In-Memory OLTP tables.  To know “More” about each, we can click on the hyperlink that says “More Information”. More Information would open this link. http://go.microsoft.com/fwlink?LinkID=306707 Let’s hit Next now.

Next screen is about “Review Optimization Options”. In my case, I already have a In-Memory Optimized file-group in the database so they are greyed out. If this a database with no in-memory optimized filegroup then we would get option to create them (Read here). The next option is a checkbox that says “Also copy table data to the new memory optimize table”.  The default value is checkbox unchecked, which means no data will be copied to the new In-Memory OLTP table. Last option is about durability setting of the table which we discussed in previous blog

Once we hit next, we need to provide details about Primary Key (which is mandatory). We also need to provide details about index type and bucket count, in case of hash index. You can read more about hash indexes (part 1 and part 2)

Once we provided the options in all previous screens, next screen is “Verify Migration Actions” which is summary of the actions selected in previous screens. 

if we click on Script button, below is the output in SSMS. If we read carefully, there are based on options selected in wizard. We selected “rename”, “Non Clustered Hash Primary Key” and “Schema and data durability”

USE [SQLSeverHelp_IMO]
GO

EXEC dbo.sp_rename @objname = N'[dbo].[DiskBasedTable]', @newname = N'DiskBasedTable_old', @objtype = N'OBJECT'
GO

USE [SQLSeverHelp_IMO]
GO

SET ANSI_NULLS ON
GO

CREATE TABLE [dbo].[DiskBasedTable]
(
    [iID] [int] NOT NULL,
    [vFName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [vLName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

CONSTRAINT [DiskBasedTable_primaryKey] PRIMARY KEY NONCLUSTERED HASH 
(
    [iID]
)WITH ( BUCKET_COUNT = 16384)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

GO

If we click on migrate, the script would be run automatically by the wizard.

Summary

As we can see that wizard can be very useful to migrate a disk based table to an In-Memory OLTP table. It can help us in identifying the things you have to do to a table in order to migrate it.  After resolving all the issues we can either generate script to migrate later or use wizard itself to run script.

Hope you have learned something new today.