メモリの自動チューニング機能を完全に把握しよう：SQL Server 2000 チューニング全工程（1）（1/3 ページ）

本連載ではSQL Server 2000のチューニングに関するノウハウを解説する。SQL Server 2000は自動チューニング機能を持つために、チューニングはあまり必要ないと思われがちだが、そのアーキテクチャを理解し適切にツール類を使用しなければ、本来のパフォーマンスを得られない。（編集局）

[沖要和，NRIラーニングネットワーク]

SQL Serverのチューニングについて

　データベースの設計を終え、システムの稼働を始めたとき、エンドユーザーにとって期待していた応答時間が実現できなかった場合、システムのチューニングが必要になることがあります。設計段階で格納されるデータ量やユーザーの同時接続数、実行されるトランザクションの数などが正しく見積もられ、正規化されたテーブルに、適切なインデックスや必要に応じた非正規化などの最適化を施したテーブル構造を実装し、適正なストアドプロシージャやクエリが書かれていれば、本番環境で稼働を始めても、それほど多くのチューニング項目は残されていないはずです。

　しかし、現在の企業システムを取り巻く環境では、データ構造の変更やデータ量や利用者数の増加、新しい機能の追加などが急速に発生し、データベースの状態や使用状況が常に一定ということはありません。そのため、以前は問題のなかった処理が、ユーザー数やデータ量の増加に伴い、求められる性能を満たさなくなってしまうということが発生します。

　SQL Server^注のイメージとして、そのほかのデータベース製品が「マニュアル車」としたら、SQL Serverは「オートマ車」といった例えがあります。SQL Serverは前バージョンの7.0からそのアーキテクチャを大きく変化させ、システムの使用環境は絶えず変化するという前提の下で、SQL Serverが使用状況や環境の変化に合わせて、自らを最適にチューニングするような自動チューニングのアーキテクチャへ進化しました。しかしその半面、「SQL Serverをデータストアとするアプリケーションを開発したが、どうもパフォーマンスを上げられなくて困っている」とか、「SQL Serverのチューニングって本当のところどうやって行うべきなのか」といった話も聞こえてきます。

注）本文中で特に断らない限り、SQL Serverと表記した場合はバージョン2000を指す。また、次期バージョンとなるSQL Server 2005の情報については、適宜盛り込む予定だ。

　実はSQL Serverでのパフォーマンス問題の多くが、データベースの設計段階の誤りであったり、適正なインデックス付けがなされていなかったり、SQL Serverのロック メカニズムをよく理解しないまま書かれたSQLクエリが原因になっていたりします。また、せっかくの自動チューニング機能やツール類が生かされていない状況も多く見られます。オートマ車であっても、運転するためには免許証の取得が必要になるように、SQL Serverでも、正しく運用していくには、そのアーキテクチャと使用するツール類の理解が、まず不可欠でしょう。

　そこで本連載では、SQL Serverの自動チューニングのアーキテクチャがどのようなものなのかを解説し、そのうえでチューニング作業によく選択されるツール類の使用に当たってのポイントを紹介しながら、オートマ車であるが故に陥りがちないくつかの問題点にフォーカスし、その解決方法を紹介していきたいと考えます。

　2005年にはSQL Serverの次期バージョンになるSQL Server 2005が出荷予定となっています。2004年のTechED 2004では参加者特典として、SQL Server 2005のベータ2が配布される予定になっているようですが、ベータ版を検証しチューニング関連で拡張された機能が確認できた場合、こちらも合わせてトピックスとしてお伝えしていきたいと考えています。

動的メモリ管理のメカニズム

　SQL Serverの管理するリソースの中で、最も重要なのがメモリ上のアドレス空間といえます。SQL Serverのアドレス空間は、SQL Serverのシステムが使用している実行コードの領域とメモリプールの2つの領域から構成され、それぞれに複数の構成要素が含まれています。このアドレス空間の中で、動的にサイズが変更される領域がメモリプールです。

図1　SQL Serverのアドレス空間

　以下の表1ではメモリ プール内に割り当てられる領域の主な役割を説明しています。

領域名	役割
システム レベルのデータ構造体	データベース記述子、ロック テーブルなどのインスタンスにグローバルなデータを保持するデータ構造体
バッファ キャッシュ	データ ページが読み取られるバッファ ページ プール
プロシージャ キャッシュ	インスタンス内で現在実行中のすべてのTransact-SQLステートメントの実行プランが入っているページ プール
ログ キャッシュ	ログごとに、ログ ページの読み書きに使用するバッファ ページのキャッシュ。ログ キャッシュは、ログ バッファとデータ バッファの同期を減らすため、バッファキャッシュとは別に管理される
接続コンテキスト	接続ごとに、接続の現在の状態を記録する一連のデータ構造体。これらのデータ構造体には、クエリやストアドプロシージャのパラメータ値、カーソル位置情報、現在参照されているテーブルなどのアイテムが含まれている
スタック領域	Windowsでは、SQL Serverによって起動されるスレッドごとに512Kbytesのスタック領域を割り当てる
表1　メモリ プールの構成要素

　これまでのデータベース管理システムでは、例えば、昼間の通常運用時と夜間のバッチ処理時でデータベースのメモリ利用方法が異なるような場合、それぞれのパラメータファイルを作成し、その間の切り替えで再起動をスケジュールするといった運用もあったでしょう。

　SQL Serverでは、ユーザーの接続数、そこに投げられたクエリの数とコストに応じ、自動的にオプティマイザが最適なメモリの割り当てを判断しバッファプール内で動的なチューニングの面倒を見てくれます。この動的なメモリチューニング機能では、できるだけディスクIOをしないように、つまり仮想メモリ（ページングファイル）の使用を避けるように、物理メモリの領域に、常に4〜10Mbytesの空き領域を確保しつつ、その中に、できるだけ多くのデータを効率よい形で展開していくといったことを動的に行ってくれます。空き領域が5Mbytes未満の場合、フリーリストに割り当てられるメモリがWindowsに解放されます。物理メモリの空き領域が、5Mbytes以上ある場合、メモリがバッファ キャッシュに再コミットされます。

　このような機構によってメモリ プールは最適化されるため、ロック数やオブジェクトのオープン数、接続数の上限などの複雑な設定オプションを個別に行う必要がなくなります。これらはすべて動的に制御されます。またデータベースの使用中にOSも別のサービスを動かすため、メモリを使用しなければいけなくなった場合、SQL Serverは柔軟にリソースを分け与えることもしてくれます。

図2　動的メモリ管理のメカニズム（概念図） （クリックで拡大します）

　次ページでは、システム管理者が行うメモリチューニングの実作業をステップバイステップで解説していきます。（次ページに続く）