SQL Server でのインメモリ OLTP 機能の採用計画

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

この記事では、SQL Server のインメモリ機能の導入がどのようにビジネス システムのその他の観点で影響を与えるかについて説明します。

Note

A. インメモリ OLTP 機能の採用

次のサブセクションでは、インメモリ機能を採用して実装する計画を立てる場合に考慮する必要がある要因について説明します。

A.1 前提条件

インメモリ機能を使用するための前提条件の 1 つに、SQL 製品のエディションまたはサービス層が関係する場合があります。 この前提条件とその他の前提条件については、次の情報を参照してください。

A.2 アクティブ メモリ量の予測

新しいメモリ最適化テーブルをサポートするのに十分なアクティブ メモリがシステムにありますか?

Microsoft SQL Server

200 GB のデータを含むメモリ最適化テーブルは、サポート専用に 200 GB を超えるアクティブ メモリが必要です。 大量のデータを含むメモリ最適化テーブルを実装する前に、サーバー コンピューターへの追加が必要になる場合がある追加のアクティブ メモリの量を予測する必要があります。 推定のガイダンスについては、次の情報を参照してください。

Azure SQL Managed Instance に同様のガイダンスを利用できます。

Azure SQL データベース

Azure SQL データベースのクラウド サービスでホストされているデータベースの場合、選択したサービス層が、データベースで消費可能なアクティブ メモリの量に影響します。 アラートを使用してデータベースのメモリ使用量を監視する計画を立てる必要があります。 詳細については、次の情報を参照してください。

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

tempdb データベースにある従来の #TempTable よりも、メモリ最適化されていると宣言されたテーブル変数の方が適している場合があります。 テーブル変数は、大量のアクティブ メモリを使用することなく、パフォーマンスを改善することができます。

A.3 メモリ最適化に変換するにはテーブルをオフラインにする必要がある

いくつかの ALTER TABLE 機能をメモリ最適化テーブルで使用することができます。 ただし、ALTER TABLE ステートメントを実行して、ディスク ベース テーブルをメモリ最適化テーブルに変換することはできません。 代わりに、より手動的な一連の手順を使用する必要があります。 ディスク ベース テーブルをメモリ最適化テーブルに変換できるさまざまな方法を以下に示します。

手動でのスクリプト作成

ディスク ベース テーブルをメモリ最適化テーブルに変換する方法の 1 つとして、必要な Transact-SQL ステップを自分でコード化する方法があります。

  1. アプリケーション アクティビティを中断します。

  2. 完全バックアップを実行します。

  3. ディスク ベース テーブルの名前を変更します。

  4. CREATE TABLE ステートメントを実行して、新しいメモリ最適化テーブルを作成します。

  5. ディスク ベース テーブルからサブ SELECT を指定してメモリ最適化テーブルへの挿入を行います。

  6. ディスク ベース テーブルを削除します。

  7. さらに完全バックアップを実行します。

  8. アプリケーション アクティビティを再開します。

メモリ最適化アドバイザー

メモリ最適化アドバイザー ツールでは、ディスク ベース テーブルからメモリ最適化テーブルへの変換に役立つスクリプトを生成できます。 このツールは、SQL Server Data Tools (SSDT) の一部としてインストールされます。

.dacpac ファイル

SSDT で管理される、.dacpac ファイルを使用してデータベースのインプレース更新を行うことができます。 SSDT では、.dacpac ファイルにエンコードされているスキーマの変更を指定できます。

Database型の Visual Studio プロジェクト コンテキストで .dacpac ファイルを操作します。

A.4 インメモリ OLTP 機能がアプリケーションに適しているかどうかに関するガイダンス

インメモリ OLTP 機能で特定のアプリケーションのパフォーマンスを改善できるかに関するガイダンスについては、次の情報を参照してください。

B. サポートされていない機能

特定のインメモリ OLTP シナリオでサポートされていない機能については、以下の情報を参照してください。

次のサブセクションでは、より重要ないくつかのサポートされていない機能に重点を置きます。

B.1 データベースのスナップショット

メモリ最適化テーブルまたはモジュールを特定のデータベースで初めて作成した場合、データベースの スナップショット を取得することはできません。 具体的な理由を以下に示します。

  • 最初のメモリ最適化項目の場合、メモリ最適化ファイル グループから最後のファイルを削除することはできません。
  • メモリ最適化ファイル グループのファイルがないデータベースではスナップショットをサポートできません。

