SQL Server 2014 実践シリーズ （HTML 版）
「No.1 インメモリ OLTP 機能の実践的な利用方法」

1.12　弊社のお客様 A社の「ポイントカード システム」での検証結果

弊社のお客様「A社」の「ポイントカード システム」では、インメモリ OLTP 機能を採用することで、2.8倍の性能向上を実現できることを確認しました。

このお客様のポイントカード システムは、流通系企業でお馴染みの「ポイントカード」における、「ポイントの入金」や「出金」、「残高照会」などを行うのが主なトランザクション処理になります。

これまでの環境は、SQL Server 2005 Enterprise エディション X64版と Windows Server 2003 R2 X64版で構成されており、1秒あたり 1,743個の処理量でした（ポイント入出金や残高照会などのトランザクション処理を 1秒間に何回できるかの処理量）。

これを SQL Server 2014 Enterprise + Windows Server 2012 R2 環境へ移行して、インメモリ OLTP 機能を利用することで 1秒あたり 4,795個の処理ができることを確認できました（2.8倍の性能向上）。これは、1秒間の間に、4,795人のユーザーが同時にポイントカードを利用しても問題がない、という処理量になります（これまでのシステムでは、1秒間に 1,743人のユーザーが同時に処理を行おうとすると、遅延が発生する可能性がありましたが、対応可能なユーザー数が 2.8倍に増えました）。

今回の負荷テストは、本番のピーク時を想定しているので、1時間継続して負荷をかけ続けています。この 1時間で処理できた件数が次のとおりです。

インメモリ OLTP では、1秒あたり 4,795個の処理が可能で、1分では 28.7万、1時間では 1,726万もの処理を実行できることを確認できました。

◆ 検証の背景 ～将来のアクセス増への対応～

このお客様のポイントカード システムでは、利用者が日々増え続けており、このままでは将来のアクセス増に耐えられるか不安がありました。そこで、「SQL Server 2014 のインメモリ OLTP 機能を利用すれば、性能が上がりそうなので、実際にどのくらいの効果があるのか？ 将来のアクセス増に耐えられそうか？ 性能効果を調査したい」ということで、今回の検証を実施しました。また、今回は、ハードウェアをリプレイスすることなく、性能向上を実現したい、という希望もあったので、同じハードウェア スペックのマシンを利用して、検証を行っています。

◆ 処理の内容

このシステムでは、「ポイントの入金」や「出金」、「残高照会」が日中トランザクションの 9割以上の処理になり、各処理内ではステートメントが 12個実行されています。その内訳は、次のとおりです。

1回のポイント入金では、上記の 12個のステートメントが実行されて、Insert と Update ステートメントが 1/4 ずつ（3個ずつ）、Select ステートメントが 1/2（6個）の割合で実行されています。出金処理や、残高照会処理についても、ほぼ同様のステートメントが 12個ずつ実行されています。

◆ テーブル構成

このシステムでの主なテーブルは、次の 6つで、マスター系のテーブル（カードやカード種別、顧客、メッセージ）と、処理の履歴を格納するトランザクション テーブル、顧客の利用履歴を格納するテーブルなどがあります。

これらのテーブルは、データの永続化が必要になるので、インメモリ OLTP の SCHEMA_AND_DATA（Durable）で作成しています。また顧客履歴テーブルに関しては、SCHEMA_AND_DATA でデータを永続化しますが、Delayed Durability（遅延永続化）を利用可能だったので、これを採用しました。

◆ シングル実行でも性能向上（1.74倍の性能向上）

各処理を、多重実行ではなく、シングル実行（シングル スレッドで単体実行）したときの性能は、次のようになりました。

シングル実行でも、全体として 1.74倍の性能向上（1.3ミリ秒から 750マイクロ秒へ向上）することを確認できました。また、INSERT ステートメント 3個では 2.5倍、SELECT ステートメント 6個では 2.1倍、UPDATE ステートメント 3個では 1.2倍の性能向上を確認することができました。

