通过使用内存优化获得更快的临时表和表变量

2025-05-31

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例

如果使用临时表、表变量或表值参数，请考虑转换它们以使用内存优化表和表变量来提高性能。此代码的更改通常很小。

本文介绍：

支持转换为内存对象的场景。
实现转换为内存对象的技术步骤。
转换为内存对象之前的先决条件。
突出了内存优化的性能优势的代码示例

答：内存优化表变量的基础知识

内存优化表变量使用与内存优化表相同的内存优化算法和数据结构，因此具有很高的效率。从本机编译模块内访问表变量时，效率将最大化。

内存优化表变量：

仅存储在内存中，在磁盘上没有任何组件。
不涉及 IO 活动。
不涉及tempdb 利用或争用。
可以作为表值参数 (TVP) 传递到存储过程。
必须具有至少一个索引，哈希或非聚集索引。
- 对于哈希索引，理想情况下桶计数应为预期的唯一索引键数的 1-2 倍，但是估计过高的桶计数通常也没有问题（高达 10 倍）。有关详细信息，请参阅内存优化表的索引。

对象类型

内存 OLTP 提供以下可用于内存优化临时表和表变量的对象：

内存优化表
- 持久性 = SCHEMA_ONLY
内存优化表变量
- 必须在两个步骤中（而不是以内联的方式）声明：
  - CREATE TYPE my_type AS TABLE ...; ，然后
  - DECLARE @mytablevariable my_type;。

B. 方案：替换全局临时表

将全局临时表替换为内存优化的 SCHEMA_ONLY 表非常简单。最大的改变是在部署时（而不是运行时）创建该表。由于采用了编译时优化，创建内存优化表会比创建传统表所用时间更长。创建和删除内存优化表作为在线工作负载的一部分，会影响工作负载的性能，也会影响 Always On 可用性组辅助数据库的重做性能和数据库恢复的性能。

假设有以下全局临时表。

CREATE TABLE ##tempGlobalB
(
    Column1 INT NOT NULL,
    Column2 NVARCHAR (4000)
);

请考虑将全局临时表替换为以下 DURABILITY = SCHEMA_ONLY 的内存优化表。

CREATE TABLE dbo.soGlobalB
(
    Column1 INT NOT NULL INDEX ix1 NONCLUSTERED,
    Column2 NVARCHAR (4000)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

步骤

从全局临时转换为 SCHEMA_ONLY 的步骤如下：

只需像创建传统磁盘表一样创建dbo.soGlobalB表一次。
从 Transact-SQL（T-SQL）中删除 ##tempGlobalB 创建表操作。请务必在部署时创建内存优化表，而不是在运行时创建内存优化表，以避免创建表带来的编译开销。
在 T-SQL 中，将所有提及的 ##tempGlobalB 替换为 dbo.soGlobalB。

°C 方案：替换会话临时表

替换会话临时表的准备工作包含的 T-SQL 比之前的全局临时表场景要多。令人高兴的是，额外的 T-SQL 并不意味着完成转换需要付出更多努力。

和全局临时表方案一样，最大的改变就是在部署时（而不是运行时）创建表，从而避免编译开销。

假设有以下会话临时表。

CREATE TABLE #tempSessionC
(
    Column1 INT NOT NULL,
    Column2 NVARCHAR (4000)
);

首先，创建下表值函数以筛选 @@spid。函数可用于所有由会话临时表转换而来的 SCHEMA_ONLY 表。

CREATE FUNCTION dbo.fn_SpidFilter
(@SpidFilter SMALLINT)
RETURNS TABLE
WITH SCHEMABINDING, NATIVE_COMPILATION
AS
RETURN
    SELECT 1 AS fn_SpidFilter
    WHERE @SpidFilter = @@spid

其次，创建 SCHEMA_ONLY 表，并在该表上添加安全策略。

每个内存优化表必须至少有一个索引。

对于表 dbo.soSessionC，如果我们要计算合适的 BUCKET_COUNT，则使用哈希索引可能更好。不过在本示例中我们简化为使用非聚集索引。

CREATE TABLE dbo.soSessionC
(
    Column1 INT NOT NULL,
    Column2 NVARCHAR (4000) NULL,
    SpidFilter SMALLINT DEFAULT (@@spid) NOT NULL,
    CONSTRAINT CHK_soSessionC_SpidFilter CHECK (SpidFilter = @@spid),
    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter)
    -- INDEX ix_SpidFilter HASH
    --    (SpidFilter) WITH (BUCKET_COUNT = 64),
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO

CREATE SECURITY POLICY dbo.soSessionC_SpidFilter_Policy
    ADD FILTER PREDICATE dbo.fn_SpidFilter(SpidFilter) ON dbo.soSessionC
    WITH (STATE = ON);
GO

再次，在常规的 T-SQL 代码中：

将 Transact-SQL 语句中对临时表的所有引用替换为新的内存优化表：
- 旧体验：
- 新体验：
将代码中的 CREATE TABLE #tempSessionC 语句替换为 DELETE FROM dbo.soSessionC，以确保会话不会暴露于由具有相同 session_id 的先前会话插入的表内容。请务必在部署时创建内存优化表，而不是在运行时创建内存优化表，以避免创建表带来的编译开销。
将DROP TABLE #tempSessionC语句从代码中删除。（可选）如果内存大小为潜在问题，可以插入 DELETE FROM dbo.soSessionC 语句。

D. 场景：表变量可以将 MEMORY_OPTIMIZED 设置为 ON

传统表变量表示数据库中的 tempdb 表。为了提高性能，可以内存优化表变量。

下面是传统表变量的 T-SQL。其作用域在批处理或会话结束时结束。

DECLARE @tvTableD TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR (10));

