わかりやすいSQL Serverの内部構造 I/O動作編

SQLOSスケジューラに管理される側の動作の1つであるI/Oについて、詳しく説明していきます。SQL 主にServerが管理するデータベースと物理ファイルの関連、I/Oを行う内部コンポーネント、アクセスに使用しているAPI、さらにモニタリング方法について記載しています。

SQL Serverが管理するデータベースの実体

SQL Serverは、Windowsというオペレーションシステムの観点から見た場合、ほとんどすべての点において一般的なユーザーアプリケーションと変わりありません。SQL Serverも例外ではなく、長期的にデータを保存するデータベースを管理するアプリケーションです。SQL Serverのデータベースはデータファイルとログファイルという２種類の物理ファイルで構成されています。

データベースを配置したフォルダにはデータファイル（拡張子が.mdfや.ndf）とログファイル（拡張子が.ldf）が置かれます。

データファイル（.mdf、.ndf）

Data files (.mdf, .ndf)

ユーザーが参照/挿入/更新/削除を行うデータ本体が格納されています。SQL Serverはデータファイルの中を8KBのブロックに論理的に分割して使用しています。分割したブロックは「ページ」という論理的な単位として管理されます。また、ページはSQL Serverが物理ディスクから読み込んだデータを処理する際、あるいは物理ディスクへデータを書き込む際の最小の論理的な単位でもあります。

1つのデータベースに対して、1つのデータファイルを指定することも、複数のデータファイルを割り当てることもできます。一般的に、複数のスピンドルを持つディスク装置にデータベースを配置する場合は、データファイルを複数に分割したほうが物理アクセスの速度が向上します。

ログファイル

SQLServerが管理するデータに対して実行した更新内容を記録しています。SQL Serverが管理するデータに対して何らかの更新が行われると、まずデータ自体を更新する前に、変更内容の履歴をすべて物理ディスクに存在するログファイルに書き込みます。変更に関するログがすべて正しく書き込まれると、データ変更の処理が行われます。この動作は「先行書き込みログ（Write Ahead Loggin）」と呼ばれ、SQL Serverがトランザクションとデータの関係を維持するために、とても重要な意味を持っています。先行書き込みログ動作のおかげで、トランザクション処理の途中で電源断などが発生した場合でも、データの状態が確実に把握できるというわけです。

データベースファイルへのアクセスパターン

SQL Serverがデータベースを構成する物理ファイルへアクセスする際に、いくつか特徴的なパターンがあります。

データファイル

オンライントランザクション処理（OLTP）システムの場合

数多くのクライアントが、それぞれごく狭い範囲のデータを参照、あるいは更新します。また、各クライアントが必要とするデータの種類や分布範囲はまちまちであることから、データファイルに格納されたデータがファイル内の様々な場所に点在している可能性が高くなります。その結果として、データファイルへのアクセスはファイル全体にランダムに発生する傾向が強くなります。

意思決定支援システム（DSS）の場合

小数のクライアントが大規模な読み込みをお行います。クライアントが必要とするデータは「ある履歴情報の過去10年分」といった一定の連続性を持ったデータである場合が多くなります。テーブルのデザインにも左右されますが、多くの場合は結果として、データファイルへ順次アクセスが行われる傾向が強いと言えます。

ログファイル

log file

ログファイルへの更新内容の書き込みは、必ず時系列順に行われます。複数の更新処理がデータベースで実行されている場合であっても、それぞれの変更内容が行われた順序どおりにディスク上のログファイルに書き込まれていきます。つまりディスクへの書き込みを行うポイントは常に1か所です。そのため、ディスク装置に複数スピンドルが存在しても、その恩恵を受けることはできません。

また、常に前回ディスクへの書き込みが終了した地点から、次の書き込みを始めます。そのため、それぞれのデータベースにログファイル専用のスピンドルを用意すれば、ヘッドが書き込み開始ポイントまで移動する時間を毎回節約することができます。

（現在では、SSDが主流のため過去参考まで）

SQL Serverが行うI/Oの特徴的な動作

先行読み取り（Read-Ahead）

Read-Ahead

