ホーム  >  SQLServer / データベース

わかりやすいSQL Serverの内部構造 メモリ管理解説（第2章）

ちょっと、古い内容になりますが、SQL Serverのバッファキャッシュ領域、MemToLeave領域、バッファキャッシュのメンテナンス、メモリリソースの割り振りやメモリに関連した内部コンポーネントの詳細、メモリ使用状況のモニタリングについて記載しています。

SQLServerは自分自身の仮想アドレス空間を効率的に使用するために、用途ごとに様々なレベルで領域を使い分けています。その領域の最も上位レベルの使い分けは「バッファキャッシュ領域」と「MemToLeave領域」です。

目次
  1. バッファキャッシュ領域
  2. MemToLeave領域
  3. バッファキャッシュのメンテナンス
  4. モニタリング
  5. パフォーマンスモニタ
  6. 動的管理ビュー

バッファキャッシュ領域

バッファキャッシュ領域は、通常、SQLServerへ割り当てているメモリのサイズとしてユーザーが認識している領域です。そのサイズは「max server memory」と「min server memory」の間で推移し、その領域はまず「Reserved」の状態で確保され、実際に必要になった時に「Committed」として物理メモリ上にマップされます。その主たる用途は、物理ディスク上のデータファイルから読み込んだデータをキャッシュすることです。その他の用途としては、クエリのプランを格納するための領域、クエリの処理を行うワークスペースなどが挙げられます。また、バッファキャッシュは8ＫＢごとに区切って使用され、各8KBメモリブロックは「ページ」と呼ばれます。（図1）

図1

図1

データキャッシュ

データキャッシュは、一般的にはバッファキャッシュの最も多くを占める領域で、データベースのデータページやインデックスページをディスクから読み込んでキャッシュするために使用されます。SQLServerは必ず、いったんデータキャッシュに読み込んだデータを使用しますが、必要なデータを取得するために毎回データキャッシュ全体をスキャンしていて、いかにアクセス速度が高速なメモリ上の操作と言え大変なオーバーヘッドになります。そのため、SQLServerはデータキャッシュへデータページを配置する際にハッシュアルゴリズムを使用します。各データページが持つ固有の値を基にハッシュ値が生成されて、「ハッシュバケット」と呼ばれるページに格納されます。ハッシュ値はデータキャッシュ上のページへのポインタとともに格納されます。そのためハッシュバケット内のハッシュ値にアクセスすると、実際のページの位置が確認できます。これによって少ないアクセス回数で、目的とするデータキャッシュ上のデータにアクセスすることができます。

プロシージャキャッシュ

クライアントが実行したクエリは、SQLServer内で最適な形で処理されるようにコンパイルされ、クエリプランに変換されます。このコンパイル時間のオーバーヘッドを緩和させるためのアーキテクチャがプロシージャキャッシュです。一度コンパイルされたクエリプランは、同じクエリが再度実行されたときに備えてプロシージャキャッシュに保存されます。２回目の実行時にクエリは再度コンパイルされることはなく、プロシージャキャッシュ上のクエリプランが再利用されます。その結果としてコンパイル時間分のパフォーマンスが向上することになります。（図2）

クエリの初回実行時にコンパイルが行われる

図2

図2

クエリの２回目以降の実行時にはコンパイルされない

図2

クエリワークスペース

クエリワークスペースは、各クエリが結果セットの並べ替えなどを行う必要がある場合に使用されます。一定サイズ以上のデータの並べ替えが必要となった場合は、クエリワークスペースではなくtempdbデータベースが使用されることもあります。

最適化

クライアントが要求したクエリを実行するために、SQLServerはクエリのコンパイルを行います。その際にもバッファキャッシュ上のメモリが使用されます。

グローバル

ロック用メモリ、レプリケーション作業用メモリ、クエリテキスト格納用メモリなどは、グローバル領域で管理します。

プロシージャキャッシュの注意点

とても有効なプロシージャキャッシュですが、実は注意点もあります。アドホッククエリに関しては完全に一致しないとクエリプランは再利用されません。以下のクエリは一致している見なされません。

select col1 from tab1 where col2 = 1
select col1 from tab1 where col2 = 2

where区に指定されている条件が異なるためです。そのため、プロシージャキャッシュにはそれぞれのクエリプランが格納され、完全に一致するクエリが実行されない限り、再利用することはありません。

また、アドホッククエリのwhere句に指定される値のバリエーションが大量にある場合を考えてみましょう。次に示す例は少し極端ですが、1億通りものアドホッククエリが実行されたとします。そのため、1億通りものクエリプランがプロシージャキャッシュに格納され、再利用を待っています。

