用于分区内存优化表的应用程序模式

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

In-Memory OLTP 支持一种应用程序设计模式，该模式优先考虑当前数据的性能。当读取或更新当前数据比旧数据更频繁时，此模式适用。当前数据被视为活动数据或热数据，较旧的数据为冷数据。

主要核心思路是将热数据存储在内存优化表中。每周或每月，冷却的旧数据将被移动到分区表中。已分区表将其数据存储在磁盘或其他硬盘驱动器上，而不是存储在内存中。

通常，此设计使用 datetime2 键使迁移过程能够有效地区分热数据和冷数据。

高级分区

该设计旨在模拟一个具有一个内存优化分区的已分区表。若要使此设计正常工作，请确保表共享通用架构。本文后面的代码示例演示了此方法。

新数据始终被视为热数据。在内存优化表中插入热数据，并对其进行更新。冷数据是在传统分区表中维护的。存储过程将定期添加新分区。分区包含从内存优化表中移出的最新冷数据。

如果一个操作只需要热数据，可以使用本机编译的存储过程来访问数据。可能访问热数据或冷数据的操作必须使用经过解释的 Transact-SQL，将内存优化表与已分区表联接在一起。

添加分区

最近变冷的数据必须移动到分区表中。这种定期分区交换的步骤如下：

对于内存优化表中的数据，确定作为热数据与新的冷数据之间的分界线或分界点的日期时间。
将来自 OLTP 表 cold_staging In-Memory 的新冷数据插入表中。
从内存优化表中删除相同的冷数据。
将 cold_staging 表交换到分区中。
添加分区。

维护时段

前面的一个步骤是从内存优化表中删除新的冷数据。此删除与添加新分区的最后一步之间存在时间间隔。在此时间间隔内，尝试读取新冷数据的任何应用程序都失败。

有关示例，请参阅应用程序级分区。

代码示例

下面的 Transact-SQL 示例以一系列较小的代码块显示，这只是为了便于演示。可以将它们都附加到一个大的代码块中，以便进行测试。

从整体来看，T-SQL 示例展示了如何将内存优化表与经过分区的基于磁盘的表一起使用。

T-SQL 示例的第一阶段创建数据库，然后在数据库中创建对象（如表）。后面的阶段展示如何将数据从内存优化表移至经过分区的表。

创建数据库

T-SQL 示例的此部分将创建一个测试数据库。将数据库配置为同时支持内存优化表和已分区表。

CREATE DATABASE PartitionSample;
GO

-- Add a FileGroup, enabled for In-Memory OLTP.
-- Change file path as needed.
ALTER DATABASE PartitionSample
    ADD FILEGROUP PartitionSample_mod
    CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE PartitionSample
    ADD FILE (
        NAME = 'PartitionSample_mod',
        FILENAME = 'C:\data\PartitionSample_mod')
    TO FILEGROUP PartitionSample_mod;
GO

为热数据创建内存优化表

该部分创建的内存优化表用于保存最新的数据，其中大部分仍为热数据。

USE PartitionSample;
GO

-- Create a memory-optimized table for the HOT Sales Order data.
-- Notice the index that uses DATETIME2.
CREATE TABLE dbo.SalesOrders_hot
(
    so_id INT IDENTITY PRIMARY KEY NONCLUSTERED,
    cust_id INT NOT NULL,
    so_date DATETIME2 NOT NULL INDEX ix_date NONCLUSTERED,
    so_total MONEY NOT NULL,
    INDEX ix_date_total NONCLUSTERED (so_date DESC, so_total DESC)
)
WITH (MEMORY_OPTIMIZED = ON);
GO

为冷数据创建已分区表

这个部分创建已分区表，用于保存冷数据。

-- Create a partition and table for the COLD Sales Order data.
-- Notice the index that uses DATETIME2.
CREATE PARTITION FUNCTION [ByDatePF](DATETIME2)
    AS RANGE RIGHT
    FOR VALUES ();
GO

CREATE PARTITION SCHEME [ByDateRange]
    AS PARTITION [ByDatePF]
    ALL TO ([PRIMARY]);
GO

CREATE TABLE dbo.SalesOrders_cold
(
    so_id INT NOT NULL,
    cust_id INT NOT NULL,
    so_date DATETIME2 NOT NULL,
    so_total MONEY NOT NULL,
    CONSTRAINT PK_SalesOrders_cold PRIMARY KEY (so_id, so_date),
    INDEX ix_date_total NONCLUSTERED (so_date DESC, so_total DESC)
) ON [ByDateRange](so_date);
GO

创建一个在移动过程中存储冷数据的表

本部分将创建 cold_staging 表。同时创建了一个视图，将两个表的热数据和冷数据结合在一起。

-- A table used to briefly stage the newly cold data, during moves to a partition.
CREATE TABLE dbo.SalesOrders_cold_staging
(
    so_id INT NOT NULL,
    cust_id INT NOT NULL,
    so_date DATETIME2 NOT NULL,
    so_total MONEY NOT NULL,
    CONSTRAINT PK_SalesOrders_cold_staging PRIMARY KEY (so_id, so_date),
    CONSTRAINT CHK_SalesOrders_cold_staging CHECK (so_date >= '1900-01-01'),
    INDEX ix_date_total NONCLUSTERED (so_date DESC, so_total DESC)
);
GO

