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

[アーティクル]
05/30/2023

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

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

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

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

A. メモリ最適化テーブル変数の基本

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

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

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

オブジェクトの種類

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

メモリ最適化テーブル
- Durability = SCHEMA_ONLY
メモリ最適化テーブル変数
- 次の 2 つの手順 (インラインでなく) で宣言する必要があります。
  - CREATE TYPE my_type AS TABLE ...; 、その後
  - DECLARE @mytablevariable my_type;。

B. シナリオ: グローバル tempdb ##table の置換

グローバル一時テーブルとメモリ最適化 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);

B.1 手順

グローバル一時から SCHEMA_ONLY へ変換する手順は、次のとおりです。

従来のディスク上のテーブルと同様に、一度、dbo.soGlobalB テーブルを作成します。
Transact-SQL から、##tempGlobalB テーブルの作成を削除します。テーブルの作成から生じるコンパイルのオーバーヘッドを回避するには、メモリ最適化テーブルを実行時ではなく展開時に作成することが重要です。
T-SQL で、##tempGlobalB のすべてのメンションを dbo.soGlobalB に置き換えます。

C: シナリオ: セッション tempdb #table の置換

セッションの一時テーブルを置換するための準備には、以前のグローバル一時テーブルのシナリオよりも多く 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    NOT NULL   DEFAULT (@@spid),  

    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter),  
    --INDEX ix_SpidFilter HASH  
    --    (SpidFilter) WITH (BUCKET_COUNT = 64),  
        
    CONSTRAINT CHK_soSessionC_SpidFilter  
        CHECK ( SpidFilter = @@spid ),  
)  
    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) );

D.1 インラインを明示的に変換

上記の構文は、テーブル変数 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 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 スクリプトを実行します。

'GO 5001' ステートメントは、T-SQL を 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 インデックスを選択することをお勧めします。

H. 関連項目

メモリ最適化テーブル。
メモリ最適化オブジェクトの持続性の定義。
2017 年 9 月のブログで発表された、不適切なメモリ不足エラーの可能性を除去するための累積的な更新プログラム。
- 「SQL Server 2016 のビルドバージョン」には、リリース、サービスパック、および累積的な更新プログラムの詳細が示されています。
- このような不定期の不適切なエラーは、SQL Server Enterprise Edition では発生しませんでした。