インメモリ OLTP の概要と使用シナリオ

適用対象:データベース Azure SQL Managed Instance Azure SQL SQL Server (サポートされているすべてのバージョン)

In-Memory OLTP は、トランザクション処理、データ インジェスト、データ読み込み、一時的なデータ シナリオのパフォーマンスを最適化するために、SQL ServerとSQL Databaseで利用できる最高のテクノロジです。 この記事では、インメモリ OLTP のテクノロジと使用シナリオの概要について説明します。 この情報を参照して、インメモリ OLTP が用途に合っているかどうかを判断してください。 この記事の最後には、インメモリ OLTP オブジェクトを表示する例、パフォーマンス デモの参照、次のステップに使用できるリソースの参照を掲載しています。

この記事では、SQL ServerとSQL Databaseの両方でIn-Memory OLTP テクノロジについて説明します。 Azure SQLのインメモリ データに固有の詳細については、「Azure SQL データベースとAzure SQL Managed Instanceおよびブログ: Azure SQL データベースの OLTP のIn-Memory」のインメモリ テクノロジを使用したパフォーマンスの最適化に関する記事を参照してください。

インメモリ OLTP の概要

インメモリ OLTP は、適切なワークロードの場合にパフォーマンスが大きく向上します。 一部の事例では、最大 30 倍のパフォーマンス向上が見られていますが、向上率はワークロードによって変わります。

それでは、このパフォーマンス向上は何に由来するのでしょうか。 つまり、In-Memory OLTP は、データ アクセスとトランザクション実行の効率を高め、同時に実行されるトランザクション間のロックとラッチの競合を取り除くことで、トランザクション処理のパフォーマンスを向上させます。 In-Memory OLTP はインメモリであるため、高速ではありません。メモリ内のデータを中心に最適化されているため、高速です。 メモリ内のコンカレンシー処理が多い計算に関する最新の機能強化を利用するように、データ ストレージ、アクセス、処理アルゴリズムはゼロから再設計されました。

これで、データがメモリ内に存在するからといって、障害が発生しても失われるわけではありません。 既定で、すべてのトランザクションは完全に持続可能になりました。つまり、SQL Server の他のテーブルと同じ持続可能性の保証があります。トランザクション コミットの一部として、すべての変更はディスク上のトランザクション ログに書き込まれます。 トランザクション コミット後のどこかの時点で障害が発生すると、データベースがオンラインに戻ったときに、コミットされたデータを利用できます。 さらに、In-Memory OLTP は、Always On可用性グループ、Always Onフェールオーバー クラスター インスタンス (SQL Server)、バックアップ/復元など、SQL Serverのすべての高可用性とディザスター リカバリー機能で動作します。

データベースIn-Memory OLTP を使用するには、次の種類のオブジェクトのうち 1 つ以上を使用します。

  • メモリ最適化テーブル は、ユーザー データの格納に使用されます。 テーブルは、作成時にメモリが最適化されるように宣言します。
  • 非持続的テーブル は、キャッシュまたは中間の結果セット用の一時的なデータに使用されます (従来の一時テーブルは置き換えられます)。 非永続テーブルは、DURABLEITY=SCHEMA_ONLY で宣言されているメモリ最適化テーブルです。つまり、これらのテーブルに対する変更では IO は発生しません。 そのため、持続性が重要ではない場合、ログ IO リソースの消費を回避することができます。
  • メモリ最適化テーブル型 は、テーブル値パラメーター (TVP) だけでなく、ストアド プロシージャの中間結果セットにも使用されます。 メモリ最適化テーブル型は、従来のテーブル型の代わりに使用できます。 メモリ最適化テーブル型を使用して宣言したテーブル変数と TVP は、非持続的メモリ最適化テーブルの利点 (効率的なデータ アクセス、IO なし) を継承します。
  • ネイティブ コンパイル T-SQL モジュール は、操作の処理に必要な CPU サイクルを減らして、個々のトランザクションにかかる時間をさらに短縮するために使用されます。 Transact-SQL モジュールは、作成時にネイティブでコンパイルするように宣言します。 このとき、ストアド プロシージャ、トリガー、スカラー ユーザー定義関数という T-SQL モジュールをネイティブでコンパイルできます。

In-Memory OLTP は、SQL ServerとSQL Databaseに組み込まれています。 これらのオブジェクトは従来のオブジェクトと同様に動作するため、データベースとアプリケーションに最小限の変更を加えるだけでパフォーマンス上の利点が得られることがよくあります。 さらに、同じデータベースにメモリ最適化テーブルと従来のディスクベースのテーブルの両方を持ち、2 つのテーブルに対してクエリを実行することができます。 この記事の下部には、これらの種類のオブジェクトの例を含む Transact-SQL スクリプトがあります。