将内联转换为显式

前面的语法是要以内联方式创建表变量。内联语法不支持内存优化。因此，让我们针对 TYPE 将内联语法转换为显式语法。

范围： 即使服务器关闭并重新启动，第一个带分隔符的批处理创建的 TYPE 定义也会持续存在。但是在第一个 go 分隔符之后，声明的表 @tvTableC 仅会保留到到达下一个 go 分隔符，并且批处理将结束。

CREATE TYPE dbo.typeTableD AS TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR (10));
GO

SET NOCOUNT ON;

DECLARE @tvTableD AS dbo.typeTableD;
INSERT INTO @tvTableD (Column1) VALUES (1), (2);
SELECT * FROM @tvTableD;
GO

D.2 将显式磁盘转换为内存优化

内存优化表变量不驻留在 tempdb。内存优化会使速度变快，通常快 10 倍或更多。

转换为内存优化只要一个步骤就可以完成。按如下所示加强类型创建功能，增加了以下内容：

一个索引。再次提醒，每个内存优化表都必须具有至少一个索引。
MEMORY_OPTIMIZED = ON。

CREATE TYPE dbo.typeTableD AS TABLE (
    Column1 INT NOT NULL INDEX ix1,
    Column2 CHAR (10))
    WITH (MEMORY_OPTIMIZED = ON);

完成。

E. SQL Server 的先决条件 FILEGROUP

在 Microsoft SQL Server 上，若要使用内存优化功能，数据库必须具有一个 FILEGROUP，该 FILEGROUP 通过 MEMORY_OPTIMIZED_DATA 声明。

Azure SQL 数据库不需要创建此 FILEGROUP。

先决条件： 下面的针对 FILEGROUP 的 Transact-SQL 代码是本文后面小节中较长的 T-SQL 代码示例的先决条件。

必须使用可提交 T-SQL 的 SSMS.exe 或另一种工具。
将示例 FILEGROUP T-SQL 代码粘贴到 SSMS。
编辑 T-SQL，根据自己的喜好更改其特定名称和目录路径。

FILENAME 值中的所有目录必须预先存在，最后一个目录则不能预先存在。

运行已编辑的 T-SQL。

无需多次运行 FILEGROUP T-SQL 语句，即便在下一个小节中反复调整并重新运行速度比较 T-SQL 语句。

ALTER DATABASE InMemTest2
    ADD FILEGROUP FgMemOptim3 CONTAINS MEMORY_OPTIMIZED_DATA;
GO

