SQL Server のメモリ管理 – Part 2


神谷 雅紀
SQL Server Escalation Engineer

 

SQL Server のメモリ管理 - Part 1 は、SQL Server のメモリ管理を理解する上での前提条件としての仮想アドレス空間についての話でした。今回は、SQL Server の話です。SQL Server 2000 ~ 2008 R2 までのバージョンが該当します。

 

バッファプール (Buffer Pool)

 

SQL Server のユーザー仮想アドレス空間は、2 つの領域に分けられます。バッファプール (Buffer Pool) とそれ以外です。

32-bit の場合は、以下のようになります。

 

バッファプールとは?

 

バッファプールは、SQL Server の Buffer Manager によって、管理されるメモリ領域です。

バッファプールには、データベースファイルのコピーイメージ (データキャッシュ)、実行プラン (プロシージャキャッシュ)、クライアント接続の管理構造、ロックの管理構造などが置かれます。

 

バッファプールの予約 (Reserve) サイズとコミット (Commit) サイズ

 

SQL Server が起動すると、MEM_RESERVE の指定された VirtualAlloc Windows API 呼び出しにより、SQL Server の仮想アドレス空間内に、バッファプールのための領域が予約 (Reserve) されます。予約される領域は、起動パラメータ –g に指定されている値と sp_configure ‘max worker threads’ に指定されている最大ワーカースレッド数によって決定されます。バッファプールとして予約してしまえば、仮想アドレス空間内のその領域はスレッドスタックとしては使用できなくなるため、max worker threads の値を、バッファプールとして予約するサイズの決定要因とすることは理にかなっています。スレッドスタック用として残しておくサイズと –g に指定されているサイズを除くサイズ分の領域がバッファプールとして予約されます。

ただし、この計算された予約サイズがユーザー仮想アドレス空間の 1/2 よりも小さい場合は、ユーザー仮想アドレス空間の 1/2 が予約サイズとなります。また、物理メモリサイズが仮想アドレス空間サイズよりも小さい場合は、仮想アドレス空間サイズは物理メモリサイズと等しいものと見なされ、計算が行われます。

 

-g 起動パラメータとは?

-g は、仮想アドレス空間の中で、バッファプール以外、スレッドスタック以外として残しておく領域のサイズを指定するパラメータです。例えば、リンクサーバーを使用する場合や拡張ストアドプロシージャを使用する場合など、DLL が多くロードされる環境では、それら DLL をロードするための領域の残しておくために、-g の指定値を大きくする必要があるでしょう。また、巨大な XML を扱う、リンクサーバーで大きな結果セットを受け取るなど、バッファプール以外のメモリ領域が多く使用される環境では、-g には既定値よりも大きな値を設定する必要があります。

 

ワーカースレッドとは?

ワーカースレッド (worker thread) という用語自体は、一般的に使用される用語ですが、SQL Server で言うところのワーカースレッドは、SQL Server Operating System (SOS) によって作成管理され、SQL Server データベースエンジンに対するクライアントからのリクエスト (ログイン要求、バッチ要求など) やシステムバックグラウンドタスク (Ghost Cleanup など) を処理しているスレッドを指します。リンクサーバーのための OLE DB プロバイダが作成したスレッド、分散トランザクションのための Distributed Transaction Coordinator (MS DTC) プロキシーが作成したスレッド、拡張ストアドプロシージャ DLL が作成したスレッド、、サービスコントロールスレッドなど、非 SQL Server データベースエンジンコンポーネントが作成したスレッドは含みません。このようなワーカースレッドの最大数を決めているパラメータが max worker threads パラメータです。

SQL Server プロセス内には、非 SQL Server データベースエンジンコンポーネントの作成したスレッドも存在するため、SQL Server プロセス内のスレッド数は、max worker threads に指定されている最大数を超える場合があります。

スレッドは、スレッドスタックと呼ばれる個々のスレッドに固有のメモリ領域を持ちます。SQL Server プロセスの場合、32-bit では 512KB、64-bit では 2MB がスレッドスタックとして各スレッドに割り当てられます。

 

バッファプールの拡張と縮小

 

バッファプールとして予約された領域は、新たなメモリが必要になった時点で MEM_COMMIT の指定された VirtualAlloc 呼び出しにより、コミット (Commit) されます。仮想アドレス空間内でバッファプール用に予約されている領域のうち、バッファプールとしてコミットされる最大サイズは、sp_configure ‘max server memory’ によって制限されます。

仮想アドレス空間内の領域を予約しても、物理メモリやページファイル上に領域は確保されません。コミットして始めて確保されます。従って、max server memory でコミットされるサイズの上限を設定しておくことは、バッファプールが物理メモリやページファイルを使用するサイズの上限を設定しておくことになります。

max server memory が明示的に指定されていない場合や予約サイズよりも大きいサイズが指定されている場合、予約サイズがコミット最大サイズになります。また、バッファプールは、sp_configure ‘min server memory’ により、最小コミットサイズを指定することができます。

バッファプールはメモリが必要になった時にコミットされることから、SQL Server 起動直後は、バッファプールのコミットサイズはそれほど大きくはありません。min server memory が指定されていたとしても、起動直後には、コミットサイズは min server memory に達していない場合があります。min server memory がある程度のサイズに設定されている場合は、通常、この状況になります。これは、異常なことではありません。しばらく SQL Server を使用すると、徐々にコミットされた領域が増え、コミットサイズが大きくなります。要求される処理を完了するためにメモリが必要であれば、バッファプールは、min server memory のサイズまではコミットされます。

min server memory のサイズを超えると、その後は、max server memory と min server memory の間で、物理メモリの消費状況に応じて、メモリのコミット (MEM_COMMIT 指定の VirtualAlloc 呼び出しにより Reserved から Committed へ) とデコミット (MEM_DECOMMIT 指定の VirtualFree 呼び出しにより Committed から Reserved へ) を行います。物理メモリの消費状況は、Windows の Memory Resource Notification API を用いて行います。Memory Resource Notification API が、物理メモリの空きが少ない状態であると報告すれば、SQL Server は、その時点で使用されていないメモリをデコミットします。クエリなどにより新たなメモリ要求があり、その時点で、Memory Resource Notification API が物理メモリに空きがあると報告しており、かつ、max server memory に達していなければ、SQL Server は新たな領域をコミットします。もし、max server memory と min server memory の設定値が同一であれば、物理メモリの状態がどうなったとしても、指定されているサイズに達した後は、コミットもデコミットもしません。

このような動作を行うため、max server memory を明示的に指定していない場合には、SQL Server がサーバーの物理メモリを使い尽くしているように見える状況が発生しますが、実際には、SQL Server は、物理メモリの空き領域を考慮しながら動いています。

ただし、物理メモリにあまり空きがない状況では、他のアプリケーションやサービス、ドライバなどが大きなメモリを要求した場合に、物理メモリの不足が発生する可能性はあります。そのような場合にはページングが発生し、サーバー全体がスローダウンする可能性があります。そのため、物理メモリの多くがあまっている環境でなければ、SQL Server 以外が使用するメモリサイズを考慮して max server memory の値は明示的に設定することが推奨されます。

 

今回はここまでです。次回は、バッファプールの使用用途や AWE についてです。

Skip to main content