Nisarg Upadhyay
Memory optimization checklist

Migrating disk-based tables to memory-optimized tables of a SQL database

January 6, 2022 by

In this series of articles, we will learn how to convert the disk-based table to an in-memory table of SQL Database.

This article is the first article of the series Migrate disk-based tables to memory-optimized tables. It explains how we can migrate the disk-based table to a memory-optimized table by using Memory Optimization Advisor.

Environment Setup

I have installed SQL Server 2019 developer edition on my workstation and restored the Stackoverflow2010 database. The details of the tables and their sizes of SQL Database can be populated by running the query shown below:

Output:

Tables of SQL database with size

The table does not have any foreign keys and constraints in the dbo.Users table. In the next article of this series, I will explain how we can migrate the table that has foreign keys and check constraints.

Migrate tables using Memory Optimization Advisor

We will convert Sales. Users table to in-memory table. To run it, Right-click on dbo.Users 🡪 Select Memory Optimization Advisor.

Memory optimizer advisor for SQL Database

The memory optimization wizard of SQL Database begins. On the welcome screen, you can view the details of the wizard. Skip the welcome screen, click on Next.

Table Memory Optimization Advisor

On the Memory Optimization Checklist screen, the wizard runs a prerequisite check. If any of the prerequisites fails, the wizard will stop the migration process. In our demo, the table meets all requirements.

Memory optimization checklist

On the Memory Optimization Warning screen, you can view the list of tasks that cannot be performed after migrating the table.

Memory Optimization Warning

Using the Review Optimization Options screen, you can configure the following options:

  1. Memory-optimized filegroup: Specify the name of the memory-optimized filegroup. If you have already created a memory-optimized filegroup, you can specify the name of it. If you have not created the filegroup, the wizard will create it for you. In this demo, I have created a memory-optimized filegroup named FG_Users
  2. Logical file name: Specify the logical file name of the memory-optimized file
  3. File path: Specify the path of a memory-optimized file. If you have created the directory to keep a memory-optimized file, you can enter the path, or a wizard will create it for you. I have created a directory named FG_Users on my workstation, so I have provided the directory’s path
  4. Rename the original table as This option is used as a fallback plan. Before migrating the table, it creates a copy of a table with a different name. Suppose you migrate the existing table to a memory-optimized table and your application encounters an error. In that case, you can rename the new table with a different name and rename the original disk-based table. In our demo, I have created a backup table named Users_old
  5. Estimated current memory cost: This option gives an estimated space occupied by the memory-optimized table. This option gives you an idea of how much memory will be utilized after migrating the table. The value specified in this option is an estimation and calculated by the wizard. The real value can be high

After migrating the table, if you want to copy the data to a new memory-optimized table, you can enable Also copy table data to the new memory-optimized table option. I have enabled it. If you do not want to copy the data after creating the filegroup, you can use the SQL Server import-export wizard to copy the data. In our demo, we are copying data after migration, so I have enabled the option.

Review Optimization Options

On the primary key conversion screen, you can specify the new name of the primary key. You can create the following types of primary keys.

  1. Non-clustered Hash Index: The non-clustered hash indexes increase the performance when the query is doing a point lookup. It is not useful when the query is doing the range scan. In a Non-clustered hash index, we must specify the Bucket Count. The bucket count is the number of buckets created in a hash table
  2. Non-clustered index: The non-clustered index improves the performance of the queries doing the range scans and Order by clauses. To achieve the desired performance improvement, make sure that the sort order of the query and sort order of indexes are the same

In our demo, I am using Non-Clustered Hash indexes. Click Next.

Review Primary Key Conversion

Review the index migration screen; you can convert the existing indexes into a Non-clustered Hash Index or a Non-Clustered index. In our demo, the new index will be the NON-CLUSTERED HASH index. I have not changed the value of the bucket count. Click Next.

Review Index Conversion

On the summary screen, you can view the tasks and configuration that we have set to migrate a normal disk-based table to a memory-optimized table. You can generate the script of the migration process by clicking on the Script button. Click Migrate to begin the migration process.

Verify action to convert to memory optimized table of SQL database

The migration process begins.

Progress

The migration process was completed successfully. Click Ok to close the wizard.

Table has been converted in memory-optimized table of SQL Database

Verify the changes

Now, let us verify that the table has been migrated properly or not. We can view the details from the table properties. Right-click on dbo.Users 🡪 Properties. On the general page, you can see the value of the Memory-optimized option is True, and the value of the Durability option is SchemaAndData.

View Table Properties

You can view the details of the memory-optimized filegroup by executing the following query.

Output:

View Filegroup

As you can see, the table is created in the FG_Users filegroup. To view the indexes that have been created for dbo.Users table, run the following query.

Output:

View indexes of memory-optimized table

As you can see, a NON-CLUSTERED hash primary key and a NON-CLUSTERED hash index have been created on the table.

Summary

In this article, we learned how to convert the disk-based table to the Memory-Optimized table of the SQL Database. In the next article, we will learn how to migrate the disk-based table to a memory-optimized table with foreign keys. Stay tuned.

Nisarg Upadhyay
Database design, Importing, exporting

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

423 Views