このように、インメモリ OLTP 機能は、シングル実行でも性能向上を確認することができ、さらに多重度を上げた場合に大きな性能向上に繋がることが理解できました（前述の 2.8倍）。なお、各処理内で実行しているステートメントは、ネイティブ コンパイル ストアド プロシージャへ変換していますが、その詳細については、第2章で説明します。

◆ 多重実行時のオーバーヘッド（ラッチ待ち、ロック待ち、チェックポイントなど）

冒頭のグラフのように、本番を想定した 100多重での負荷テストでは、約 2.8倍の性能向上を確認しています。

このように、多重度を上げた場合に、シングル実行よりも性能差が大きく出る（シングル実行では 1.74倍だったところが、100多重では 2.8倍になった）のは、ディスク ベースのテーブルでは、「ラッチ待ち」や「ロック待ち」など、多重実行時のオーバーヘッドが非常に大きく、またチェックポイント時の書き込みの負荷がインメモリ OLTP よりも大きいことが、今回の負荷テストで確認することができたためです。

◆ パフォーマンス カウンターの様子（ラッチ待ちとバッチ要求数）

この 100多重での負荷テストを行ったときの様子をパフォーマンス モニター（データ コレクターで収集したパフォーマンス カウンター）で確認すると、次のようになります。

このグラフは、負荷テストを 1時間実施し続けているときの、その間の 15分～45分の 30分間を抜き出したものです。

グラフ内の赤色の折れ線がディスク ベースでのラッチ待ち（Latch Waits/sec）で、多発していることが分かります（1秒あたり 2,000～16,000 のラッチ待ちが発生）。

青色の折れ線が、ディスク ベースでの Batch Requests/sec（1秒あたりのバッチ要求数）で、1秒あたり 10,000～45,000ぐらいの要求数を処理していることが分かります。また、Batch Requests/sec が増えたタイミングで、ラッチ待ちも増加、減ったタイミングで、ラッチ待ちも減少する、といった具合に連動しています。

インメモリ OLTP では、緑色の折れ線が Batch Requests/sec で 50,000 近辺の値を安定して推移していることが分かります。黄色の折れ線がラッチ待ちで 0（ラッチ待ちなし）を推移しています。

また、このグラフの 30分間でのラッチ待ちとバッチ要求数の平均を計算すると、次のようになります。

ディスク ベースでは、ラッチ待ちが多発する（平均 7,331/sec）ことによって、1秒あたりのバッチ要求数が伸びていません（平均 23,945/sec）。これに対して、インメモリ OLTP では、バッチ要求数が安定して高い値を推移しています（平均 51,707/sec）。

◆ ラッチ待ちによるスループットの低下

前述したように、ラッチ待ちが多発している場合には、スループットが低下します。

ラッチ待ちが発生する場合には、ユーザー数が増えれば増えるほど、スループットが低下していきます（同時実行数が増えるほど、システムの処理能力が頭打ちになります）。

これに対して、インメモリ OLTP が採用している、ラッチを利用しない「ラッチ フリー」のアーキテクチャであれば、ユーザー数が増えても性能低下は発生しにくくなります。

今回は、本番を想定した 100多重でのテストでしたが、インメモリ OLTP 機能を利用すれば、今後同時接続ユーザー数が増えた場合にも、有効なアーキテクチャになっています。

◆ ラッチ待ちの割合を調べる ～Latch Waits/sec～

パフォーマンス モニターの Latch Waits/sec カウンターを利用するときのポイントは、この値が、バッチ要求数（Batch Requests/sec）と比較してどれぐらいなのかを見ることです。これで、ラッチ待ちの頻度を推測することができます。今回の結果では、ラッチ待ちが 7,331/sec に対して、バッチ要求数が 23,945/sec なので、ラッチ待ちの割合は約 30％になりますが、このシステムでは、前述したように、処理のうち SELECT が 50％、INSERT が 25％、UPDATE が 25％で実行されているので、更新系でラッチ待ちが発生しているのではないか、と推測できます。