select col1 from tab1 where col2 = 1
select col1 from tab1 where col2 = 2
・
・
・
・
select col1 from tab1 where col2 = 9999999
select col1 from tab1 where col2 = 10000000

これは、どのような点で問題なのでしょうか？多数のクエリプランをプロシージャキャッシュに保存しておくには、それぞれのクエリ用のメモリブロックを用意する必要があります。そのため、メモリブロックは、バッファキャッシュ内から用意されます。しかし、バッファキャッシュには他にも数多くの用途があり、ディスクから読み込まれたデータを格納するという重要な役割もあります。あまりに多くのクエリプランがプロシージャキャッシュに格納されると、データを格納すべき領域が少なく場合があります。その結果として、ディスクI/Oが頻発してパフォーマンスへ悪影響を与えることが考えられます。

では、どのような対処が望ましいのでしょうか。プロシージャキャッシュを効果的に使用するためには「パラメータ化クエリ」という手段が用意されています。アドホッククエリの一部をパラメータに置き換えることによって、より汎用性の高いクエリプランが作成されます。前述のアドホッククエリをパラメータ化クエリに書き換えると、次のようになります。

execute sp_executesql N' select coll from tab1 where col2 = @parm',
 N' @parm int',
 N' @parm = 1;

sp_executesqlシステムストアドプロシージャを使用し、クエリ自体をパラメータとして渡します。また、where句に指定していた条件は@parm変数に指定します。クエリをこのように書き換えることによって、1億回コンパイルされていたクエリは1回のコンパイルで済むようになります。そのためCPU使用時間の点からパフォーマンスの向上が望めます。また、1億通りのクエリプランを格納してプロシージャキャッシュには、たった1個のクエリプランが保持されるだけです。そのため、メモリの有効活用という点でとても効果があることがわかるかと思います。

MemToLeave領域

SQLServerが使用する、もう一方のメモリ領域はMemToLeave領域と呼ばれています。この領域には様々な名称で呼ばれることがありますが（Multi Page Allocator、MemToReserve、MemoryToReserveなど）、このサイトではMemToLeaveに統一します。バッファキャッシュ領域のアクセス単位が8KBであることは、すでに紹介しました。多くの場合は、バッファキャッシュ内の8KBのメモリブロックを使用して処理が行われます。また、SQLServerのデータベースの基本的なアクセス単位も8KBです。双方のサイズが一致しているため、ディスから読み込まれたデータページはバッファキャッシュ上のメモリブロックに収まります。また、それ以外の既出の用途に関しても、大半の場合は8KBのメモリブロックを使用することで問題なく処理することができます。しかしながら、どうしても8KBのメモリブロックでは処理できない場合があります。次に示すいくつかの種類のオブジェクトをメモリ上に格納するためには、どうしても8KBよりも大きなサイズのメモリが必要になります。そのような場合に対処するためにMemToLeave領域が使用されます。これから、「8KBより大きなメモリブロックを使用する」点がMemToLeave領域の最初の特徴と言えます。

  • SQLCLR
  • 拡張ストアプロシージャ
  • スレッドスタック

2番目の特徴は、獲得したメモリの解放のタイミングです。 バッファキャッシュ領域は、いったん獲得したメモリは物理メモリがおおよそ5MB以下になるまで解放 しません。しかし、MemToLeave領域は不要になった領域を随時解放します。そ して、3つ目の特徴は、MemToLeaveの最大サイズは固定されているという点です。 デフォルトのMemToLeave領域の最大サイズは256MBに設定されています。
これら3つの特徴から、 大きなサイズのメモリを消費してしまう可能性のあるオ ブジェクトを、一定量に限定したサイズのメモリであるMem ToLeave領域に配置 すれば、管理しようとしている動作仕様が確認できます。 この動作仕様は、ディス クから読み込んだデータを大量に格納する必要があるバッファキャッシュ内のメモ リブロックが、大量に消費されるのを防ぐことを目的の1つとしています。
また、使用を終えた Mem ToLeave領域のメモリブロックをWindowsオペレーテ ィングシステムへと解放する動作は、大きなサイズのメモリブロックを長期間保持 するのではなく、ほかのアプリケーションに明け渡すため、限りあるメモリリソー スの有効活用へとつながります （図3）

図3

１，8KBのメモリブロックに収まらない処理が実行される

図3

２，MemToLeave内に展開されて処理される

図3

３，処理完了後はMemToLeaveのサイズは元に戻る

図3

バッファキャッシュのメンテナンス

