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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
USE [StackOverflow2010] go SELECT TOP 10 Schema_name([table].schema_id) + '.' + [table].NAME AS [table name], Cast(Sum([allocationunit].used_pages * 8) / 1024.00 AS NUMERIC(36, 2)) AS [used mb], Cast(Sum([allocationunit].total_pages * 8) / 1024.00 AS NUMERIC(36, 2)) AS [allocated mb] FROM sys.tables [table] JOIN sys.indexes [index] ON [table].object_id = [index].object_id JOIN sys.partitions [partitions] ON [index].object_id = [partitions].object_id AND [index].index_id = [partitions].index_id JOIN sys.allocation_units [allocationunit] ON [partitions].partition_id = [allocationunit].container_id GROUP BY Schema_name([table].schema_id) + '.' + [table].NAME ORDER BY Sum([allocationunit].used_pages) DESC; |
Output:
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.
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.
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.
On the Memory Optimization Warning screen, you can view the list of tasks that cannot be performed after migrating the table.
Using the Review Optimization Options screen, you can configure the following options:
- 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
- Logical file name: Specify the logical file name of the memory-optimized file
- 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
- 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
- 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.
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.
- 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
- 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 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.
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.
The migration process begins.
The migration process was completed successfully. Click Ok to close the wizard.
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.
You can view the details of the memory-optimized filegroup by executing the following query.
|
1 2 3 4 5 6 7 8 9 10 11 |
USE [StackOverflow2010] go SELECT Db_name(mf.database_id) AS [Database Name], fg.NAME AS [Logical File Name], fg.type_desc, mf.physical_name, mf.size FROM sys.filegroups fg INNER JOIN sys.master_files mf ON fg.data_space_id = mf.data_space_id AND Db_name(mf.database_id) = 'StackOverflow2010' |
Output:
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE [StackOverflow2010] go SELECT Object_name(object_id) [Table Name], NAME [Index Name], type_desc [Index Type], CASE WHEN is_primary_key = 1 THEN 'Yes' ELSE '-' END AS [Primary Key] FROM sys.indexes WHERE Object_name(object_id) = 'Users' AND index_id <> 0 |
Output:
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.
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
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022