-- A view, for retrieving the aggregation of hot plus cold data.
CREATE VIEW dbo.SalesOrders AS
    SELECT so_id,
           cust_id,
           so_date,
           so_total,
           1 AS 'is_hot'
    FROM dbo.SalesOrders_hot
    UNION ALL
    SELECT so_id,
           cust_id,
           so_date,
           so_total,
           0 AS 'is_cold'
    FROM dbo.SalesOrders_cold;
GO

创建存储过程

该部分用于创建您需要定期运行的存储过程。此过程将内存优化表中新的冷数据移到已分区表中。

注释

如果连续快速调用此过程， SYSDATETIME() 可能会为连续调用返回相同的 datetime2 值。在这种情况下， ALTER PARTITION FUNCTION ... SPLIT RANGE 失败并出现错误 7721，因为分区函数中已存在边界值。空格调用过程，以便 @splitdate 每个调用的值都是不同的。

-- A stored procedure to move all newly cold sales orders data
-- to its staging location.
CREATE PROCEDURE dbo.usp_SalesOrdersOffloadToCold
@splitdate DATETIME2
AS
BEGIN
    BEGIN TRANSACTION;

    -- Insert the cold data as a temporary heap.
    INSERT INTO dbo.SalesOrders_cold_staging WITH (TABLOCKX)
    SELECT so_id,
           cust_id,
           so_date,
           so_total
    FROM dbo.SalesOrders_hot WITH (SERIALIZABLE)
    WHERE so_date < @splitdate;

    -- Delete the moved data from the hot table.
    DELETE dbo.SalesOrders_hot WITH (SERIALIZABLE)
    WHERE so_date < @splitdate;

    -- Update the partition function, and switch in the new partition.
    ALTER PARTITION SCHEME [ByDateRange] NEXT USED [PRIMARY];

    DECLARE @p AS INT = (SELECT MAX(partition_number)
                         FROM sys.partitions
                         WHERE object_id = OBJECT_ID('dbo.SalesOrders_cold'));

    EXECUTE sp_executesql N'ALTER TABLE dbo.SalesOrders_cold_staging
            SWITCH TO dbo.SalesOrders_cold partition @i', N'@i int', @i = @p;

    ALTER PARTITION FUNCTION [ByDatePF]()
        SPLIT RANGE (@splitdate);

    -- Modify a constraint on the cold_staging table, to align with new partition.
    ALTER TABLE dbo.SalesOrders_cold_staging
    DROP CONSTRAINT CHK_SalesOrders_cold_staging;

    DECLARE @s AS NVARCHAR (100) = CONVERT (NVARCHAR (100), @splitdate, 121);

    DECLARE @sql AS NVARCHAR (1000) = N'ALTER TABLE dbo.SalesOrders_cold_staging
        ADD CONSTRAINT CHK_SalesOrders_cold_staging CHECK (so_date >= ''' + @s + ''')';

    PRINT @sql;

    EXECUTE sp_executesql @sql;

    COMMIT TRANSACTION;

END
GO

准备示例数据，并演示存储过程

这一部分生成并插入示例数据，然后运行存储过程作为演示。

-- Insert sample values into the hot table.
INSERT INTO dbo.SalesOrders_hot
VALUES (1, SYSDATETIME(), 1);
GO

INSERT INTO dbo.SalesOrders_hot
VALUES (1, SYSDATETIME(), 1);
GO

INSERT INTO dbo.SalesOrders_hot
VALUES (1, SYSDATETIME(), 1);
GO

-- Verify that the hot data is in the table, by selecting from the view.
SELECT *
FROM dbo.SalesOrders;
GO

-- Treat all data in the hot table as cold data:
-- Run the stored procedure, to move (offload) all sales orders to date to cold storage.
DECLARE @t AS DATETIME2 = SYSDATETIME();
EXECUTE dbo.usp_SalesOrdersOffloadToCold @t;

-- Again, read hot plus cold data from the view.
SELECT *
FROM dbo.SalesOrders;
GO

-- Retrieve the name of every partition.
SELECT OBJECT_NAME(object_id),
       *
FROM sys.dm_db_partition_stats AS ps
WHERE object_id = OBJECT_ID('dbo.SalesOrders_cold');

-- Insert more data into the hot table.
INSERT INTO dbo.SalesOrders_hot
VALUES (2, SYSDATETIME(), 1);
GO

INSERT INTO dbo.SalesOrders_hot
VALUES (2, SYSDATETIME(), 1);
GO

INSERT INTO dbo.SalesOrders_hot
VALUES (2, SYSDATETIME(), 1);
GO

-- Read hot plus cold data from the view.
SELECT *
FROM dbo.SalesOrders;
GO

-- Again, run the stored procedure, to move all sales orders to date to cold storage.
DECLARE @t AS DATETIME2 = SYSDATETIME();
EXECUTE dbo.usp_SalesOrdersOffloadToCold @t;

-- Read hot plus cold data from the view.
SELECT *
FROM dbo.SalesOrders;
GO

-- Again, retrieve the name of every partition.
-- The stored procedure can modify the partitions.
SELECT OBJECT_NAME(object_id),
       partition_number,
       row_count
FROM sys.dm_db_partition_stats AS ps
WHERE object_id = OBJECT_ID('dbo.SalesOrders_cold')
      AND index_id = 1;

删除所有演示对象

请记得清理测试系统中的演示测试数据库。

USE master;
GO

DROP DATABASE PartitionSample;
GO

内存中 OLTP 的示例数据库

反馈

此页面是否有帮助？

Last updated on 2026-04-23