メモリ最適化テーブルのパーティション分割に関するアプリケーションのパターン

[アーティクル]
04/26/2023

適用対象:SQL Server Azure SQL Database Azure SQL Managed Instance

インメモリ OLTP では、比較的新しいデータでパフォーマンスリソースをふんだんに利用するアプリケーション設計パターンがサポートされます。このパターンは、現在のデータが、以前のデータよりもはるかに頻繁に読み取られるか更新される場合に適用できます。ここでは、現在のデータが "アクティブ" または "ホット" で、古いデータが "コールド" であるとします。

基本的には、"ホット" データをメモリ最適化テーブルに格納します。 "コールド" になった古いデータは、週または月単位でパーティションテーブルに移動されます。パーティションテーブルのデータは、メモリ内ではなく、ディスクまたは他のハードドライブに格納されています。

通常、この設計では datetime キーを使用して、ホットデータとコールドデータを効率的に区別するために移動プロセスを有効にします。

高度なパーティション分割

この設計は、1 つのメモリ最適化パーティションも含むパーティションテーブルを模倣することを意図しています。この設計を機能させるには、すべてのテーブルで共通のスキーマが確実に共有されるようにする必要があります。この記事の後半にあるコードサンプルでは、この手法を示します。

新しいデータは、定義上、ホットであると見なされます。ホットデータは、メモリ最適化テーブルに挿入され、更新されます。コールドデータは、従来のパーティションテーブルに保持されます。ストアドプロシージャでは、定期的に新しいパーティションを追加します。パーティションには、メモリ最適化テーブルから移動された最新のコールドデータが含まれます。

操作でホットデータのみが必要な場合は、ネイティブコンパイルストアドプロシージャを使用してデータにアクセスできます。ホットデータまたはコールドデータにアクセスする可能性がある操作では、解釈された Transact-SQL を使用して、メモリ最適化テーブルをパーティションテーブルと結合する必要があります。

パーティションを追加する

最近コールドになったデータは、パーティションテーブルに移動する必要があります。この定期パーティションスワップの手順は次のとおりです。

メモリ最適化テーブルのデータでは、ホットデータと新しいコールドデータの間の境界またはカットオフである datetime を決定します。
In-Memory OLTP テーブルから新しくコールドデータを cold_staging テーブルに挿入します。
メモリ最適化テーブルから同じコールドデータを削除します。
cold_staging テーブルをパーティションにスワップします。
パーティションを追加します。

メンテナンス期間

上記の手順の 1 つは、メモリ最適化テーブルから新しいコールドデータを削除することです。このように削除してから、最後の手順で新しいパーティションを追加するまで、ある程度の期間があります。この期間中に、アプリケーションで新しいコールドデータを読み取ろうとすると失敗します。

関連サンプルについては、「アプリケーションレベルのパーティション分割」を参照してください。

コードサンプル

次の Transact-SQL サンプルは、プレゼンテーションを容易にする目的でのみ、一連の小さなコードブロックで表示されています。テストのために、それらをすべて 1 つの大きなコードブロックに追加することができます。

全体として、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 テーブルを作成します。 2 つのテーブルのホットおよびコールドデータを結合するビューも作成されます。

-- 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),
   INDEX ix_date_total NONCLUSTERED (so_date desc, so_total desc),
   CONSTRAINT CHK_SalesOrders_cold_staging CHECK (so_date >= '1900-01-01')
);
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

ストアドプロシージャを作成する

このセクションでは、定期的に実行するストアドプロシージャを作成します。プロシージャでは、新しいコールドデータをメモリ最適化テーブルからパーティションテーブルに移動します。

-- 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 FROM 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 INT = (
        SELECT MAX(partition_number)
            FROM sys.partitions
            WHERE object_id = OBJECT_ID('dbo.SalesOrders_cold'));

      EXEC 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 nvarchar( 100) = CONVERT( nvarchar( 100) , @splitdate , 121);
      DECLARE @sql nvarchar( 1000) = N'alter table dbo.SalesOrders_cold_staging 
         add constraint CHK_SalesOrders_cold_staging check (so_date > ''' + @s + ''')';
      PRINT @sql;
      EXEC sp_executesql @sql;

      COMMIT;
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 datetime2 = SYSDATETIME();
EXEC 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 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 datetime2 = SYSDATETIME();
EXEC 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 ps
  WHERE object_id = OBJECT_ID( 'dbo.SalesOrders_cold')
    AND index_id = 1;

すべてのデモオブジェクトを削除する

テストシステムからデモテストデータベースを消去することを忘れないでください。

-- You must first leave the context of the PartitionSample database.

-- USE <A-Database-Name-Here>;
GO

DROP DATABASE PartitionSample;
GO

参照

メモリ最適化テーブル

メモリ最適化テーブルのパーティション分割に関するアプリケーションのパターン

高度なパーティション分割

パーティションを追加する

メンテナンス期間

コードサンプル

データベースを作成する

ホットデータ用のメモリ最適化テーブルを作成する

コールドデータ用のパーティションテーブルを作成する

移動中にコールドデータを格納するテーブルを作成する

ストアドプロシージャを作成する

サンプルデータを準備し、ストアドプロシージャのデモを行う

すべてのデモオブジェクトを削除する

参照

フィードバック

フィードバック

その他のリソース

メモリ最適化テーブルのパーティション分割に関するアプリケーションのパターン

高度なパーティション分割

パーティションを追加する

メンテナンス期間

コード サンプル

データベースを作成する

ホット データ用のメモリ最適化テーブルを作成する

コールド データ用のパーティション テーブルを作成する

移動中にコールド データを格納するテーブルを作成する

ストアド プロシージャを作成する

サンプル データを準備し、ストアド プロシージャのデモを行う

すべてのデモ オブジェクトを削除する

参照

フィードバック

フィードバック

その他のリソース

コードサンプル

ホットデータ用のメモリ最適化テーブルを作成する

コールドデータ用のパーティションテーブルを作成する

移動中にコールドデータを格納するテーブルを作成する

ストアドプロシージャを作成する

サンプルデータを準備し、ストアドプロシージャのデモを行う

すべてのデモオブジェクトを削除する