DO’s&DONT’s #12: やった方がいいこと - max server memory を設定する

 

神谷 雅紀
Escalation Engineer

 

サーバースローダウンなどの事象

SQL Server の一時的な無応答、処理の遅延 (サーバースローダウン)、クエリタイムアウト、ログインタイムアウト、ネットワークエラー、既存のクライアント接続の切断、MSFC フェールオーバーの発生、MSFC での SQL Server クラスタリソース再起動、ミラーリングフェールオーバー、ミラーリングパートナータイムアウト、CPU 高負荷 (使用率 100%) といった事象が発生することがあります。これらの原因のひとつがページング (paging) です。ページングにより SQL Server プロセスのワーキングセット (working set) が小さくなることで、SQL Server 上で行われるすべての処理が遅くなり、また、SQL Server が動作するサーバー全体のパフォーマンスが悪化します。

このような事象が発生した場合、定常監視項目として Memory:Pages/secProcess:Working Set, SQLServer: Buffer Manager\Total pages パフォーマンスカウンタを採取している場合には、それらのカウンタを確認してみて下さい。問題の事象の発生と同時に Memory:Pages/sec の値が上昇し、sqlservr プロセスの Process:Working Set の値が大きく減少してるにも関わらず、SQLServer: Buffer Manager\Total pages がそれと同等の減少をしていない場合、その事象はページングが原因であると考えて間違いありません。

 

対応方法

ページングによるスローダウンを防ぐために、まず最初にすべきことは、max server memory 値の設定です。既定では、max server memory は 2147483647 に設定されています。この設定は、「そのサーバーで使用可能なメモリはすべて SQL Server が使用することを許可する」という設定です。

SQL Server がバッファプールとして使用するメモリ領域を小さくすることで、他のプロセスやサービス、ドライバなどがメモリを要求したとしても、物理メモリが不足する状況が発生しないようにします。そのために、max server memory を物理メモリサイズよりも小さなサイズに設定します。

設定するサイズは、物理メモリサイズから、Windows OS を含む SQL Server 以外が使用するであろう最大サイズを差し引いたサイズを指定します。これを基準として、物理メモリの空き (Memory: Available MBytes) やページングの発生 (Pages/sec) を観察します。max server memory 設定後もページングが発生している場合には、max server memory の設定をさらに小さくします。ページングが発生せず、物理メモリに一定量の空きが常にある場合には、max server memory の値を大きくすることができます。

最もやってはいけないことは、実際の状況を見て設定値を調整せず、机上で計算した値に固定しようとすることです。ちょっとした検証作業を行えば設定値の妥当性を検証できるにもかかわらず、それを行わずに固定してしまうのは、節約したコストに対してあまりにもリスクが大きいと思います。

設定変更は、T-SQL でも Management Studio GUI でも可能です。Management Studio GUI から設定を変更する場合は、サーバーのプロパティの「メモリ」ページから設定変更可能です。

max server memory 変更後、SQL Server の再起動は必ずしも必要ありません。設定変更時点の SQL Server バッファプールのサイズが max server memory に指定されたサイズよりも大きい場合は、SQL Server は解放可能なメモリを解放することでバッファプールのサイズを指定値以下にしようとしますので、徐々に SQL Server の使用メモリは減少します。

 

T-SQL での変更

以下は 10240MB に設定する場合の例です。

sp_configure ‘max server memory’, 10240
go
reconfigure
go
sp_configure ‘max server memory’
go

 

Management Studio GUI での変更

赤枠内の「最大サーバーメモリ」を変更します。

maxservermemory

 

max server memory を設定しても、ページングによって SQL Server のワーキングセットが切り詰められる状況が頻発する場合には、SQL Server サービスアカウントに「ページのロック」(Lock pages in memory) 権限を付与し、バッファプールをページングの対象外とすることを検討します。

Lock Pages in Memory オプションを有効にする方法 (Windows)

 

ページング

SQL Server が使用するメモリの大半はデータベースデータや実行プランをキャッシュすることを目的としています。キャッシュとは、データをメモリ上に留めることでパフォーマンスを向上させることを目的としているため、可能な限り多くのデータを可能な限り長くメモリ上に留めておくことで、キャッシュの効果は高まります。

SQL Server は、定期的に Windows に対して物理メモリの使用状況を確認しますが、Windows が物理メモリの空きが少ないと報告しない限りは、キャッシュできるデータを多くするために必要に応じてメモリを確保し続けます。より長くデータをキャッシュするために、Windows に対する物理メモリ使用状況確認において物理メモリの空きが少ないと報告されない限り、一度確保したメモリは解放しません。

SQL Server が Windows に問い合わせた時点では物理メモリに空きがある状況であっても、その直後に何らかのプロセスやサービス、ドライバなどが多くのメモリを要求すれば、次に SQL Server が Windows に物理メモリの空き状況を問い合わせる前に、物理メモリの不足が発生する可能性があります。Windows への問合わせにより SQL Server が物理メモリの不足を知ったとしても、使用中のデータが置かれているメモリは解放できないため、その不足分すべてを補うだけのメモリを即座に解放できるとは限りません。このような場合に、ページングが発生します。

SQL Server のメモリ管理 - Part 2