◆ ラッチ待ちの詳細を調べるには ～Wait Stats、Index Stats～

ラッチ待ちの詳細調査には、dm_os_wait_stats と dm_db_index_operational_stats 動的管理ビューが役立ちます。

dm_os_wait_stats は、SQL Server に関する内部的な "待機" の情報を取得することができる便利なビューです。SQL Server が起動してから、現在までの累積値を参照することができ、この結果は、Wait Stats とも呼ばれています（Wait：待機 の Statistics：統計）。

このビューでは、さまざまな待機の種類を参照することができますが、次のように wait_type（待機の種類）で LATCH という文字列を含んだもののみに絞り込むことで、ラッチ待ちがどれぐらい発生していたかを確認することができます。

ORDER BY 句で waiting_tasks_count が大きい順に並べ替えることで、待機の発生した回数の多い順に並べ替えて表示、wait_time_ms は、該当する待機（wait_type）によって、どれぐらいの時間の待機時間（ミリ秒単位）が発生していたのかを確認することができます。

上の画面は、ディスク ベースで 100多重で 1時間実行した後に取得したときのもので、排他ページ ラッチ（PAGELATCH_EX）や共有ページ ラッチ（PAGELATCH_SH）が上位に表示されることから、ページ ラッチ待ちが多発していることが分かります。

dm_os_wait_stats ビューの結果は、あくまでも SQL Server を起動してから、現在までの累積値であることを忘れてはいけません。上の画面は、多重テストの実行前に SQL Server を再起動しているので、多重テストで発生したラッチ待ちを確認することができていますが、通常は、調べたい操作の前（多重テストの前など）に、ビューの結果を取得しておき、多重テスト後に、もう一度ビューの結果を取得して、その2つの結果の差分を参照する、という使い方をします。

◆ Index ごとのラッチ待ちを確認可能 ～dm_db_index_operational_stats～

dm_os_wait_stats ビューでは、SQL Server 全体としての待機に関する情報でしたが、dm_db_index_operational_stats 動的管理ビューを利用すれば、インデックスごとのラッチ待ち（やロック待ち）に関する情報を参照することができます。

これは、次のように利用できます。

dm_db_index_operational_stats ビューの第1引数には DB_ID() を指定して、現在接続中のデータベースを指定し、第2～第4引数へ NULL を指定することで、データベース内のすべてのテーブルの、すべてのインデックスを参照できるようになります（第2引数はオブジェクトID、第3引数はインデックスID、第4引数はパーティション番号を指定することで、特定のオブジェクトの特定のインデックスのみを参照することも可能）。

page_latch_wait_count と page_latch_wait_in_ms で、インデックスごとのページ ラッチ待ちの発生回数と待機時間の合計（SQL Server が起動してからの累積値）、page_lock_wait_count と page_lock_wait_in_ms で、ロック待ちの発生回数と待機時間の合計を確認することができます。

上の画面は、ディスク ベースで 100多重で 1時間実行した後に取得したときのもので、顧客利用履歴テーブルの index_id = 1 とトランザクション テーブルの index_id = 1 で多くのページ ラッチ待ちが発生していることを確認できます。index_id に対応したインデックスの名前を確認するには、sys.indexes システム ビューを利用しますが、index_id = 1 はクラスター化インデックスに割り当てられている番号と決まっています。

詳しくは後述しますが、顧客利用履歴テーブルとトランザクション テーブルのクラスター化インデックスは、どちらも IDENTITY(1,1) へ設定した PRIMARY KEY（主キー）列になっていて、多数のユーザーが同時にデータ追加を行っていることで、次のようにインデックスの最終ページへのアクセスが集中している状態です。

連番系の列（IDENTITY を設定した列や、シーケンスを設定した列）など、データを追加するたびに連続した値が格納されていくような場合には、このようなインデックスの最終ページでページ ラッチ待ちが多発する（最終ページがホット スポットになる）ことがよくあります。これは、シングル実行（1人のユーザーによる単体実行）では、発生しないものですが、多数のユーザーが同時にデータを追加する場合には発生し得ます。

