In my last few articles I have been exploring the new In-Memory OLTP table option that came out with SQL Server 2014. In this article I will be exploring a new tool that also came out with SQL Server 2014 named “Memory Optimization Advisor”. This tool is meant to help people quickly analyze their tables to see how easy it is to migrate them to In-Memory OLTP tables.
What is the Memory Optimization Advisor?
The Memory Optimization Advisor is a wizard that walks you through reviewing and migrating disk based tables to In-Memory OLTP tables. The advisor walks you through a number of steps starting with a review of a candidate table you want to migrate. The wizard reports any violation errors you might have with migrating your candidate table. By using the advisor you can quickly identify all the things that you will need to change in your table in order to migrate from a disk based table to an In-Memory OLTP table. Once you have resolved all the issues with a table the advisor will actually generate the code to migrate the table, and/or actually migrate the table for you.
To better understand the Memory Optimization Advisor let me do a walk-through using the tool to migrate a table in the AdventureWorks2012 database.
Walk-through of the Memory Optimization Advisor
To use the Memory Optimization Advisor to migrate a table to be an In-Memory OLTP table, I first right click on a table I want to migrate. In my case I will use the “HumanResources.EmployeePayHistory” table in the AdventureWorks2012 database for my demo. To bring up the Memory Optimization Advisor I first click on the HumanResources.EmployeePayHistory table. Doing this brings up the following context menu:
Context Menu
When I select the “Memory Optimization Advisor” item the migration wizard will be launched, which will display the following screen:
Table Memory Optimization Advisor
On this screen I click on the “Next>” button to take me to the “Memory Optimization Checklist” page that is shown below:
Memory Optimization Checklist
As you can see there are a number of validation errors related to migrating the HumanResources.EmployeePayHistory table. This table contains the following problems:
- one foreign key reference
- two check constraints
- one default constraint
Because of these errors you can see the “Next>” button is grayed out. I will need to resolve these three problems before this table can be migrated to an In-Memory OLTP table.
On this migration screen I can also click on the “Generate Report” button to generate a report that I can save. When I click on this button the advisor tool will prompt me for a place to save the report. The report will be saved in HTML format. Here is what my generated report looks like:
Memory Optimization Checklist Report
Since the HumanResources.EmployeePayHistory table has a number of issues I can’t proceed with migration of this table until I resolve all the issues. Since I am working in the AdventureWorks2012 database, I don’t want to corrupt the existing HumanResources.EmployeePayHistory table in this database. Instead I will create a new table that looks just like the HumanResources.EmployeePayHistory table, but doesn’t have the foreign key, check and default constraint issues. Here is the script I used to create my new table:
CREATE TABLE [HumanResources].[EmployeePayHistoryTemp](
[BusinessEntityID] [int] NOT NULL,
[RateChangeDate] [datetime] NOT NULL,
[Rate] [money] NOT NULL,
[PayFrequency] [tinyint] NOT NULL,
[ModifiedDate] [datetime] NOT NULL PRIMARY KEY CLUSTERED
(
[BusinessEntityID] ASC,
[RateChangeDate] ASC
)
) ON [PRIMARY]
After I create this table I used the Import/Export wizard to populate this table with the data from the HumanResources.EmployeePayHistory table. Once I have created and populated my HumanResources.EmployeePayHistoryTemp table I will run the Memory Optimized Advisor against my new table. When I do that here are my new results:
Memory Optimization Checklist
As you can see my new HumanResources.EmployeePayHistoryTemp table has no migration problems. Since I now have resolved all the migration issue the “Next>” button is now available. When I click on the “Next>” button the following window is displayed:
Memory Optimization Warnings
On this screen migration warnings are shown. These warnings allow you to understand the kinds of things you cannot do with In-Memory OLTP tables. If you wanted to find out more about each of these warnings you can click on the hyperlink that says “More Information” next to the warning. Once I understand information about all these warnings, I will proceed with the migration of my table by clicking on the “Next>” button. When I click on that button the following screen is displayed:
Review Optimization Options
On this screen the wizard sets default options that will be used when migrating my table to an In-Memory OLTP table. If I don’t like the defaults I can change them.
The first bit of information on this screen identifies the information about the Memory-0ptimized filegroup that has been set up on the AdventureWorks2012 databases. If the AdventureWorks2012 database had not already been set up to support In-Memory OLTP tables the wizard would have made these first three options available to me and would have provided default values for: Memory-optimize filegroup, Logical file name, and File path. Since my AdventureWorks2012 database is already set up to support In-Memory OLTP tables these first three fields identify the file group, logical name, and file path that is set up on my AdventureWorks2012 database.
The wizard preserves the original table when it migrates a table to an In-Memory OLTP table. This next option allows me to pick a name that I would like to use for preserving my old table. The wizard identified the default the name of “EmployeePaymentHistoryTemp_old” for retaining the old table information. If I didn’t like this name I could change it. For this demo I will leave the default name.
The next bit of information shown on this screen is how much memory space will be taken for my table. In my case the estimated memory cost is .02 MB. It is good to know how much memory you will take up prior to migrating a table. Remember these tables are now taking up memory space that is outside the buffer pool. If you migrate really large tables it is possible to exhaust the amount of available memory you have on your machine.
The next option is a checkbox that says “Also copy table data to the new memory optimize table”. The default value leaves this checkbox unchecked, which means no data will be copied to the new In-Memory OLTP table that is created. In my case I want to copy the data, so I will check this box.
The next check box is labeled “Check this box to migrate this table to a memory-optimized table with no data durability”. This option identifies the how the data will be handled should the system crash or be restored. Durability identifies whether or not the In-Memory OLTP table will retain data should the instance stop, or crash. Not checking this box, means the data will be retained in the event that SQL Server is stopped or crashes. If I wanted the data to not be durable between recycles of SQL Server then I could just have left this box uncheck, which would mean I would lose the data in my In-Memory OLTP table should the instance stop or crash.
To move on to the next step of the migration wizard I just click on the “Next>” button. Upon doing this the following screen is displayed:
Review Primary Key Conversion
On the Review Primary Key Conversion window I need to decide what type of primary key index I need. In-Memory OLTP tables support two different types of indexes: HASH and RANGE. To read more about these different kinds of indexes and which one might be best for your migration you should read this article: Overview of the Different In-Memory OLTP Index Types.
On the screen above the wizard defaults my existing primary key to a HASH index. Since my table currently has 316 unique values for the primary key columns the wizard rounded up to the next power of 2 for the bucket count, which is 512. If I expect a larger number of unique values beyond 512 to be inserted into this table then I might consider changing the default bucket count. By for my demo I will use that number.
When I click on the “Next>” button, I move on to the next step of the migration wizard, which displays the following screen:
Verify Migration Actions
On this screen I can verify the Table Memory Optimization Advisor migration actions that will be taken, to verify they are the ones I really want. The options displayed echo the options I have picked as I went through the wizard. If I am unhappy with any one of the options picked I could use the “<Previous” button to go back and change any of my previously picked options. Additionally if I didn’t what the migration wizard to perform the migration I could generate a script that would do the migration by clicking on the “Script” button. When I click on the “Script” button the wizard places the following code in a new query window:
USE [AdventureWorks2012]
GO
EXEC dbo.sp_rename @objname = N'[HumanResources].[EmployeePayHistoryTemp]’, @newname = N’EmployeePayHistoryTemp_old’, @objtype = N’OBJECT’
GO
USE [AdventureWorks2012]
GO
SET ANSI_NULLS ON
GO
CREATE TABLE [HumanResources].[EmployeePayHistoryTemp]
(
[BusinessEntityID] [int] NOT NULL,
[RateChangeDate] [datetime] NOT NULL,
[Rate] [money] NOT NULL,
[PayFrequency] [tinyint] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
CONSTRAINT [EmployeePayHistoryTemp_primaryKey] PRIMARY KEY NONCLUSTERED HASH
(
[BusinessEntityID],
[RateChangeDate]
)WITH ( BUCKET_COUNT = 512)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO
INSERT INTO [AdventureWorks2012].[HumanResources].[EmployeePayHistoryTemp] SELECT * FROM [AdventureWorks2012].[HumanResources].[EmployeePayHistoryTemp_old]
GO
Since I want to migrate my table using the wizard I will click on the “Migrate” button. When I do, the wizard will show a progress screen. The progress screen will show the progress SQL Server is making as it migrates my table to be an In-Memory OLTP table. When my migration completes the progress screen looks like this:
Migrate to Memory-Optimized Table Progress
At this point my table has been migrated. If I wanted to generate a report of my migration I can click on the Generate Report button. Or I can exit the migration wizard by clicking on the OK button.
Using the Wizard to Migrate Disk Based Tables to Memory Base Tables
As you can see it is very easy to migrate a disk based table to an In-Memory OLTP table. The migration wizard helps identify the things you have to do to a table in order to migrate it. Once you have resolved all the issues the wizard will allow you to create a script to do the migration, or actually perform the migration directly from the wizard. As you start exploring SQL Server 2014 and the In-Memory OLTP tables you might consider using the Memory Optimization Advisor to actually migrate one or more of your tables to In-Memory OLTP tables.
