Problem
From the Analysis, Migrate and Report (AMR) tool (Comparison of AMR Data Collection and Reports in SQL 2016 and SQL 2014) I can see there are recommendations for some tables to be memory optimized in SQL Server. I also used the In-Memory OLTP migration checklists (Using In-Memory OLTP migration checklists in SQL Server 2016) to evaluate any incompatibilities in memory optimization. With the data I have collected, I am looking forward to migrating tables to memory optimized tables. Is there any tool that could be used to migrate the compatible tables?
Solution
Beginning with SQL Server 2014, we could make use of the “Memory Optimization Advisor” to migrate the tables to memory optimized tables.
In order to test this feature, just create a table in a database. Use script below.
CREATE TABLE [dbo].[Example]( [Id] [int] NOT NULL, CONSTRAINT [PK_Example] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
In SQL Server Management Studio (SSMS), right click on the table and select “Memory Optimization Advisor”.
Once you select this option, you will see the window shown below.
This tool can be used not only for evaluating incompatibilities for memory optimization, but also for migrating data to memory-optimized tables. From the previous tools – Analysis, Migrate and Report (AMR) tool (Comparison of AMR Data Collection and Reports in SQL 2016 and SQL 2014) and In-Memory OLTP migration checklists (Using In-Memory OLTP migration checklists in SQL Server 2016) discussed in earlier tips, you could only generate reports on the tables that could be considered for memory optimization. But with this Memory Optimization Advisor we can go the extra mile to use this tool to migrate the data to memory-optimized tables. Click on the “Next” button and you will see the “Memory Optimization Checklist” window as shown below.
As we have a simple table, there are no incompatibility issues. Click on the “Generate Report” option to view the report in HTML format. Select a folder to save the report. A sample report is shown below.
Click “Next”, which will take you to the “Memory Optimization Warnings” screen as shown below.
For a detailed report of the validation results, click on the “Generate Report” option to view the report in HTML format. Save the report to a specific location to view it. The warnings do not necessarily prevent migration to In-Memory OLTP, but it is recommended to take a detailed look at this report. The report for our table is shown below.
Click “Next”, and you will see the “Review Optimization Options” window as shown below.
One of the requirements for memory-optimization is a need for a memory-optimized filegroup. You can see from the “Review Optimization Options” window, that memory-optimized filegroup with a logical file name and file path needs to be created. If one already exists, the options to create the memory-optimized filegroup would be greyed out and this cannot be changed. You would also need to rename the original table as at the end of this process a memory-optimized table with the original table name would be created. As we used a sample table with no data in it, the “Estimated current memory cost (MB)” is showing as 0. We could also select to copy the data over to the new memory optimized table and if required enable the option for memory-optimization with no data durability. Regarding memory-optimization with no data durability, you need to be cautious as all data would be lost with a server restart.
Click “Next”, and you will see the “Review Primary Key Conversion” as shown below.
Based on the primary key meta data available, the details would get populated. You need to be cautious on the type of index selected as it would have a huge impact on performance. For point lookups, a nonclustered Hash index provides better performance. However, for queries that use inequality predicates and those that use ORDER BY clauses, using a memory-optimized nonclustered index would provide greater performance. In our case, a nonclustered index is selected with ASC sort order. Also note, the memory-optimized nonclustered indexes are unidirectional. If you check our CREATE TABLE script above, the order specified in the script is ASC. As memory-optimized nonclustered index is unidirectional, make sure you select the sort order as appropriate based on the order specified in the index.
Click “Next”, and you will see the “Summary” section as shown where you can verify the migration actions.
Just click on the “Script” option to generate the T-SQL script which can be used to try this out on other tables that can be memory-optimized. Click on the “Migrate” option to start the process of memory-optimization. In the final screen, you will see this window. For a successful migration, all the steps should have passed as shown. Click on the “Generate Report” option to view the report in HTML format.
With this, the migration process is complete and can be accomplished by using this simple tool.
Next Steps
- Learn about the Analysis, Migrate and Report (AMR) tool from this tip: Comparison of AMR Data Collection and Reports in SQL 2016 and SQL 2014 to find out which tables in SQL Server 2016 would benefit from migrating to In-Memory OLTP
- If you are using SQL Server 2014, refer this tip: Getting started with the AMR tool for migration to SQL Server In-memory OLTP Tables
- For checking tables that have incompatibilities with In-Memory OLTP feature, refer this tip: Using In-Memory OLTP migration checklists in SQL Server 2016