今回のシステムでは、本番を想定した 100多重でのテストを行っていますが、このように多重度が高いとページ ラッチ待ちが多発することになります。改めて、以下のパフォーマンス カウンターの様子を見てみてください。

ディスク ベースでは、ラッチ待ちが多発することで、Batch Requests/sec（1秒あたりのバッチ要求数）がインメモリ OLTP よりも低くなっています。インメモリ OLTP では、Max 6万ぐらいまで出るのに対して、ディスク ベースでは Max 4.8万ぐらいまでしか出ていません。

ディスク ベースでの Batch Requests/sec の値が安定していない（ガクンと定期的に下がる）理由については、別の理由があるので（ディスクへの書き込み待ちなど）、以降では、それを見ていきます。

◆ Processor Time（CPU 利用率）と Disk Queue（ディスク待ち）

多重テスト時の %Proccessor Time と Disk Write Queue Length（ディスクへの書き込み待ちの、待ち行列の長さ）は、次のようになりました。

グラフ内の青色の折れ線は、インメモリ OLTP での CPU 利用率で、70～80% ぐらいを安定して推移し（平均は 71.7%）、黄色の折れ線が Disk Write Queue Length でほとんどキューが溜まっていないことが分かります（平均 2.2。第2軸）

これに対して、グラフ内の緑色の折れ線がディスク ベースでの CPU 利用率で、定期的にガクンと下がるタイミングがあって安定していないことが分かります（平均すると、CPU が 38.5% しか活用されていません）。また、赤色の折れ線が Disk Write Queue Length で、CPU 利用率がガクンと下がったタイミングで、キュー（ディスク待ち）がいっきに 50以上に増えてしていることが分かります（平均 18。第2軸）。このグラフには、Batch Requests/sec を含めていませんが、この値が安定しなかったのも同じ理由で、Batch Requests/sec がガクンと下がるタイミングで、ディスク待ちが発生して（キューが溜まって）いました。

ディスクへの書き込み待ちが発生することによって、他のリソース（CPU など）は余力を残してしまう、というのは典型的なボトルネックの例です（ディスク ボトルネックが原因で、CPU がフル活用されていない状態です）。ディスクへの書き込み待ちが発生している理由は、チェックポイント処理によるものですが、以降では、これについて詳しく見ていきます。

◆ チェックポイント処理の負荷 ～復旧間隔、Checkpoint pages/sec～

チェックポイント処理は、メモリ内のデータ バッファの内容を、データ ファイル（.mdf）へフラッシュする（書き込む）処理です。これを定期的に行っておくことで、万が一の障害発生時に、復旧時間を短くできるというメリットがあります。チェックポイントが発生するタイミングは、［復旧間隔］（recovery interval）で設定することができ、これは、次のようにサーバーのプロパティの［データベースの設定］ページから設定できます。

［復旧間隔］は、既定では「0」に設定されていて、この場合は「1」分に設定した場合と同等の動作になって、復旧時間が 1分程度で済むようにチェックポイントを発生させる、という設定値になります。この場合、データ更新が 1分間ずっと続いているような状態であれば、約1分でチェックポイントが発生して、データ更新がほとんど行われない状態であればチェックポイントはほとんど行われません。

今回の負荷テストでは、処理のうち SELECTが 50％、INSERTが 25％、UPDATEが 25％の割合で、データ更新（INSERT／UPDATE）の割合が 50％ で実行されているので、1分 20秒～1分40秒ぐらいのタイミングで、チェックポイントが定期的に実行されています（後半になって、処理できる量が減っていくと、チェックポイントのタイミングも遅くなっていきます）。

チェックポイントが発生したかどうかは、Checkpoint pages/sec（1秒あたりにチェックポイントで処理したページ数）カウンターで、次のように調べることができます。