ほとんどの場合、 仮想アドレス空間や物理メモリのサイズ的な制約で、 SQLServerが使用するディスク上のすべてのオブジェクトをバッファキャッシュ上に展開することはできません。そのため、一定数のオブジェクトをメモリに展開してバ ッファキャッシュに空きがなくなった後は、当然ながら、それ以降にメモリに展開 する必要があるオブジェクトのために空き領域を用意しなければなりません。ここでは、バッファキャッシュを効率的に使用するために動作しているSQL Serverの 内部プロセスについて紹介します。

バッファマネージャとメモリマネージャ

SQL Serverのメモリ管理用コンポーネントとして、バッファマネージャとメモ リマネージャが存在します。外部から見た場合のSQL Serverのメモリリソースの管理はバッファマネージャが行なっています。 具体的に は物理メモリの獲得や解放、データキャッシュの維持管理といった作業はバッファマネージャが担当します。
では、メモリマネージャというコンポーネントは何を行なっているかと言うと、 データキャッシュ以外の用途(プロシージャキャッシュやワークスペースなど)の すべてのメモリの獲得および解放です。 バッファマネージャのメモリの獲得先は物 理メモリであるのに対し、メモリマネージャはバッファキャッシュから必要なメモ リを獲得してきます。
その両者のメモリ獲得動作の違いが面白い形で表われている箇所があります。 バ フォーマンスカウンタのSQL Server: Buffer Managerオブジェクトを確認する と、バッファマネージャの動作を確認するためのさまざまなカウンタが確認できま す。ここで注目してもらいたいのが「Stolen Pages カウンタ」 です。 突然 「Stolen」 などという穏やかではない名称が出てきますが、 「誰」 から 「Stolen」 されたのかが明記されていませんね。鋭い方ならもうお分かりかと思いますが、 「Steal」していったのはメモリマネージャなのです (図A)。

目mロ位マネージャはプロシージャキャッシュなどで使用するメモリ獲得のため、バッファマネージャからStealする

図A

図A

バッファマネージャからすると、 「せっかく自分が必要なサイズを物理メモリか ら獲得してきたのに、メモリマネージャから自分の必要とする分を “Steal” された。という認識です。

レイジーライタスレッド

レイジーライタの役割は、しきい値より多い数のフリーページを常にバッファキャッシュ内に確保しておくことです。しきい値はバッファキャッシュのサイズなどを考慮して SQL Serverによって動的に設定されます。また、フリーページとは、ディスクから読み込んだデータを格納することができる、バッファキャッシュ上の空きページ です。では、一定数のフリーページを確保するためのアルゴリズムを確認してみま しょう。

❶レイジーライタは定期的 (通常は1秒間隔だが、フリーページが少ない状況が続 く場合は頻度が上がる) に次の点を確認する

●Windows オペレーティングシステムのメモリ使用状況
●バッファキャッシュとしてコミットされているサイズ
●フリーページの数

❷フリーページがしきい値を下回っている際に、 もしバッファキャッシュのサイ ズが設定された最大値 (max server memory) よりも小さい (リザーブされているがコミットされていない) 場合は、 新たなメモリ領域をコミットしてバ ッファキャッシュにページを追加する。 追加されたページがフリーページとして認識される (図4)

図4

１，レイジーライタがフリーページ数の不足を検知

図4

２，新たなメモリ領域をCommitしてフリーページを確保

図4

➌バッファキャッシュのサイズがすでに最大値に達していて、さらにフリージがしきい値を下回っている場合、レイジーライタはフリーページを確保する ためにバッファキャッシュ内のページの状況確認を開始する
❹レイジーライタは主として以下のようなページを解放してフリーページにする
●ラッチされていないページ:ラッチされているページは、その時点で何らかの アクセスが行なわれているため、解放できない

●ダーティではないページ: ダーティベージとは、バッファキャッシュ内のペー ジの内容は変更されたが、まだディスクに変更内容が反映されていない状態を 指す。 レイジーライタはダーティページのディスクへの書き込み要求を行ない、 書き込みが完了してダーティではなくなった時点でページは解放される

●一定期間参照されていないページ: 各ページは参照カウントを持っていて、 そ のページが参照されないまま時間が経過すると参照カウントは減っていく。 参照カウント数が一定値を下回ると「一定期間参照されていない」と判断され、そのページは解放される

チェックポイントプロセス