通常、スナップショットは簡単なテスト イテレーションで役立ちます。

B.2 複数データベースにまたがるクエリ

メモリ最適化テーブルで 複数データベース にまたがるトランザクションはサポートされません。 メモリ最適化テーブルにもアクセスする同じトランザクションまたは同じクエリから別のデータベースにアクセスすることはできません。

テーブル変数はトランザクション処理されません。 したがって、複数データベースにまたがるクエリでは メモリ最適化テーブル変数 を使用できます。

B.3 READPAST テーブル ヒント

クエリでは、READPAST テーブル ヒント をどのメモリ最適化テーブルにも適用できません。

READPAST ヒントは、複数のセッションでそれぞれ、キューの処理時などに、同じ小さな行セットに対してアクセスおよび変更を行うシナリオで役立ちます。

B.4 RowVersion、Sequence

  • メモリ最適化テーブルでは、 RowVersion の列にタグを付けることはできません。

  • SEQUENCE はメモリ最適化テーブルの制約では使用できません。 たとえば、NEXT VALUE FOR 句では DEFAULT 制約を作成できません。 SEQUENCE は、INSERT および UPDATE ステートメントで使用できます。

C: 管理メンテナンス

このセクションでは、メモリ最適化テーブルが使用されるデータベース管理の違いについて説明します。

C.1 ID シードのリセット、増分値 > 1

IDENTITY 列を再シードするためにDBCC CHECKIDENTをメモリ最適化テーブルで使用することはできません。

メモリ最適化テーブルでは、IDENTITY 列に対する増分値はちょうど 1 に限定されます。

C.2 DBCC CHECKDB でメモリ最適化テーブルを検証することはできない

DBCC CHECKDB コマンドは、そのターゲットがメモリ最適化テーブルである場合、機能しません。 回避策として、次の手順を実行してください。

  1. トランザクション ログをバックアップします。

  2. メモリ最適化ファイル グループ内のファイルを null デバイスにバックアップします。 バックアップ プロセスでは、チェックサム検証を呼び出します。

    破損が見つかった場合は、次の手順に進みます。

  3. メモリ最適化テーブルからディスク ベース テーブルにデータをコピーし、一時的に保存します。

  4. メモリ最適化ファイル グループのファイルを復元します。

  5. ディスク ベース テーブルに一時的に保存したデータをメモリ最適化テーブルに挿入します。

  6. データを一時的に保持したディスク ベース テーブルを削除します。

D. パフォーマンス

このセクションでは、メモリ最適化テーブルの優れたパフォーマンスが最大限に生かされないままになる可能性のある状況について説明します。

D.1 インデックスに関する考慮事項

メモリ最適化テーブルのすべてのインデックスは、テーブル関連ステートメントの CREATE TABLE と ALTER TABLE で作成され、管理されます。 CREATE INDEX ステートメントではメモリ最適化テーブルを対象にすることはできません。

メモリ最大化テーブルを初めて実装する際に、多くの場合、従来の B ツリー非クラスター化インデックスを選択するのが賢明かつ簡単です。 後で、アプリケーションのパフォーマンスを確認してから、インデックスの別の種類への切り替えを検討できます。

Note

SQL Server のドキュメントでは、一般にインデックスに関して B ツリーという用語が使用されます。 行ストア インデックスで、SQL Server によって B+ ツリーが実装されます。 これは、列ストア インデックスやメモリ内データ ストアには適用されません。 詳細については、「SQL Server と Azure SQL のインデックスのアーキテクチャとデザイン ガイド」を参照してください。

特殊な 2 種類のインデックス (ハッシュ インデックスと列ストア インデックス) では、メモリ最適化テーブルのコンテキストでのディスカッションが必要になります。

メモリ最適化テーブルのインデックスの概要については、次の情報を参照してください。

ハッシュ インデックス

ハッシュ インデックスは、'=' 演算子を使用して正確な主キー値で 1 つの特定の行に最も速くアクセスできる形式です。

  • '!='、'>'、または 'BETWEEN' などの不正確な演算子をハッシュ インデックスで使用すると、パフォーマンスが損なわれます。

  • キー値の重複率が高くなる場合、ハッシュ インデックスは最良の選択とは言えないかもしれません。

  • 個々のバケット内のチェーンが長くならないように、ハッシュ インデックスで必要になる可能性がある バケット 数を低く見積もらないでください。 詳細については、次の情報を参照してください。