In-Memory OLTP の使用シナリオ

In-Memory OLTP は魔法の高速ボタンではなく、すべてのワークロードに適しているわけではありません。 たとえば、ほとんどのクエリが広範囲に及ぶデータに対する集計を行う場合、メモリ最適化テーブルで CPU 使用率は下がりません。 このようなシナリオには、列ストア インデックスが役立ちます。

In-Memory OLTP で顧客が成功するシナリオとアプリケーション パターンの一覧を次に示します。

高スループット、低遅延トランザクションの処理

これはインメモリ OLTP を構築する中核となるシナリオです。個々のトランザクションの低遅延を保ちながら、大量のトランザクションに対応します。

一般的なワークロード シナリオとして、金融商品の取引、スポーツくじ、モバイル ゲーム、広告配信などがあります。 また、一般的なパターンとして、読み取りや更新が頻繁な "カタログ" もあります。 たとえば、大きなファイルを複数のクラスターノードに分散して配置し、各ファイルのシャードの位置をメモリ最適化テーブルでカタログ化するような場合です。

実装時の注意事項

中核となるトランザクション テーブル、つまり、最もパフォーマンスが重要なトランザクションがあるテーブルにメモリ最適化テーブルを使用します。 ネイティブでコンパイルされたストアド プロシージャを使用して、ビジネス トランザクションに関連付けられたロジックの実行を最適化します。 データベースのストアド プロシージャに組み込むことができるロジック数が多いほど、インメモリ OLTP の利点も大きくなります。

既存のアプリケーションで開始するには:

  1. トランザクション パフォーマンス分析レポートを使用して移行するオブジェクトを特定します。
  2. 移行用にメモリ最適化およびネイティブ コンパイル アドバイザーを使用します。

IoT (モノのインターネット) などのデータ統合

インメモリ OLTP は、同時にさまざまなソースから大量のデータを取り込む処理が特に得意です。 また、SQL Serverはデータに対するクエリを高速に実行し、リアルタイムの分析情報を取得できるため、他の宛先と比較して、SQL Server データベースにデータを取り込むのに役立つことがよくあります。

一般的なアプリケーション パターン:

  • センサーの読み取りとイベントを取り込みます。通知だけでなく履歴分析を可能にします。
  • 複数のソースからでも、一括更新を管理し、同時読み取りのワークロードに対する影響を最小限に抑えることができます。

実装時の注意事項

データの取り込みにメモリ最適化テーブルを使用します。 取り込みの大部分が (更新ではなく) 挿入で構成され、データのインメモリ OLTP ストレージの占有領域が重要な場合、次のいずれかを行います。

  • ジョブを使用して、クラスター化列ストア インデックスを持つディスク ベースのテーブルにデータを定期的にバッチ オフロードします。または、INSERT INTO <disk-based table> SELECT FROM <memory-optimized table>
  • 一時メモリ最適化テーブルを使用して、履歴データを管理します。このモードでは、履歴データはディスクに保存され、データの移動はシステムによって管理されます。

SQL Server サンプル リポジトリに一時メモリ最適化テーブル、メモリ最適化テーブル型、およびネイティブ コンパイル ストアド プロシージャを使用するスマート グリッド アプリケーション含めることでデータの取り込みを高速化し、センサー データのインメモリ OLTP ストレージの占有領域を管理しています。

キャッシュとセッションの状態

In-Memory OLTP テクノロジは、SQL ServerデータベースまたはAzure SQL データベース内のデータベース エンジンを、セッション状態 (たとえば、ASP.NET アプリケーション) を維持し、キャッシュするための魅力的なプラットフォームにします。

ASP.NET セッション状態は、OLTP をIn-Memoryするための成功したユース ケースです。 SQL Server では、あるお客様が毎秒約 120 万要求を達成しました。 一方、社内のすべての中間層アプリケーションのキャッシュ ニーズにインメモリ OLTP を使用し始めました。 詳細: bwin SQL Server 2016 (13.x) In-Memory OLTP を使用して、これまでにないパフォーマンスとスケールを実現する方法

実装時の注意事項

