Memory Optimization Advisor
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Transaction performance reports tool (see Determining if a Table or Stored Procedure Should Be Ported to In-Memory OLTP) informs you about which tables in your database will benefit if ported to use In-Memory OLTP. After you identify a table that you would like to port to use In-Memory OLTP, you can use the memory optimization advisor to help you migrate the disk-based database table to In-Memory OLTP.
To begin, connect to the instance that contains the disk-based database table. You can connect to SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, or SQL Server 2014 instance. However, if you wish to perform a migration operation with the advisor, you must connect to a SQL Server 2014 instance on which In-Memory OLTP functionality is enabled. For more information about In-Memory OLTP requirements, see Requirements for Using Memory-Optimized Tables.
For information about migration methodologies, see In-Memory OLTP – Common Workload Patterns and Migration Considerations.
In Object Explorer, right click the table you want to convert, and select Memory-Optimization Advisor. This will display the welcome page for the Table Memory Optimization Advisor.
Memory Optimization Checklist
When you click Next in the welcome page for the Table Memory Optimization Advisor, you will see the memory optimization checklist. Memory-optimized tables do not support all the features in a disk-based table. The memory optimization checklist reports if the disk-based table uses any features that are incompatible with a memory-optimized table. The Table Memory Optimization Advisor does not modify the disk-based table so that it can be migrated to use In-Memory OLTP. You must make those changes before continuing migration. For each incompatibility found, the Table Memory Optimization Advisor displays a link to information that can help you modify your disk-based tables.
If you wish to keep a list of these incompatibilities, to plan your migration, click the Generate Report to generate a HTML list.
If your table has no incompatibilities and you are connected to a SQL Server 2014 instance with In-Memory OLTP, click Next.
Memory Optimization Warnings
The next page, memory optimization warnings, contains a list of issues that do not prevent the table from being migrated to use In-Memory OLTP, but that may cause the behavior of other objects (such as stored procedures or CLR functions) to fail or result in unexpected behavior.
The first several warnings in the list are informational and may or may not apply to your table. Links in the right-hand column of the table will take you to more information.
The warning table will also display potential warning conditions that are not present in your table.
Actionable warnings will have a yellow triangle in the left-hand column. If there are actionable warnings, you should exit the migration, resolve the warnings, and then restart the process. If you do not resolve the warnings, your migrated table may cause a failure.
Click Generate Report to generate an HTML report of these warnings. Click Next to proceed.
Review Optimization Options
The next screen lets you modify options for the migration to In-Memory OLTP:
Click Next to continue.
Review Primary Key Conversion
The next screen is Review Primary Key Conversion. The Memory-Optimization Advisor will detect if there are one or more primary keys in the table, and populates the list of columns based on the primary key metadata. Otherwise, if you wish to migrate to a durable memory-optimized table, you must create a primary key.
If a primary key doesn’t exist and the table is being migrated to a non-durable table, this screen will not appear.
For textual columns (columns with types char, nchar, varchar, and nvarchar) you must select an appropriate collation. In-Memory OLTP only supports BIN2 collations for columns on a memory-optimized table and it does not support collations with supplementary characters. See Collations and Code Pages for information on the collations supported and the potential impact of a change in collation.
You can configure the following parameters for the primary key:
To understand the type of index best for your primary key, see Hash Indexes.
Click Next after you make your primary key choices.
Review Index Conversion
The next page is Review Index Conversion. The Memory-Optimization Advisor will detect if there are one or more indexes in the table, and populates the list of columns and data type. The parameters you can configure in the Review Index Conversion page are similar to the previous, Review Primary Key Conversion page.
If the table only has a primary key and it’s being migrated to a durable table, this screen will not appear.
After you make a decision for every index in your table, click Next.
Verify Migration Actions
The next page is Verify Migration Actions. To script the migration operation, click Script to generate a Transact-SQL script. You may then modify and execute the script. Click Migrate to begin the table migration.
After the process is finished, refresh Object Explorer to see the new memory-optimized table and the old disk-based table. You can keep the old table or delete it at your convenience.