将基于磁盘的表迁移到SQL数据库的内存优化的表上的方法

吴欣
188 阅读5分钟

在本系列文章中，我们将学习如何将基于磁盘的表转换为SQL数据库的内存表。

本文是系列文章《将基于磁盘的表迁移到内存优化的表》的第一篇。它解释了我们如何通过使用内存优化顾问将基于磁盘的表迁移到内存优化的表。

环境设置

我在工作站上安装了SQL Server 2019开发者版，并恢复了Stackoverflow2010数据库。通过运行如下所示的查询，可以弹出SQL数据库的表及其大小的详细信息。

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;

输出。

Tables of SQL database with size

该表在dbo.Users表中没有任何外键和约束。在本系列的下一篇文章中，我将解释我们如何迁移有外键和检查约束的表。

使用内存优化顾问迁移表

我们将把Sales.Users表转换为内存表。要运行它，右击dbo.Users🡪选择内存优化顾问。

Memory optimizer advisor for SQL Database

SQL数据库的内存优化向导开始了。在欢迎屏幕上，你可以查看该向导的细节。跳过欢迎画面，点击下一步。

Table Memory Optimization Advisor

在 "内存优化检查表 "屏幕上，该向导运行一个先决条件检查。如果任何一个先决条件失败，向导将停止迁移过程。在我们的演示中，该表满足所有的要求。

Memory optimization checklist

在 "内存优化警告 "界面，你可以查看迁移表后不能执行的任务列表。

Memory Optimization Warning

使用审查优化选项屏幕，你可以配置以下选项。

  1. 内存优化的文件组。指定内存优化文件组的名称。如果你已经创建了一个内存优化的文件组，你可以指定它的名字。如果你还没有创建文件组，向导将为你创建它。在这个演示中，我创建了一个内存优化的文件组，名为FG_Users。
  2. 逻辑文件名：指定内存优化文件的逻辑文件名。
  3. 文件路径。指定内存优化文件的路径。如果你已经创建了保存内存优化文件的目录，你可以输入该路径，或者由向导为你创建。我在工作站上创建了一个名为FG_Users的目录，所以我提供了该目录的路径
  4. 将原表重命名为这个选项是作为一个后备计划。在迁移表之前，它会创建一个名字不同的表的副本。假设你把现有的表迁移到一个内存优化的表，而你的应用程序遇到了一个错误。在这种情况下，你可以用不同的名字重命名新的表，并重命名原来基于磁盘的表。在我们的演示中，我已经创建了一个备份表，名为 Users_old
  5. 估计当前的内存成本。这个选项给出了一个内存优化表所占用的估计空间。这个选项可以让你了解迁移表后会占用多少内存。这个选项中指定的数值是一个估计值，由向导计算得出。实际值可能很高

在迁移表之后，如果你想把数据复制到一个新的内存优化的表中，你可以启用Also copy table data to the new memory-optimized table选项。我已经启用了它。如果你不想在创建文件组后复制数据，你可以使用SQL Server导入-导出向导来复制数据。在我们的演示中，我们是在迁移后复制数据，所以我启用了该选项。

Review Optimization Options

在主键转换界面，你可以指定主键的新名称。你可以创建以下类型的主键。

  1. 非聚类哈希索引。当查询进行点查询时，非聚类哈希索引会提高性能。当查询在做范围扫描时，它是没有用的。在非聚类散列索引中，我们必须指定Bucket Count。桶计数是在散列表中创建的桶的数量。
  2. 非聚类索引。非聚类索引提高了进行范围扫描和Order by条款的查询的性能。为了达到预期的性能改善，确保查询的排序顺序和索引的排序顺序是相同的。

在我们的演示中，我正在使用非聚类哈希索引。点击 "下一步"。

Review Primary Key Conversion

查看索引迁移屏幕；你可以将现有的索引转换成非集群哈希索引或非集群索引。在我们的演示中，新索引将是NON-CLUSTERED HASH索引。我没有改变桶数的值。点击 "下一步"。

Review Index Conversion

在摘要屏幕上，你可以看到我们为将一个普通的基于磁盘的表迁移到一个内存优化的表所设置的任务和配置。你可以通过点击脚本按钮来生成迁移过程的脚本。点击Migrate，开始迁移过程。

Verify action to convert to memory optimized table of SQL database

迁移过程开始。

Progress

迁移过程成功完成。点击OK，关闭向导。

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

验证变化

现在，让我们验证一下该表是否被正确迁移。我们可以从表的属性中查看细节。右击dbo.Users 🡪属性。在常规页面中，你可以看到Memory-optimized选项的值是True，Durability选项的值是SchemaAndData。

View Table Properties

你可以通过执行下面的查询来查看内存优化的文件组的详细信息。

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'

输出。

View Filegroup

正如你所看到的，该表是在FG_Users文件组中创建的。要查看已经为dbo.Users表创建的索引，运行下面的查询。

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

输出。

View indexes of memory-optimized table

正如你所看到的，在表上已经创建了一个NON-CLUSTERED哈希主键和一个NON-CLUSTERED哈希索引。

总结

在这篇文章中，我们学习了如何将基于磁盘的表转换成SQL数据库的内存优化表。在下一篇文章中，我们将学习如何将基于磁盘的表迁移到具有外键的内存优化表。请继续关注。

avatar
文章
阅读
粉丝