varbinary(max) 列に BLOB を格納することで、簡易なキーと値のストアとして非持続的メモリ最適化テーブルを使用できます。 または、SQL ServerとSQL Databaseで JSON サポートを使用して半構造化キャッシュを実装することもできます。 最後に、多様なデータ型と制約を含み、完全なリレーショナル スキーマを使用する非持続的なテーブルで、完全なリレーショナル キャッシュを作成できます。

GitHub で発行されたスクリプトを使用して、組み込みのSQL Server セッション状態プロバイダーによって作成されたオブジェクトを置き換えることで、セッション状態 ASP.NET メモリ最適化を開始します。aspnet-session-state

Customer case study (お客様のケース スタディ)

tempdb オブジェクトの置換

非永続テーブルとメモリ最適化テーブル型を使用して、一時テーブル、テーブル変数、テーブル値パラメーター (TVP) などの従来 tempdb のベースの構造を置き換えます。

メモリ最適化テーブル変数と非持続的テーブルは、従来のテーブル変数と #temp テーブルと比較すると、一般的に CPU が減り、ログの IO が完全になくなります。

実装時の注意事項

開始するには、以下をご覧ください。メモリ最適化を使用した一時テーブルとテーブル変数のパフォーマンスの向上。

Customer case study (お客様のケース スタディ)

ETL (抽出、変換、読み込み)

多くの場合、ETL ワークフローには、データのステージング テーブルへの読み込み、データの変換、最終的なテーブルへの読み込みが含まれています。

データのステージングには非持続的メモリ最適化テーブルを使用します。 すべての IO が完全になくなり、データ アクセスがより効率的になります。

実装時の注意事項

ワークフローの一部としてステージング テーブルで変換を実行する場合、ネイティブ コンパイル ストアド プロシージャを使用すると、変換を高速化できます。 このような変換を並列して実行できると、メモリ最適化からさらにスケール メリットが得られます。

サンプル スクリプト

インメモリ OLTP の使用を開始する前に、MEMORY_OPTIMIZED_DATA ファイルグループを作成する必要があります。 さらに、データベース互換性レベル 130 (以上) を使用し、データベース オプション MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT をオンに設定することをお勧めします。

次の場所のスクリプトを使用して、既定のデータ フォルダーにファイルグループを作成し、推奨される設定を構成できます。

次のサンプル スクリプトは、データベースIn-Memory作成できる OLTP オブジェクトを示しています。

まず、In-Memory OLTP 用にデータベースを構成します。

-- configure recommended DB option
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;
GO

異なる持続性を持つテーブルを作成します。

-- memory-optimized table
CREATE TABLE dbo.table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON);
GO
-- non-durable table
CREATE TABLE dbo.temp_table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON,
      DURABILITY=SCHEMA_ONLY);
GO

インメモリ テーブルとしてテーブル型を作成します。

-- memory-optimized table type
CREATE TYPE dbo.tt_table1 AS TABLE
( c1 INT IDENTITY,
  c2 NVARCHAR(MAX),
  is_transient BIT NOT NULL DEFAULT (0),
  INDEX ix_c1 HASH (c1) WITH (BUCKET_COUNT=1024))
WITH (MEMORY_OPTIMIZED=ON);
GO

ネイティブ コンパイル ストアド プロシージャを作成します。 詳細については、「データ アクセス アプリケーションからのネイティブ コンパイル ストアド プロシージャの呼び出し」を参照してください。

-- natively compiled stored procedure
CREATE PROCEDURE dbo.usp_ingest_table1
  @table1 dbo.tt_table1 READONLY
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT,
          LANGUAGE=N'us_english')

  DECLARE @i INT = 1

  WHILE @i > 0
  BEGIN
    INSERT dbo.table1
    SELECT c2
    FROM @table1
    WHERE c1 = @i AND is_transient=0

    IF @@ROWCOUNT > 0
      SET @i += 1
    ELSE
    BEGIN
      INSERT dbo.temp_table1
      SELECT c2
      FROM @table1
      WHERE c1 = @i AND is_transient=1

      IF @@ROWCOUNT > 0
        SET @i += 1
      ELSE
        SET @i = 0
    END
  END

END
GO
-- sample execution of the proc
DECLARE @table1 dbo.tt_table1;
INSERT @table1 (c2, is_transient) VALUES (N'sample durable', 0);
INSERT @table1 (c2, is_transient) VALUES (N'sample non-durable', 1);
EXECUTE dbo.usp_ingest_table1 @table1=@table1;
SELECT c1, c2 from dbo.table1;
SELECT c1, c2 from dbo.temp_table1;
GO

詳細情報に関するリソース

こちらもご覧ください

次の手順