ALTER DATABASE InMemTest2
    ADD FILE (NAME = N'FileMemOptim3a', FILENAME = N'C:\DATA\FileMemOptim3a'
    -- C:\DATA\ preexisted.
) TO FILEGROUP FgMemOptim3;
GO

以下脚本为你创建文件组，并配置建议的数据库设置： enable-in-memory-oltp.sql

有关针对 FILE 和 FILEGROUP 的 ALTER DATABASE ... ADD 的详细信息，请参阅：

F. 证明速度提高的快速测试

本节提供的 Transact-SQL 代码用于测试并比较 INSERT-DELETE 从使用内存优化表变量中的速度提升效果。代码由几乎一样的两部分组成，除了第一部分的表类型为内存优化。

比较测试的持续时间大约为 7 秒。要运行该示例：

先决条件： 必须在上一节中已运行 FILEGROUP T-SQL。
运行以下 T-SQL INSERT-DELETE 脚本。

请注意该 GO 5001 语句，该语句重新提交 T-SQL 5,001 次。你可以调整该数字，然后重新运行。

在 Azure SQL 数据库中运行该脚本时，请确保从同一区域中的 VM 上运行。

PRINT ' ';
PRINT '---- Next, memory-optimized, faster. ----';
DROP TYPE IF EXISTS dbo.typeTableC_mem;
GO

CREATE TYPE dbo.typeTableC_mem -- !!  Memory-optimized.
AS TABLE (
    Column1 INT NOT NULL INDEX ix1,
    Column2 CHAR(10)
)
WITH (MEMORY_OPTIMIZED = ON);
GO

DECLARE @dateString_Begin NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_Begin,
    ' = Begin time, _mem.'
);
GO

SET NOCOUNT ON;

DECLARE @tvTableC dbo.typeTableC_mem;-- !!

INSERT INTO @tvTableC (Column1) VALUES (1), (2);
INSERT INTO @tvTableC (Column1) VALUES (3), (4);

DELETE @tvTableC;GO 5001

DECLARE @dateString_End NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_End,
    ' = End time, _mem.'
);
GO

DROP TYPE IF EXISTS dbo.typeTableC_mem;
GO

---- End memory-optimized.
-------------------------------------------------
---- Start traditional on-disk.
PRINT ' ';
PRINT '---- Next, tempdb based, slower. ----';

DROP TYPE IF EXISTS dbo.typeTableC_tempdb;
GO

CREATE TYPE dbo.typeTableC_tempdb -- !!  Traditional tempdb.
AS TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR(10)
);
GO

DECLARE @dateString_Begin NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_Begin,
    ' = Begin time, _tempdb.'
);
GO

SET NOCOUNT ON;

DECLARE @tvTableC dbo.typeTableC_tempdb;-- !!

INSERT INTO @tvTableC (Column1) VALUES (1), (2);
INSERT INTO @tvTableC (Column1) VALUES (3), (4);

DELETE @tvTableC;
GO 5001

DECLARE @dateString_End NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_End,
    ' = End time, _tempdb.'
);
GO

DROP TYPE IF EXISTS dbo.typeTableC_tempdb;
GO

PRINT '---- Tests done. ----';
GO

结果集如下。

---- Next, memory-optimized, faster. ----
2016-04-20 00:26:58.033  = Begin time, _mem.
Beginning execution loop
Batch execution completed 5001 times.
2016-04-20 00:26:58.733  = End time, _mem.

---- Next, tempdb based, slower. ----
2016-04-20 00:26:58.750  = Begin time, _tempdb.
Beginning execution loop
Batch execution completed 5001 times.
2016-04-20 00:27:05.440  = End time, _tempdb.
---- Tests done. ----

G. 预测活动内存使用

你可以通过以下资源预测内存优化表的活动内存需求：

对于较大的表变量，非聚集索引所使用的内存大于它们对内存优化表所使用的内存。行数和索引键越大，这种差别就越大。

如果每次访问内存优化表变量时只使用一个准确的键值，那么哈希索引是比非聚集索引更好的选择。但是，如果您无法估计合适的 BUCKET_COUNT，那么使用非聚集索引是一个很好的第二选择。

通过