基本的にデータファイルからの読み込みは、クエリが必要とするだけのデータを取得するたびに行われます。それに加えて、SQL Serverは将来的に必要だと予測されるデータに関しては、実際の読み込み要求が発生する前に、あらかじめ物理上のディスクに存在するデータファイルからメモリ（SQL Serverがデータを読み込むためのメモリ領域を「キャッシュバッファ」）へデータを読み込むことがあります。

この操作は「先行読み取り（Read-Ahead）」と呼ばれ、実際に読み込み要求が発生した際のI/Oのオーバーヘッドを緩和するようになっています。一度に行われる先行読み取り操作の量は、SQL Serverのエディションによって差があります。Standard Editionまでの最大数は128ページですが、EnterpriseEditionでは1024ページまで読み込み可能となっています。

チェックポイント（checkpoint）

SQLServerが管理するデータに対して変更を行うと、先行書き込みログ操作が行われます。続いてバッファキャッシュ上のデータが更新されます。この時点での更新操作は「論理書き込み」と呼ばれ、まだ物理ディスク上のデータは更新されていません。その後、いくつかの契機を経てバッファキャッシュ上に更新されたデータが物理ディスクに書き込まれます。その操作は、先の論理書き込みに対して「物理書き込み」と呼ばれます。また、論理書き込みがすでに行われていて、まだ物理書き込みが行われていないバッファキャッシュ上のデータは「ダーティページ」と呼ばれます。

ダーティページに応じて、物理書き込みを行う契機の1つが「チェックポイント」です。SQL Server内にはチェックポイントプロセスと呼ばれる内部コンポーネントが存在し、バッファキャッシュに読み込まれた各データベースのデータを定期的にスキャンしています。スキャンを行った際にダーティページが見つかると、チェックポイントプロセスは各ダーティページに応じて物理書き込みを行います。チェックポイントプロセスは一度に16個までのダーティページの物理書き込み要求を行い、基本的にダーティページがなくなるまで非同期で物理書き込み要求を繰り返します。

集中書き込み（EagerWrite）

集中書き込み動作も、ダーティページに応じて物理書き込みが行われる契機の1つです。通常の場合、先行書き込みログの動作で説明したように、データの更新に関わる動作はすべてログファイルに書き込まれます。データの保存性やトランザクションの一貫性を高めるためにはとても有効な動作ですが、一方でパフォーマンスに好ましくない影響を与える場合もあります。

例えば、ほかのデータソース（メインフレーム上のデータベースなど）からテキストファイルとして出力された大量のデータを、SQL Server上のデータベースに取り込む場合を考えてみましょう。取り込まなくてならないすべてのデータに対して先行書き込みログを行うと、大量のログの書き込みが発生します。ログファイルへの書き込みがボトルネックとなりスループットを著しく低下させ、ログファイルのサイズを肥大化さあせる危険性があります。

そのような場合に備えて「一括操作」と呼ばれる選択肢が用意されています。大量データの取り込みを一括操作として取り扱うことによって、個別のデータの更新ログは書き込まれなくなり、最小限の情報だけがログファイルに記録されます。

一括操作を実行すると、大量のダーティページがバッファキャッシュ上に発生します。集中書き込みは、そのデータを物理書き込みするために用意されたアクティビティです。大量のデータをデータファイルに効率良く書き込むためには、新たなデータを格納するうえで必要となるページを物理ディスク上のデータファイル委に作成する動作と、バッファキャッシュ上のダーティページに応じて物理書き込みを行う動作を並列に実行します。なお、集中書き込みはSQLServer2000以降で採用されました。

レイジー書き込み（LazyWrite）

ダーティページの物理書き込みを行う3番目の動作はレイジー書き込みです。レイジー書き込みを行うためには、SQL Serverは「レイジーライタ」と呼ばれる内部プロセスを用意しています。

レイジーライタの最も重要な使命は、バッファキャッシュに一定量の空ページを用意しておくことです。ディスから読み込んだデータは必ずバッファキャッシュ上に保持されます。データが新たにディスクから読み込まれると、格納するための空ページがバッファキャッシュ上に必要になります。しかし、ディスクからの読み込みが行われるたびに、データの格納先となる空ページを探して毎回バッファキャッシュをスキャンするのは、非効率的なうえパフォーマンスにも悪影響がでます。そのため、あらかじめ使用可能な空ページを「フリーリスト」と呼ばれるリンクリストに登録しておきます。こうすることで、ディスクからデータが読み込まれると、フリーリストに登録されている空ページに格納するだけで済むというわけです。

