SQL Server max server memory を設定する

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

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

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

http://blogs.msdn.com/b/jpsql/archive/2012/02/06/do-s-amp-dont-s-12-max-server-memory.aspx

対応方法

ページングによるスローダウンを防ぐために、まず最初にすべきことは、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 の値を大きくすることができます。