誰も知らないメモリ・チューニングの極意を教えよう：Dr. K's SQL Serverチューニング研修（2）（3/3 ページ）

» 2006年03月03日 00時00分公開

「Procedure Cache」のメモリ不足もパフォーマンスの大敵

　CPU負荷を増やすもう1つの原因が「Procedure Cache」です。メモリ上に蓄えられた「Procedure Cache」の内部には、クライアントから発行されたSQL文やT-SQL文、ストアドプロシージャ、パラメータクエリなどをオプティマイザが解析した実行プランが格納されています。メモリ不足による競合が発生すると、せっかく一度コンパイルした実行プランが、「メモリプレッシャー」のときと同様にメモリから消えてしまいます。

　図8を見ると「Procedure Cache」の最大値は「866.34Mbytes」に対して最小値は「141.68Mbytes」まで減少しています。こうなると、何度も新たに実行プランを作り直さなくてはなりません。この生成プロセスは非常にメモリを消費するので、あらかじめメモリに余裕を持たせていれば避けられたはずの、CPU負荷の増大が起きます。

数多い設定項目を前に途方に暮れるのはまだ早い
メモリ管理にかかわる重要パラメータは4つ

ユーザー自身で設定可能な「sp_configure」の重要パラメータ

　ユーザー自身が「設定可能な＝チューニング可能な」SQL Server 2000のパラメータは、17種類あります。これは冒頭でも触れた「sp_configure」環境設定オプションで設定可能な項目なのですが、この中でメモリのチューニングにかかわる重要なパラメータは表1の4種類があります。


環境設定オプション	最小	最大	既定値
awe enabled	0	1	0
max server memory	4	2147483647	2147483647
max worker threads	10	1024	255
user connections	0	32767	0
表1　SQL Server 2000でメモリ・チューニングにかかわる重要なパラメータ

awe enabled

　4Gbytesを超えて拡張メモリを使ってよいかどうかの設定を行います。使い方は前節で説明したとおりです。

max server memory

　前節で触れましたが、メモリのバッファプール全体の領域を設定するパラメータです。物理メモリは全体で

メモリのバッファプール
memory to reserve
SQL Server 2000のワーキングセット
Windows Serverのワーキングセット

の4つの領域に分かれます。「max server memory」で指定するのは最も上位のバッファプールであることを覚えておいてください。この内容は次回に詳しく説明します。

図9　「SQL Serverのプロパティ」の「メモリ」タブ
「SQL Serverメモリの動的設定」は「sp_configure」環境設定オプションの「max server memory」「min server memory」に対応する。

max worker threads

　これはSQL Serverがユーザーのために用意しているスレッドの容器のようなものと考えれば分かりやすいでしょう。スレッドはSQL Server OSの中でマルチタスクで動いているため、スレッドの切り替えにレジスタの待避領域が必要になってきます。これを「ワーカースレッド・スタック」と呼び、32bitバージョンでは1スタック当たり512Kbytes、64bitバージョンでは4Mbytesが割り当てられています。これもメモリ管理に影響するパラメータです。

　既定値は「255」で、通常はクライアント接続ごとに1つ以上のワーカースレッドが作成されるので、接続数の増加に伴ってメモリ消費も増加します。ユーザー接続数が「max worker threads」に設定した値を超えると、新規に発生したユーザー接続は、SQL Serverのワーカースレッドの空きを待つ待機キュー上で管理されます。大量のクライアント接続が発生するシステムでは、この最大数を変更する必要がありますが、4CPU以下を持つSQL Serverでは、既定値は変更すべきではありません。5CPU以上のSMP環境では、4CPU増加ごとに既定値に32を加えた値を、マイクロソフトは推奨しています。

図10　「SQL Serverのプロパティ」の「プロセッサ」タブ
「ワーカースレッド最大数」は「sp_configure」環境設定オプションの「max worker threads」に対応する。

user connections

　同時に確立できるユーザー接続の最大数を指定する場合に使用するもので、既定値は「0」です。「0」は動的設定であることを意味しており、データベースの利用状況に応じて必要なだけのメモリをSQL Server自身が取りにいくのです。しかし急にユーザー接続数が増えると、すぐにはメモリを確保できない場合があります。この結果、ここを上手に設定していないと、アクセス数が急激に変化するシステムではユーザーが接続できないといったトラブルが発生します。このようなシステムでは、値を指定することを検討した方がいいでしょう。最大32767個のユーザー接続を指定できます。

図11　「SQL Serverのプロパティ」の「接続」タブ
「同時ユーザー接続の最大数（0 = 無制限）」は「sp_configure」環境設定オプションの「user connections」に対応する。

　今回はメモリに関連するチューニングのポイントを紹介しました。パフォーマンス低下の表面的な事象がCPU負荷の増大やディスクI/Oであっても、その原因はメモリ不足による競合が発生している場合もある、ということを理解していただけたと思います。次回は、的確なメモリを見積もるために必要な知識やノウハウを紹介する予定です。お楽しみに。（次回に続く）

監修者プロフィール

株式会社エイ・エヌ・テイ（2006年4月1日より株式会社CSK Winテクノロジに社名変更）執行役員／コンサルティング部長。

熊澤幸生（くまざわ・ゆきお）

メインフレーム環境で20年近くデータベース関連のITプロジェクトを数多く経験。また1979年から1983年まで米国に駐在し、データ主導型システム設計を実プロジェクトで学ぶ。1994年、アスキーNT（現、エイ・エヌ・テイ）設立に参加し、SQL Server Ver 4.2からSQL Server 2000までシステム構築、教育にかかわってきた。現在同社執行役員 Chief Technology Officer。また、SQL Serverユーザー会「PASSJ」の理事として活動中。

「次回」へ

「Dr. Ks SQL Serverチューニング研修」バックナンバー