Batch Requests/sec がガクンと下がるタイミングで、チェックポイント処理が行われて（データファイルへの書き込みが発生して）、このときにログ書き込みとチェックポイント処理が競合することによって、ディスクへの書き込み待ちが多数発生してしまっています。また、後半になればなるほど、データ量が増えていくので、チェックポイントにかかる時間も延びていっています。

◆ インメモリ OLTP ではチェックポイントの負荷が軽い

今回の負荷テストでは、ディスク ベースでは、チェックポイント処理が大きなボトルネックとなっていますが、インメモリ OLTP 機能の場合は、チェックポイント処理の負荷が軽いことを確認することができました。

また、インメモリ OLTP 機能では、マルチバージョンの楽観的同時実行制御を実現するために、利用しなくなった古いバージョンのデータを定期的に削除するために「ガベージ コレクション」（GC：Garbage Collection）が動作するのですが、これもディスク ベースでのチェックポイント処理ほどの負荷にはならないことを確認できました。

インメモリ OLTP 機能では、チェックポイント処理が発生したかどうかは「XTP Checkpoint Completed」カウンター、ガベージ コレクションが発生したかどうかは「XTP Main GC work items/sec」カウンターで調べることができます。これを取得したときの様子が次のグラフです。

青の折れ線がチェックポイントの完了（完了ごとに１ずつインクリメントされる）で、30秒～1分ごとぐらいに定期的に完了していて、30分間で 41回完了しています。赤の折れ線がガベージ コレクションによって 1秒間に処理されたアイテム数で、30分間で 27回実行されています（1回の GC で 20万～35万ぐらいのアイテムが処理されています。第2軸）。

インメモリ OLTP 機能では、チェックポイントは、ログ サイズが 512MB 増えるたびに実行され、ガベージ コレクションは、1分に 1回実行されるアーキテクチャになっています。詳しくは、オンライン ブックの以下のトピックが参考になると思います。

メモリ最適化テーブルのチェックポイント操作
http://msdn.microsoft.com/ja-jp/library/dn553124.aspx

インメモリ OLTP ガベージ コレクション
http://msdn.microsoft.com/ja-jp/library/dn643768.aspx

◆ ガベージ コレクションの影響

ガベージ コレクションと Batch Requests/sec をグラフにしてみると、次のようになります。

Batch Requests/sec が少し下がったタイミングで、ガベージ コレクションが実行されていることが分かると思います。ディスク ベースでは、チェックポイントのタイミングで Batch Requests/sec がガクンと下がりましたが（5,000～10,000 ぐらいまで下がる）、インメモリ OLTP では、ガベージ コレクションが実行されても 30,000 ぐらいの Batch Requests/sec を処理することができ、安定したパフォーマンスを実現できることを確認することができました。

このように、インメモリ OLTP では、チェックポイント処理の負荷は非常に軽く、ガベージ コレクションに関しては、オーバーヘッドがある、ということが分かりました。チェックポイント処理のアーキテクチャについては、冒頭で紹介した以下のドキュメントが参考になると思います。

SQL Server In-Memory OLTP Internals Overview
http://download.microsoft.com/download/5/F/8/5F8D223F-E08B-41CC-8CE5-95B79908A872/SQL_Server_2014_In-Memory_OLTP_TDM_White_Paper.pdf

◆ ディスク ベースでは断片化も発生する

ディスク ベースのインデックス（b-tree 構造）では、データを追加していけばいくほど、断片化が発生していき、性能低下に繋がります。断片化は、dm_db_index_physical_stats 動的管理ビューを利用して確認することができます。

これは、次のように利用できます。

dm_db_index_physical_stats ビューの第1引数には DB_ID() を指定して、現在接続中のデータベースを指定し、第2～第4引数へ NULL を指定することで、データベース内のすべてのテーブルの、すべてのインデックスを参照できるようになります（第2引数はオブジェクトID、第3引数はインデックスID、第4引数はパーティション番号を指定することで、特定のオブジェクトの特定のインデックスのみを参照することも可能）。第5引数で LIMITED を指定することで、断片化の度合いを簡易チェックすることができます（DETAILED を指定することで詳細チェックも可能）。