定期的なチェックポイントプロセスの役割は、 一定間隔ごとにバッファキャッシ上のダーティページ (バッファキャッシュに読み込まれた後に変更された内容がディスク上に反映されていないページ)を、ディスク上の各データベースの物理データファイルに書き込むことです。 なお、 alter database 実行時やSQL Serverを終了させる場合、または直接checkpointコマンドを実行した際などにもチェックポイントは実行されます (図5)。

図5

１，データキャッシュ上のダーティページをディスクにフラッシュ

図5

２，フラッシュ後はデータキャッシュ上のページはクリーンとなる

図5

チェックポイントプロセスは、ダーティページをディスクにフラッシュするだけで、特にフリーリストにページを追加するといった作業を行ないません。 そのため、直接的にバッファキャッシュ上の空きページを増加させるわけではありません。しかし、バッファキャッシュ上のページと物理データファイルの内容を一致させることにより、ページを「クリーン」 な状態に戻します。その結果として、後でレイジーライタがバッファキャッシュの空きを確保する際に、ディスクへのフラッシュ数が減少し、処理の負荷を軽減させることにつながります。

モニタリング

次に、SQL Serverのメモリ使用状況の確認に主眼をおいて、モニタリング手法を紹介します。

dbccコマンド

次のdbccコマンドをクエリツール (sqlcmd osql、 SQL Server Management Studioなど) で実行すると、コマンド実行時にSQL Serverが使用していたメモリの 状態のスナップショットが出力されます。

dbcc memorystatus
go


個々の出力項目の中には、ほかの手段 (後述するパフォーマンスモニタや動的管 理ビュー) でも確認できるものもあります。しかし、このコマンドの出力では、 多岐にわたる情報が一度に一覧表示の形式で入手できるため、トラブルシューティン グやパフォーマンスチューニングの初期段階で、メモリ使用状況を俯瞰するのに最も適している情報と言えます。
また、定期的(1分間隔など)にこのコマンドを実行することによって、ある程 度の継続性を持たせた情報としても利用することができます。 出力される内容は (あまりに)多岐にわたるため、ここでは主な情報に絞って紹介します。 詳細な各 項目の解説は、Microsoftサポートオンラインで確認できます。
なお、このコマンドはSQL Server 7.0以降で実行できますが、バージョンによって出力内容に違いがあります (やはり新しいバージョンのほうが、より多くの情報 を得られる傾向があります)。出力結果は以下のとおりです。

「Memory Manager」 セクション


まず最初に、 SQL Serverインスタンス全体のメモリ使用状況が出力されます 間内のメモリのサイズが示されます。 同様に 「VM Committed」 はCommittedという (LIST1)。 「VM Reserved」 にはReserved という状態で確保している仮想アドレス空 状態で確保しているメモリのサイズです。Reservedおよび Committed状態に関しては前章で詳しく紹介したので、ここでは割愛します。 ちなみに、コマンドのサンプル出力は1GB RAMを搭載したコンピュータでSQL Server 起動直後に収集しました。 SQL Serverのメモリは動的管理(特にmax server memory も min server memoryも指定していない)にしてあります。「VM Reserved」のサイズに注目し てもらいたいのですが、 物理メモリのサイズとほぼ同じ約1GBがReservedになっていることが確認できます。 つまりSQL Serverは、必要があり、かつ可能であれば、ほとんどすべての物理メモリを使用しようとしていることが読み取れます。
LIST1 「Memory Manager」 セクション

Memory Manager                 KB
---------------------------    -------------
VM Reserved                    1051248
VM Committed                   26512
AWE Allocated                  0
Reserved Memory                1024
Reserved Memory In Use         0

「Memory node Id = X」 セクション

各NUMAノードごとのメモリ使用状況が出力されます (LIST2)。 NUMAノード (ソフトNUMAノードも含みます) が複数存在すると、 出力結果も同じ数だけ存在します。 各NUMAノードの各項目の使用量を合計したものが、前述の「Memory Manager」セクションの各項目と一致します。
LIST2: 「Memory node Id = X」 セクション

Memory node Id = 0          KB
--------------------------  -------------------
VM Reserved                 1047088
VM Committed                22504
AWE Allocated               0
MultiPage Allocator         7808
SinglePage Allocator        4488