フリーリストに登録されている空ページの数がしきい値（バッファキャッシュのサイズによって決まる）を下回ると、将来的なデータの読み込みに備えるため、レイジーライタは参照された頻度が低いバッファキャッシュ上のページを初期化して、フリーリストに追加します。もしも、そのページがダーティページだった場合は、初期化する前に物理ディスクに内容が書き込まれます。この動作が「レイジー書き込み」と呼ばれます。

SQL Serverが使用するI/O用のAPI

I/Oを実行するときに使用しているAPIは、Win32APIとして提供されています。

データファイル及びログファイルのオープン

CreateFile関数

SQLServerは、管理しているデータベースのデータファイル（拡張子が.mdfや.ndf）とログファイル（拡張子が.ldf）をオープンするときにCreateFile関数を使用します。データベースに「自動終了」のオプションが設定されていない場合は、SQL Serverは起動時にデータベースファイルをオープンします。また、CreateFile関数を実行する時のオプションとして必ず、FILE_FLAG_WRITETHROUGH及びFILE_FLAG_NO_BUFFERINGスィッチを指定します。

データファイル及びログファイルからの読み込み

ファイルからの読み込みには2種類のAPIが使われています。

ReadFileScater関数

SQLServer7.0以降のバージョンでは、データベースに格納されているデータをディスク上のファイルからバッファキャッシュへ読み込む際にReadFileScatter関数を使用しています。一方、SQL Server6.5以前のバージョンでは、ReadFile関数が使われていました。

ReadFileScatter関数の優れた点は、ファイルから読み込んだデータを、連続していない複数のメモリブロックへ割り当てることができる点です。SQLServerではファイルから読み込んだ一連のデータを、連続したままの状態ではバッファキャッシュに配置できないことが多々あります。つまり、複数の小さいサイズのバッファキャッシュ上のページに対して、ファイルから読み込んだ一連のデータを分割して配置する必要があるということです。そのよう場合、ReadFile関数を使用すると、ファイルから転送されたデータに関してSQL Server自身が並べ替えや分配などの処理を行う必要があります。

それに対して、ReadFileScatter関数では、分配先のメモリブロックを複数指定するだけで済むため、SQL Serverが実行する処理を簡略化できます。

ReadFile関数

SQLServer6.5以前のバージョンでは、ファイルからのデータ読み込み処理全般に使用されていました。SQLServer7.0以降では主にバックアップ関連で使用されています。

ファイルへの書き込み

ファイルへの書き込みも2種類のAPIが使われています。

SQLServer7.0以降では、SQLServerのバッファキャッシュに存在しているデータをファイルに書き込む際にWriteFileGather関数が使用されています。WriteFileGather関数の優れた点は、連続していない複数のメモリブロックを一度の命令実行でディスクへ書き込むことができるところです。チェックポイントが行われると、最大16個のダーティページが物理ディスク上のファイルに書き込まれます。その際に16個のページがバッファキャッシュ上の連続した領域に存在していれば、書き込みの際に1個のメモリブロックとして操作できます。

しかし、小さい場合でも数十MB、大きな場合は数十GBにもなるバッファキャッシュ上で8KBサイズの16個のダーティページが連続して配置されている可能性はとても低いでしょう。バッファキャッシュ上の様々な場所に点在している16個のページをディスクに書き込もうとしたときに、WriteFileGather関数が使用できないとすると、個々のページ分、つまり16回にわたってWriteFile関数を実行するか、事前に16個のページをメモリ上で連続した状態に配置してからWriteFile関数を実行する必要があります。それらの処理はとても大きなオーバーヘッドです。WriteFileGather関数を使用することにより、非効率的な処理から解放されます。

WriteFile関数

SQLServer6.5以前のバージョンでは、ディスクへの書き込み処理全般で使用されていました。SQLServer7.0以降では、主として先行書き込みログの実行やバックアップ関連で使用されています。

モニタリング(Monitoring)

SQLServerのデータベースファイルを配置しているディスクが問題なく動作しているかボトルネックとなっていないか確認する方法を記載します。

パフォーマンスモニタ(Performance Monitor)

SQLServerのデータベースファイルを配置している物理ディスクのPhysicalDiskオブジェクトを監視してください。