非クラスター化列ストア インデックス

メモリ最適化テーブルは、 オンライン トランザクション処理 (つまり、 OLTP) と呼ばれるパラダイムにおいて、一般的なビジネス トランザクション データの高スループットを提供します。 列ストア インデックスは、集計と同様の処理 ( 分析と呼ばれる) の高スループットを提供します。 OLTP と分析の両方のニーズを満たすために使用できるこれまでの最良の方法は、データの大量移動を行うテーブルと、ある程度のデータ重複を伴うテーブルを個別に使用する方法でした。 現在は、より簡単な ハイブリッド ソリューション を利用して、メモリ最適化テーブルに列ストア インデックスを作成することができます。

  • 列ストア インデックス は、クラスター化インデックスとしても、ディスク ベース テーブルに作成することができます。 ただし、メモリ最適化テーブルで列ストア インデックスをクラスター化することはできません。

  • メモリ最適化テーブルに LOB または行外列がある場合、テーブルに列ストア インデックスを作成できません。

  • テーブルに列ストア インデックスが存在する場合、メモリ最適化テーブルに対して ALTER TABLE ステートメントを実行することはできません。

    • 2016 年 8 月現在、Microsoft には列ストア インデックスの再作成パフォーマンスを改善するための短期的な計画があります。

D.2 LOB および行外列

ラージ オブジェクト (LOB) は varchar(max) などの型の列です。 メモリ最適化テーブルで LOB 列をいくつか使用しても、おそらく、パフォーマンスにはそれほど悪影響はありません。 ただし、データで必要以上の LOB 列は使用しないでください。 行外列の場合も同様です。 varchar(512) で十分な場合は、列を nvarchar(3072) として定義しないでください。

LOB および行外列の詳細については、次の情報を参照してください。

E. ネイティブ プロシージャの制限事項

Transact-SQL の特定の要素は、ネイティブ コンパイル T-SQL モジュール (ストアド プロシージャを含む) ではサポートされていません。 サポートされている機能の詳細については、次の記事を参照してください。

サポートされていない機能を使用する Transact-SQL モジュールがネイティブ コンパイルになるように移行する際の考慮事項については、次を参照してください。

Transact-SQL の特定の要素に対する制限に加えて、ネイティブ コンパイルされた T-SQL モジュールでサポートされているクエリ演算子にも制限があります。 このような制限事項があるために、ネイティブ コンパイル ストアド プロシージャは大きなデータ セットを処理する分析クエリに適していません。

ネイティブ プロシージャでは並列処理を使用できない

ネイティブ プロシージャのクエリ プランの一部として、並列処理を使用することはできません。 ネイティブ プロシージャは常にシングル スレッドです。

結合の種類

ハッシュ結合とマージ結合は両方とも、ネイティブ プロセッサのクエリ プランの一部として使用することはできません。 入れ子になったループ結合が使用されます。

ハッシュ集計を使用できない

ネイティブ プロシージャのクエリ プランで集計フェーズが必要な場合に使用できるのはストリーム集計のみです。 ネイティブ プロシージャのクエリ プランではハッシュ集計はサポートされていません。

  • 大量の行からデータを集計する必要がある場合、ハッシュ集計をお勧めします。

F. アプリケーション デザイン: トランザクションと再試行ロジック

メモリ最適化テーブルに関係するトランザクションは、同じテーブルに関係する別のトランザクションに依存する場合があります。 従属トランザクション数が許容最大値に達した場合、すべての従属トランザクションが失敗します。

SQL Server 2016 の場合

  • 従属トランザクションの許容最大値は 8 です。 8 は、指定されたトランザクションが依存できるトランザクションの制限でもあります。
  • エラー番号は 41839 です (SQL Server 2014 の場合、エラー番号は 41301 になります)。

スクリプトに 再試行ロジック を追加することで、Transact-SQL スクリプトを考えられるトランザクション エラーに対してより堅牢にすることができます。 再試行ロジックは、UPDATE や DELETE 呼び出しが頻繁に行われる場合や、メモリ最適化テーブルが別のテーブルの外部キーによって参照される場合に特に役立つことがあります。 詳細については、次の情報を参照してください。

関連するコンテンツ