[MEMORYCLERK_XXXX. CACHESTORE_XXX.USERSTORE_XXX. OBJECTSTORE_XXX、USERSTORE_XXX、OBJECTSTORE_XXX」セクション

メモリの各用途ごとに使用状況が出力されます (LIST3)。

LIST3: [MEMORYCLERK XXXX, CACHESTORE XXX, USERSTORE XXX. OBJECTSTORE XXX

OBJECTSTORE LOCK MANAGER (Total)　 KB
---------------------------------  ----------------
VM Reserved                        12
VM Committed                       12
AWE Allocated                      0
SM Reserved                        0
SM Committed                       0
SinglePage Allocator               28
MultiPage Allocator                0

「Buffer Distribution」セクション

バッファキャッシュ内でCommitted状態にある領域の使用状況の内訳が表示されます

LIST4: [Buffer Distribution] セクション

Buffer Distribution　     Buffers
------------------------  --------------
Stolen                    267
Free                      166
Cached                    356
Database (clean)          1059
Database (dirty)          40
1/0                       0
Latched                   0

[Buffer Counts] セクション

仮想アドレス空間内でのバッファキャッシュの使用状況の内訳が表示されます。(LIST5)。このセクションで表示される 「Committed」の領域のサイズは、前述の「Buffer Distribution」セクションのすべての項目の合計値とほぼ一致します。

LISTS: [Buffer Counts] セクション

Buffer Counts                    Buffers
------------------------------   ----------------------
Committed                        1888
Target                           8882
Hashed                           1099
Stolen Potential                 121395
External Reservation             0
Min Free                         32
Visible                          8882
Available Paging File            184077

「Procedure Cache」 セクション

プロシージャキャッシュとして使用されている領域に関する情報が出力されます (LIST6)。 このセクションの「TotalPages」の値が、 前述の 「Buffer Counts」 セクションの 「Target」 の値の30%を超えているようであれば、 プロシージャキャッシの圧迫が発生している可能性があります。

LIST6 : 「Procedure Cache」 セクション

Procedure Cache         Value
----------------------  ----------------
TotalProcs              15
TotalPages              131
InUsePages              8

パフォーマンスモニタ

Windowsオペレーティングシステムに付属しているパフォーマンスモニタにも、 メモリの状況を確認するために有用な情報が数多く存在します。

システム全体のメモリ状況の確認

Memoryオブジェクト
●Available Bytes カウンタ: システム全体の物理メモリの空き容量を確認でき る。 定常的に10MBを下回るような場合は、いくつかのアプリケーションをほ かのコンピュータで動作させるようにする。 可能であれば、 最大使用量に制限を 設けるなどの作業が必要
●SQL Server内部のメモリ使用状況の確認

SQL Server: Buffer Managerオブジェクト

●Page Life Expectancyカウンタ このカウンタはバッファキャッシュ上に読 み込まれたデータが、キャッシュ上に保持される平均秒数を示している。 値が小 さい場合は、読み込まれたデータが次々にキャッシュから追い出されていること になるため、バッファキャッシュのサイズが不十分である可能性がある。 一般的 には300秒以下の場合は、何らかの問題があると考えられる

●SQL Cache Hit Ratioカウンタ クエリから要求されたデータが、 データキャ ッシュ上に存在する割合を表示する。 60秒以上にわたって90%以下になるよう な状況が高頻度で発生するような場合は、 問題の解析および対処の必要がある

Lazy Writes/secカウンタ: バッファキャッシュ上に空きを作るために、レイ
ジーライタがダーティページの書き込みを行なった1秒あたりの回数を示してい る。このカウンタが高い値を定常的に示している場合は、データを展開するため に十分なデータキャッシュが確保できず、 メモリレイジーライタが高負荷であることが推測される

SQL Server Memory Managerオブジェクト

●Memory Grants Pendingカウンタ: クエリを実行するためにワークスペース メモリが獲得できずに待機している数を示している。 定常的に1よりも大きな値 を示す場合には対処が必要

動的管理ビュー

SQL Server 2005以降であれば、 新たに導入された管理機能である動的管理ビュ ーを使用してメモリ使用状況を確認するための詳細なスナップショットを収集でき ます。また、出力される内容は、先のdbcc memory status コマンドよりもさらに多 岐にわたっています。 しかし、 各動的管理ビューのデータを個別に出力したとして も解析することは難しく、意味のある情報を得ることが困難でもあります。そのた め、最初のステップとして、いくつかのホワイトペーパーなどで使用例のサンプル として掲載されているものを流用すると良いでしょう (例を次に示します)。その 後、SQL Server Books Onlineなどで各動的管理ビューの関連への理解が進んだら、 必要な情報を得るためのクエリを記述することも容易だと思います。

例:バッファキャッシュの消費量が多い上位10種類の用途を出力するためのクエリ

select top 10 type, sum(single_pages_kb) as [SPA Mem, Kb
from sys.dm_os_memory_clerks
group by type
order by sum(single_pages_kb) desc

カテゴリー:SQLServer​データベース