I/Oボトルネックの病巣はこれで究明できる：Dr. K's SQL Serverチューニング研修（7）（3/3 ページ）

» 2006年09月12日 00時00分公開

[熊澤幸生, 工藤淳（インタビュアー），CSK Winテクノロジ/オフィスローグ]

ビットマップの排他制御がパフォーマンス低下の原因になる

　このように各種のビットマップを使ってエクステントを確保するときには、ビットマップ自体にも排他制御をかけなくてはなりません。ところがビットマップというのは、1つのファイルシステムに1個しかないのです。そうすると複数のトランザクションがSQL Serverに対して一斉にINSERT処理を要求し、領域確保が発生したときに何が起こるかを考えなくてはなりません。ここがボトルネックになってしまった場合、トランザクションの同時実行性を阻害する大きな要因になってしまいます。

　このビットマップに対する排他制御は、ロックでなくラッチで行われます（ラッチに関しては第4回「排他制御メカニズムから“待ち”原因を究明する」を参照）。ここがSQL Serverのよくできたところで、なるべくラッチで済むものはラッチで処理し、トランザクションが済むまでホールドするロックは使用しないのです。一連の領域のアロケーションが終わったら即座に排他制御を解放して、ほかのトランザクションが利用できるようになっているのです。

　なぜこうした内部メカニズムの話をしているのかというと、こういう仕組みが分かっていないと、第4回でもお話ししたような、wait事象によるパフォーマンス低下の原因を追究できないからです。実はここが一番チューニングのノウハウのコアになっている部分で、内部的な領域アロケーションを行うときに、どういうタイプのラッチやロックを使うかというのが大きなポイントになるのです。これを私自身も、日常のチューニング業務の中で追究し続けているのです。

　これがSQL Server 2005になると、動的管理ビューにより詳細な内部の状況はかなり知ることができるようになったのですが、どれをどのように直せば、どんなパフォーマンスが得られるかというのは、やはりどこにも書いてありません。エンジニアはきちんと細部まで調べ抜いて、どこに同時実効性を阻害する要因があるのかを探っていかなくてはなりません。

I/O負荷分散はDBチューニングで最も深い部分だ

　最後にもう1つ、大事なtempdbの話をしておきましょう。tempdbは、トランザクションの間だけ有効な一時テーブルです。頭が「#～」という命名規約に基づいて、tempdbというデータファイル（tempdb.mdf）の中に一時的なテーブルを作るのです。ということは、ソートを伴うクエリなどが発行されると、毎回一時テーブルを作成する（DDLと一時データの格納と参照）処理が実行されるので、データやインデックスの格納されている論理ドライブとは別の領域に作成することを推奨します。

　領域を管理するためのビットマップはファイル単位に作成されるといいました。つまり、n個のファイル（サーバ上の物理CPUコア数と同一ファイル数を推奨）があるということは、n個同時にビットマップの処理ができることを意味しています。こうしたこともI/Oチューニングの中で念頭に置いておかなくてはならない知識の1つです。

　前回と今回でお話ししたI/O負荷分散というのは、データベース・チューニングの中で、一番ディープな部分の1つではないかと思います。ユーザーセッションという論理的なトランザクションの単位を、うまくリレーショナルエンジンが交通整理して、排他制御をかけながらなおかつファイルシステムに対して必要な領域を、あらかじめプリフォーマットしてある場所から確保してきて、そのオブジェクトに対してアロケーションしていって、初めて実際のデータを格納できるのです。

　新規のページを1つ追加するというのは、前後のページにあるページポインタを更新しなくてはなりません。そうした複雑で膨大な処理がデータベースエンジンの中で行われているのです。SQL Serverは、そうしたバックエンドでの処理を全部自動でやってくれてしまうので、それをご存じない初心者プログラマは「動いた動いた」と……（笑）。しかし実はここが、パフォーマンス・チューニングを行ううえでは、大きな落とし穴になっているのも事実です。

　確かにSQL Serverの自動化された機能は、データベースの門戸をより多くのビジネスユーザーに開放し、なおかつ不必要な労力を軽減することに貢献しています。しかし、チューニングに携わるプロフェッショナルのエンジニアまでが、「ああラクだ、ラクだ」といっていてよいものでしょうか？

　データベースの背後にある一連の、しかも同時並行で動いている内部的なプロセスを巧みに操りながら、最も効率の良い処理を実現していくのは、なまはんかな知識やテクニックではなし得ません。この記事をお読みの皆さんも、SQL Serverのチューニングを究めようとお思いならば、ぜひデータベースの奥深い場所まで降りていって、自分の手ですべてのシステムの動きを学ばれることをお勧めします。

　8月29日～9月1日にパシフィコ横浜で開催された「Microsoft Tech・Ed 2006」では、私のセミナーに数多くの方のご来場をいただき、大変感謝しております。次回は、SQL Server内のシステムスレッド、ユーザースレッド、ユーザースレッドの並行処理と、SQL Serverリレーショナルエンジン、ユーザーモードスケジューラの関係を予定しております。（次回へ続く）

監修者プロフィール

株式会社CSK Winテクノロジ執行役員兼技術推進担当。

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

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

「次回」へ

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