avg_fragmentation_in_percent で断片化の割合、page_count で、インデックスの使用ページ数を確認することができます。上の画面は、ディスク ベースで 100多重で 1時間実行した後に取得したときのもので、トランザクション テーブルの index_id = 2と3、顧客利用履歴テーブルの index_id = 2 で大きな断片化が発生していることが分かります。

このような断片化は、速度低下を引き起こすので、これを解消するためには、インデックスの再構築（ReBuild）または再構成（ReOrganize）をしなければなりません（このお客様では、1日１回、夜中に再構成を実施しています）。この処理は、インデックス サイズが大きい場合には、非常に時間がかかるので、その間のサーバー処理能力が低下するという問題があります。また、再構築／再構成の処理の履歴はトランザクション ログへ書き込まれるので、データベース ミラーリングや、AlwaysOn 可用性グループを利用している場合には、その処理の履歴をミラー サーバーへ転送する負荷もかかってしまうという問題もあります。

インメモリ OLTP 機能では、ハッシュ インデックスがサポートされたことで、断片化に悩まされることがなくなります（再構築や再構成は必要ありません）。また、インデックスに対する更新情報は、トランザクション ログへ記録しないアーキテクチャを採用しているので、ログの書き込み量を削減できるメリットもあります。

◆ ログ書き込み量の削減 ～インデックスに関してはログ記録しない～

今回のシステムでは、SCHEMA_AND_DATA（データの永続化有り）オプションを利用しているので、ログへの書き込みを行っていますが、インデックスに対する更新情報は、トランザクション ログへ記録しないので、ログ書き込み量が少なくなっています。

1時間の負荷テストを実行した後のログ書き込み量は、次のようになりました。

インメモリ OLTP 機能では、倍以上の処理を行っているのに対して、ログの使用量は倍にはなっていません。1件あたりのログ使用量を計算すると、45% も削減できていることを確認できます。このシステムでは、トランザクション テーブルへ ２つ、顧客利用履歴テーブルへ 1つのハッシュ インデックスを追加で作成していて（PRIMARY KEY のハッシュ インデックスとは別に作成）、1回の処理で、前者へは 1回の INSERT、後者へは 2回の INSERT を行っているので、その分のログ出力量の差が、このような大きな差となって出ています。

◆ まとめ、今後の展望

このように、ディスク ベースのテーブルでは、ラッチ待ちやロック待ちなど、多重実行時のオーバーヘッドが非常に大きく、またチェックポイント時の書き込みの負荷や、ログ書き込みの負荷、断片化の発生などもインメモリ OLTP よりも大きいことが、今回の負荷テストで確認することができました。

今回は、できる限りハードウェアをリプレイスすることなく、SQL Server 2014 へアップグレードするだけで、性能向上できないか、という当初のもくろみがありましたが、期待以上の性能向上を確認することができました。また、ハードウェアをより良いものへ変更することで、さらなる性能向上を期待できることも分かったので、最新のハードウェアを導入すれば、4～5年後のアクセス増にも耐えられるのではないか、という期待も出てきました。

◆ インメモリ OLTP 実装のポイント（次の章で説明）

このシステムでは、次のことを実践していますが、詳しくは次章以降で説明します。

• ネイティブ コンパイル ストアド プロシージャの作成して性能向上を実現
• ネイティブ コンパイル ストアド プロシージャを作成しないと速くならなかったのか？
• インメモリ化で逆に遅くなってしまった SELECT ステートメントへの対処方法
• 一部の処理で Delayed Durability を利用して大幅な性能向上を実現
• ハッシュ インデックスと bw-tree インデックスの比較
• データ型でハマったこと、データ型の違いによるメモリ使用量の差
• 実行方法の違いによる性能差 など