I/Oチューニングを成功させる必修ポイント：Dr. K's SQL Serverチューニング研修（6）（1/3 ページ）

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

SQL Serverは一般的にチューニング不要のデータベースと認識されている。しかし基幹系業務システムへの導入が進むにつれて、パフォーマンス・チューニングのニーズは急速に高まってきた。そこで本記事では、日本におけるSQL Serverコンサルタントの第一人者、熊澤幸生氏にSQL Serverチューニングのノウハウを語っていただくことにした。インタビュアーはSQL Serverへの造詣が深いITジャーナリスト、工藤淳氏が担当する。（編集部）

　SQL Serverのチューニングで重要なポイントとして、今回から2回にわたり「I/O関連」に焦点を絞って考えてみましょう。I/Oにおけるボトルネックをいかに発見し、その原因を追究して改善に導いていくかが、データベースのパフォーマンス・チューニングでは非常に重要です。

トランザクション処理のプロセスをまず確認しよう

　本題に入る前に、一連のトランザクション処理のプロセスを、レイヤごとに分けた図1を見ながら、SQL Serverの動作を確認しておきましょう。

図1　SQL Serverのトランザクション処理と各レイヤ（画像をクリックすると拡大します）

アプリケーション・レイヤ

　クライアント・アプリケーションの動作を見ると、まず処理の単位として「トランザクション」があります。アプリケーション・レイヤでは、トランザクションの開始を宣言する「BEGIN TRAN」から始まって、「SELECT」「UPDATE」「INSERT」といった処理があり、「COMMIT TRAN」でコミットを宣言するまで、これが1つのアトミック（不可分）なトランザクションのプロパティになっています。この1つのアトミックな単位をSQL Serverのリレーショナルエンジンが受け取って、メモリ上のバッファなどを介してクエリ処理に展開します。

リレーショナルエンジン・レイヤ

　SQL Serverのリレーショナルエンジンには、I/Oに関連したバッファ領域（メモリプール内のオブジェクト）が2つあります。

• バッファキャッシュ
  ここにはデータページやインデックスページが格納され、8Kbytesごとのページ単位で管理されています。
• 論理ログファイル（ログキャッシュ）
  メモリ上にあるトランザクションログで、それぞれのログレコードにはトランザクション単位のLSN（Log Serial Number：ログシリアル通番）が収められており、更新データの前後のイメージであるBI（Before Image：別名UNDO）とAI（After Image：別名REDO）の2種類の情報を行単位で記録しています。

物理ファイル・レイヤ

　ここはWindows OSが管理するNTFSなどのファイルシステムの世界です。物理ファイルのレイヤにも2種類あります。

• MDF（プライマリデータファイル）、NDF（セカンダリデータファイル）
  バッファキャッシュとの間でデータを読み書きする物理ファイル。
• LDF（ログファイル）
  論理ログファイルが書き込まれる物理ログファイルです。

SANレイヤ

　一般的には、Windows OSの先にSAN（ストレージ・エリア・ネットワーク）のような共有ディスクの環境があります。この共有ディスク上のパーティションは、Windows OSから見て通常はLUN（Logical Unit Number）と呼ばれる仮想的なパーティションストレージ（ドライブレターのD〜Zのようなもの）上に作られています。その仮想のパーティションをファイバチャネルなどでつないで、OSがトランザクション処理の仲介を行います。

　こういう各レイヤを通じた一連のトランザクションの振る舞いをまずきちんと理解したうえで、リレーショナルエンジンの中のバッファ管理とか、OSのファイルシステムとの間でどういうやりとりがなされているかを見極め、これを最適化するのがI/Oチューニングの目的になります。

　本連載では、第2回と第3回にメモリ（図1の中央のバッファキャッシュやログキャッシュ）に関する話をしました。続く第4回と第5回では、アプリケーションの振る舞いに対して、トランザクション単位の論理的な整合性を保つために、ラッチやロックといった排他制御の話をしました。そしてその中でレスポンスタイムという観点から影響の大きいページ分割、インデックス作成のポイントなどの話をしてきました。

　その一連の流れのうえで、今回はバッファキャッシュ／ログキャッシュと物理ファイルとの関係を解明して、I/Oチューニングを学んでいこうと考えています。

処理済みページは非同期でファイルシステムに書き込まれる

　チューニングをI/Oの負荷分散という観点から考える場合、バッファキャッシュと物理ファイルとの関係から突き詰めていかなくてはなりません。その中でもとりわけ、SQL Serverのバッファ・マネージャ＝リレーショナルエンジンとファイルシステムとの間でどういう動きが起こっているのかを知ることが、チューニングを行ううえでは欠かせないのです。

　バッファキャッシュの中には、8Kbytesのページ単位でデータが格納されていきますが、実際にはアプリケーションが更新したデータは、トランザクションの動きとはまったく別に、非同期にファイルシステムに一括書き込みされる仕組みになっています。この処理をバッファフラッシュとも呼びます。つまり更新済みのメモリ上のページは、リアルタイムではなくチェックポイント処理によってファイルシステムに対して、一括実更新されます。

　書き込みの間隔は、バッファキャッシュの中の更新済みのページが一定のしきい値を超えた場合、あるいはオプションで指定した復旧時間内にサーバが復旧できるサイズを超えた場合に、チェックポイントが実行されます。復旧時間は「sp configure」の中の「recovery interval」というパラメータで指定します。単位は分で、既定値の「0」（復旧時間は1分未満）は、SQL Serverが適切な間隔を自動的に決めて、ダーティページ（トランザクションから更新済みのページ）をファイルシステムに書き込みます。

　しかしこれだけだと、運の悪いタイミングでシステムがダウンしてしまった場合、バッファキャッシュ上にある更新済みのページがファイルシステムに書き込まれていない可能性があります。そこでこの空白時間を担保するために、「物理トランザクションログファイル」（LDF）が別に用意されています。これは一括書き込みではなく、「論理ログファイル」というログキャッシュに変更が生じるたびに、随時ファイルシステムに書き込んでいくようになっています。書き込みの単位はトランザクションの「BEGIN?COMMIT」で、LSN（ログ・シリアル番号）によって管理されています。

　バッファキャッシュから非同期で一括実更新を行う際にも、チェックポイント処理を行ったという記録は、論理ログファイルにも伝えられる仕組みになっています。こうしたトランザクションとは非同期に行われる一連の処理を、スムーズにSQL ServerとWindows OSが協調して行うためには、何を考えなければならないかを紹介していきましょう。（次ページへ続く）