SQL Server Memory Optimization Advisor to Migrate to In-Memory OLTP


By:   |   Updated: 2016-04-27   |   Comments   |   Related: More > In-Memory OLTP

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".

Memory Optimization Advisor in SQL Server Management Studio

Once you select this option, you will see the window shown below.

Table Memory Optimization Advisor

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.

Memory Optimization Cheklist

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.

Memory Optimization Checklist in HTML

Click "Next", which will take you to the "Memory Optimization Warnings" screen as shown below.

Memory Optimization Migration Warnings

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.

Memory Optimization Warnings for a Table in HTML format

Click "Next", and you will see the "Review Optimization Options" window as shown below.

Review Optimization Options

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.

Review Primary Key Conversion

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.

Verify 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.

Migrate to Memory-Optimized Table Progress

With this, the migration process is complete and can be accomplished by using this simple tool.

Next Steps


Last Updated: 2016-04-27


get scripts

next tip button



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools