第1回では「システム データベース」についてまとめてみました。今回はデータベースを構成するファイルなどについて、自分の整理も兼ねて、まとめてみようと思います。
データベースを構成するファイルについて
データベースは、システム データベース、ユーザー データベースに関わらず、「データベース物理ファイル」と「トランザクションログファイル」というファイルで構成されています。
SQL Serverで特にオプションを指定せずに新規データベース (本例では「TESTDB」) を作成した場合、データベース物理ファイル 「TESTDB.mdf」とトランザクションログファイル「TESTDB_log.ldf」の二つのファイルが作成され、この二つのファイルでデータベース「TESTDB」が構成されます。
そのため、データベースを正常に起動させるためには、「データベース物理ファイル」と「トランザクションログファイル」が破損のない、正常な状態である必要があります。
[補足]
データベースを正常に起動させるためには「データベース物理ファイル」および「トランザクションログファイル」が必要となるため、完全バックアップを採取した場合、完全バックアップには「データベース物理ファイル」、「トランザクションログファイル」および、データベースをリカバリするために必要となるトランザクションログ情報が含まれることになります。
データベース物理ファイル (.mdf/.ndf) について
データベース物理ファイルの拡張子は、既定で「.mdf」(プライマリ) 、「.ndf」(セカンダリ) となり、通常は「.mdf」のみでも問題ありませんが、1つのデータベースを複数のデータベース物理ファイルで構成することが可能となります。
例えば、以下の例では、データベース「TESTDB」に対して、セカンダリのデータベース物理ファイル「TESTDB_2.ndf」を追加しています。つまり、データベース「TESTDB」は3つのファイル「TESTDB.mdf」、「TESTDB_2.ndf」、「TESTDB_log.ldf」で構成されたことになります。
データベース物理ファイルには、テーブル (実データ)、インデックス、ストアドプロシージャ、ユーザー関数などのオブジェクトが含まれます。
トランザクション ログファイル (.ldf) について
トランザクション ログファイル (.ldf) の拡張子は、既定で「.ldf」となり、DML操作 (Insert/Update/Deleteなど) のロールバック、SQL Server プロセスの予期しないシャットダウンやクラッシュ時のリカバリに必要となるようなログ情報が書きこまれるファイルになります。
例えば、テーブルに対して INSERT クエリを実行した場合、「LOP_BEGIN_XACT」(トランザクションの開始)、「LOP_INSERT_ROWS」(INSERTレコード情報)、「LOP_COMMIT_XACT」(トランザクションの終了：コミット) というようなログレコードが書きこまれます。
[補足]
SQL Server ではトランザクションによるデータの整合性を保つため、例えば UPDATE クエリで特定のデータを更新した場合、まずは トランザクション ログファイル (.ldf) のバッファ (ログバッファ) にログ情報(変更前／変更後情報) を書き込み、メモリ情報のデータページ (バッファキャッシュ) を更新します。
その後、クライアントからのコミット要求を受け取った後、ログバッファの情報をトランザクション ログファイル (.ldf) に書き込み (フラッシュ)、バッファ キャッシュ上の変更のあったデータページ (ダーティ ページ) については、チェックポイントのタイミングなどで非同期にデータベース物理ファイル (.mdf/.ndf) に反映します。
※ ログバッファからトランザクション ログファイル (.ldf) への書き込み (フラッシュ) は、コミットのタイミング、ログ バッファが一杯になったタイミング、チェックポイントが実行されたタイミングなどで実施されるもようです。
※ SQL Server 2019 では、「高速データベース復旧」機能が実装されており、本機能が有効になっている場合、もう少し複雑な実装になっていると思います。
ファイル グループについて
新規データベース作成時、既定では「プライマリ」ファイル グループが作成され、データベース物理ファイル (.mdf : プライマリ) が紐づけられています。
ファイル グループは、データベースの管理、オブジェクト (テーブル、インデックスなど) の割り当て／分離などをしやすくするための機能になります。
一般的に小規模データベースの場合はあまり意識しなくても問題ないと思いますが、大規模データベースの場合、ファイル グループ単位のバックアップ、リストアが可能であったり、ディスクI/Oの負荷分散を実施するために データベース物理ファイルを複数のドライブに配置したり、また、インデックスとテーブルを完全に分離したい (パフォーマンス チューニングのテクニック) 場合などに考慮することになるかと思います。
例えば、以下の構成の場合、ファイル グループ「PRIMARY」にデータベース物理ファイル「TESTDB.mdf」、「TESTDB_2.ndf」が紐づいているため、ユーザー テーブルを作成しデータを挿入すると、「TESTDB.mdf」、「TESTDB_2.ndf」にデータが分散されて配置されることになり、RAID0 (ストライピング) のような効果により、参照クエリなどで発生するディスク I/Oを分散することで、クエリのパフォーマンスの向上が期待できます。
※ 1つのファイルグループに複数のデータベース物理ファイルが紐づいている場合、データベースの空き容量が多いファイルに対して、データの挿入が行われるようになります。
また、以下の構成の場合、データベース物理ファイル「TESTDB_IDX.ndf」は、ファイル グループ「PRIMARY」とは異なるファイル グループ「FG1」に紐づいています。
テーブルやインデックスなどの作成時、オブジェクトを配置するファイルグループを指定することができるため、例えば、テーブルはファイル グループ「PRIMARY」、非クラスタ化インデックスはファイル グループ「FG1」に作成することで、データとインデックスとを完全に分離することができます。
まとめ
今回は、SQL Serverの「データベース ファイル」 に関する内容 (一部 バッファキャッシュ、ログバッファに関する内容) をまとめてみました。
次回は、「SQL Server インスタンス」に関する内容についてまとめてみようと思います。
【第3回】基本から始める SQL Server【インスタンス】へ
