Support Service Day のご案内 毎月 8 日を「サポート・サービスディ」とし、障害以外の製品利用技術に関してお客様からのご質問や疑問にお応えする機会を設けました（こちらは無償となります）。相談されたい案件をお持ちのお客様は、毎月10日から20日までの期間内に以下をご記載の上、s8spday@microsoft.com 宛に電子メールをお送りください。 ①サポート契約番号 ②相談事項概要 後程、弊社担当者よりご連絡を差し上げ日程等を調整させて頂きます。 場所：日本マイクロソフト（株） 調布技術センター 対象製品：SQL Server, Internet Information Server, Internet Explorer, Visual Studio, SDK（但し最新バージョンと一世代前のバージョン） 受付期間：毎月10日から20日までの期間 サポート・サービスディ：休日を除く毎月8日 午前10:00-午後05:00 議題：お客様相談事項 時間：2-3時間の予定 弊社参加者：マネージャ、エンジニア
詳細はこちら
神谷 雅紀 Escalation Engineer
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 がそれと同等の減少をしていない場合、その事象はページングが原因であると考えて間違いありません。
ページングによるスローダウンを防ぐために、まず最初にすべきことは、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 の使用メモリは減少します。
以下は 10240MB に設定する場合の例です。
sp_configure ‘max server memory’, 10240 go reconfigure go sp_configure ‘max server memory’ go
赤枠内の「最大サーバーメモリ」を変更します。
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