メモリ最適化を使用した一時テーブルとテーブル変数の高速化

2025-05-31

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

一時テーブル、テーブル変数、またはテーブル値パラメーターを使用する場合は、メモリ最適化テーブルとテーブル変数を使用してパフォーマンスを向上させるための変換を検討してください。通常、コードの変更はわずかです。

この記事では、次の内容について説明します。

メモリ内への変換を優先すると主張するシナリオ
メモリ内への変換を実装するための技術的な手順
メモリ内に変換する前の前提条件
メモリ最適化のパフォーマンスの利点を強調表示するコードサンプル

Ａ。メモリ最適化テーブル変数の基本

メモリ最適化テーブル変数は、メモリ最適化テーブルで使用されるのと同じメモリ最適化アルゴリズムとデータ構造を使用して、優れた効率性を提供します。テーブル変数がネイティブコンパイルモジュール内からアクセスされる場合に、効率が最大限になります。

メモリ最適化テーブル変数:

メモリ内にのみ格納され、ディスク上にコンポーネントはありません。
IO アクティビティは関係しません。
tempdb使用率や競合は発生しません。
テーブル値パラメーター (TVP) としてストアドプロシージャに渡すことができます。
ハッシュまたは非クラスター化の少なくとも 1 つのインデックスがある必要があります。
- ハッシュインデックスの場合、バケット数は予期される一意のインデックスキーの数の 1 ～ 2 倍にするのが理想的ですが、バケット数を多めに設定しても通常は問題ありません (最大 10 倍)。詳細については、「メモリ最適化テーブルのインデックス」を参照してください。

オブジェクトの種類

インメモリ OLTP では、メモリ最適化一時テーブルおよびテーブル変数に使用できる次のオブジェクトを提供します。

メモリ最適化テーブル
- 持続性 = SCHEMA_ONLY
メモリ最適化テーブル変数
- 次の 2 つの手順 (インラインでなく) で宣言する必要があります。
  - 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 テーブルを 1 回作成します。
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 テーブルを作成します。

各メモリ最適化テーブルには、少なくとも 1 つのインデックスが必要です。

テーブル dbo.soSessionC では、適切な BUCKET_COUNT を計算する場合、HASH インデックスが望ましいことがあります。ただし、このサンプルでは、NONCLUSTERED インデックスに簡略化します。

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 ステートメントの一時テーブルのすべての参照をメモリ最適化テーブルに変更します。
- 旧: #tempSessionC
- 新: dbo.soSessionC
コード内の 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));

インラインから明示的への変換

上記の構文は、テーブル変数 inlineを作成すると考えられます。インライン構文では、メモリ最適化はサポートされていません。そのため、インライン構文を TYPE の明示的な構文に変換します。

スコープ： 最初の go 区切りバッチによって作成された 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 倍以上速くなります。

メモリ最適化への変換は、1 つだけの手順で行えます。明示的な TYPE の作成を次のように強化します。次の内容が追加されます。

インデックス。それぞれのメモリ最適化テーブルには、1 つ以上のインデックスが必要です。
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 では、メモリ最適化機能を使用するには、データベースに MEMORY_OPTIMIZED_DATA で宣言された FILEGROUP が必要です。

Azure SQL Database では、この FILEGROUP を作成する必要はありません。

前提条件: 次の FILEGROUP の Transact-SQL コードは、この記事の以降のセクションにある長い T-SQL コードサンプルの前提条件です。

SSMS.exe または T-SQL を送信できる他のツールを使用する必要があります。
サンプルの FILEGROUP T-SQL コードを SSMS に貼り付けます。
T-SQL を編集して、自分のリンクへの特定の名前とディレクトリパスを変更します。

最後のディレクトリが前から存在する必要がない場合を除いて、FILENAME 値のすべてのディレクトリは前から存在する必要があります。

編集した T-SQL を実行します。

次のサブセクションで速度比較 T-SQL を繰り返し調整して再実行した場合でも、FILEGROUP 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. 速度の向上を証明するための簡単なテスト

このセクションでは、使用しているメモリ最適化テーブル変数から、INSERT-DELETE の速度の向上をテストおよび比較するために実行できるように、Transact-SQL コードを提供します。このコードは、前半のテーブル型がメモリ最適化である場合を除いて、2 つのほぼ同じ要素で構成されます。

比較テストには、約 7 秒かかります。サンプルを実行するには

前提条件: 前のセクションで既に FILEGROUP T-SQL を実行している必要があります。
次の T-SQL INSERT-DELETE スクリプトを実行します。

T-SQL を 5,001 回再送信する GO 5001 ステートメントに注目してください。この数字は調整して、再実行することができます。

Azure SQL Database でスクリプトを実行している場合、同じリージョン内の 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を見積もることができない場合は、NONCLUSTERED インデックスが第 2 の選択肢として適しています。