第13回では「SQL Server のメモリ管理」に関する内容をまとめてみました。
今回は、SQL Server のデータベースを構成する物理データファイル (.mdf/.ndf ファイル)の構造について、自分の整理も兼ねてまとめてみようと思います。
ページ(pages)について
SQL Server のデータベースを構成する物理データファイル (.mdf/.ndf) に割り当てられたディスク領域は、0～n の連番が付けられたページという単位で論理的に分割されおり、ディスク I/O 操作 (読み取り／書き込み) はページレベルで実行されます。
SQL Serverの場合、すべてのページは「8KB」に固定されており、メモリ上にキャッシュされるデータページのサイズも同様に「8KB」になります。
[補足]
メモリ上のキャッシュ領域 (バッファキャッシュなど) 上のページが頻繁に入れ替わったとしても、ページサイズは「8KB」に固定されているため、メモリ上の断片化が発生しないアーキテクチャになっています。
ページの種類
すべてのページサイズは「8KB」に固定されていますが、使用用途により、Data、Index、Text/Image、GAM (Global Allocation Map)、SGAM (Shared Global Allocation Map)、PFS (Page Free Space)、IAM (Index Allocation Map)、BCM (Bulk Changed Map)、DCM (Differential Changed Map) のような様々な種類のページが存在します。
各々のページの種類について簡単に説明します。
|
ページ タイプ |
説明 |
|
Data |
実際のデータが保持されているページ。 ※ text, ntext, nvarchar(max), varchar(max), xml などの１行に 8,060 kb 以上のデータを含むデータ型を除く。 |
|
Index |
インデックスに使用されているページ。 ※ インデックスのキー情報などを含む。 |
|
Text/Image |
text, ntext, nvarchar(max), varchar(max), xml などの１行に 8,060 kb 以上のデータを含むデータが保持されているページ。 ※ 可変長列に保持されたデータ長の合計が 8,060 kb 以上になる場合も同様に Text/Image ページが使用される。 |
|
GAM (Global Allocaition Map) |
どのエクステントが既に割り当てられているかが記録されているページ。 ※ 1つのGAMページで 64,000 エクステント (約4GB) を管理することが可能であり、この ビットが「1」の場合は未使用状態を示し、「0」の場合は既にオブジェクトに割り当てられていることを示す。 |
|
SGAM (Shared Global Allocation Map) |
混合エクステントとして使用中であり、1ページ以上が未使用状態であるかが記録されているページ。 ※ 1つのSGAMページで 64,000 エクステント (約4GB) を管理することが可能であり、このビットが「1」の場合は混合エクステントで1ページ以上の空きがあることを示し、「0」の場合は混合エクステントとして使用されていないエクステント、もしくは、空きページのない混合エクステントであることを示す。 |
|
PFS (Page Free Space) |
ページ割り当て情報、および、ページ上で使用可能な空き容量を管理するページ。 ※ ページの使用率を5段階 (0%, 1-50%, 51-80%, 81-95%, 96-100%)で管理され、8,088 ページ毎に PFSページが追加される。 |
|
IAM (Index Allocation Map) |
クラスタ化／非クラスタ化インデックスで使用されているエクステントを管理するページ。 ※1つのIAMページで 64,000 エクステント (約4GB) を管理することが可能。 行データをオブジェクトに挿入する際に、現在のページに空き容量がない場合、IAMページで該当のオブジェクトに割り当てられたエクステントを検索し、検索されたエクステントに使用可能なページが存在するか、PFSページを検索するといった動作が行われる。 |
|
BCM (Bulk Changed Map) |
最後にトランザクションログのバックアップ(BACKUP LOG)が実行されてから、一括ログ操作によって変更されたエクステントを管理するページ。 ※ このビットが「1」の場合、一括ログ操作によって変更されたエクステントであることを示し、「0」の場合、一括ログ操作によって変更されていないエクステントであることを示す。BCMは、次回 トランザクションログのバックアップの際に、バックアップに含めるべきエクステントを特定するために使用される。 |
|
DCM (Differential Changed Map) |
最後に完全バックアップ(BACKUP DATABASE)が実行されてから、変更されたエクステントを管理するページ。 ※ このビットが「1」の場合、変更されたエクステントであることを示し、「0」の場合、変更されていないエクステントであることを示す。DCMは、差分バックアップでバックアップすべきエクステントを特定するために使用される。 |
ページの構造
各ページのオフセットの先頭から 96 Bytes は、ヘッダー情報として各ページに関する情報が格納されます。
例えば、ページ番号、ヘッダー バージョン情報、ページの種類、ページが使用されているオブジェクト情報、オブジェクトで使用されている前／次のページ情報、ページの空き容量、ページに含まれるゴーストレコード数 などの情報が含まれます。
[ページ ヘッダー例]
ページの種類が「Data」の場合、ページ ヘッダー情報 (96 Bytes) の後に、実際のデータが挿入されていきます。
[ページ データ情報 例]
また、ページの種類が「Data」の場合、ページのオフセットの末尾に「行オフセット テーブル」情報が保持され、該当の行が始まる先頭のアドレス情報(ページの先頭からのオフセット情報) が行単位 (slot単位)で管理されています。
[ページ オフセット テーブル情報 例]
上記の例の場合、Row 0 (0x0) のデータは、データページの先頭から 96 バイト目 (0x60) の slot 0 に存在し、Row1 (0x1) のデータは、データページの先頭から 311 バイト目 (0x137) の slot 1 に存在していることを示しています。
SQL Server 2019 (Azure SQL Database) 以降であれば、動的管理ビュー「sys.dm_db_page_info」を使用することで、ページの詳細情報を確認することが可能です。
エクステント(Extents)について
エクステントは、領域を管理する際の基本単位となり、領域の拡張、解放はエクステント単位で行われます。
1 エクステントは、物理的に連続した8ページ (64KB) で構成されています。
エクステントには、「単一エクステント」、「混合エクステント」の２つの種類が存在します。
各々のエクステントについて以下に記載します。
単一エクステント
単一のオブジェクトに所有され、所有しているオブジェクトのみがエクステント内のすべてのページ (8ページ) を使用することが可能。
混合エクステント
複数のオブジェクトで共有され、エクステント内の各ページが異なるオブジェクトで使用される可能性がある。
Azure SQL Database や SQL Server 2016 以降では、master、msdb、model などのシステム データベースで使用される可能性があるが、Temp データベース、ユーザー データベース の場合、既定で 単一エクステントが割り当てられるように実装が変更されたため、使用される頻度が低くなっています。
[補足]
SQL Server 2014 以前の場合、ラッチ競合を軽減させるテクニックとして「トレース フラグ 1118」を有効化し、常に 単一エクステントを使用する動作に変更する場合がありましたが、SQL Server 2016 以降では、「トレース フラグ 1118」が既定で有効化されています。
なお、ALTER DATABASE の SET句で「MIXED_PAGE_ALLOCATION」パラメータを「ON」(既定値「OFF」) にすることで、特定のユーザーデータベースに対して、SQL Server 2014 以前と同じく、オブジェクトの最初のページは混合エクステントが割り当てられる動作が行われます。
ページの構造 (GAM/SGAM)
GAM (Global Allocaition Map) は「単一エクステント」、SGAM (Shared Global Allocation Map) は「混合エクステント」使用状況を管理しているページなります。
各データページが GAM/SGAM のどちらのエクステントから割り当てられているかを確認するコマンドは確認できていないのですが、データページ情報 を確認することで、該当のデータページが GAM/SGAM のどちらの エクステント から割り当てられているかを確認することが可能です。
[データページ 例]
例えば、上記のデータページの場合、「GAM (1:2) = ALLOCATED」、「SGAM (1:3) = NOT ALLOCATED」になっているため、該当のデータページは GAM から割り当てられていることが確認できます。
また、ページ 1:2 がGAM、ページ 1:3 がSGAM として使用されているページとなります。
[GAM ページ情報]
上記の例のように、GAMページでは、単一エクステント (ページ) の割り当て状況を確認することができます。
[SGAM ページ情報]
上記の例のように、SGAMページでは、混合エクステント (ページ) の割り当て状況を確認することができます。
[PFS ページ情報]
PFS ページ情報では、各ページの使用状況を確認できると共に、どのページが 単一／混合 のどのエクステントに割り当てられるているかについても確認することができます。
まとめ
今回は、SQL Server のデータベースを構成する物理データファイル (.mdf/.ndf ファイル)の構造、および ページ／エクステントに関する内容についてまとめてみました。
物理データファイル (.mdf/.ndf ファイル) は、論理的にページ／エクステントで構成され、領域の割り当て／解放 はエクステント単位で行われ、また、ページ／エクステントは GAM/SGAM/PFS などの特殊なページで管理されていることが確認できたと思います。
通常、上記のようなアーキテクチャを知らなくてもデータベースを使用することはできますが、アーキテクチャを知っていることで、何か問題が発生した場合の問題の切り分け、改善に役立つ可能性があるため、知っておいて損はないのではないかと思います。
【第15回】基本から始める SQL Server【統計情報1】へ
※ 2022年1月 更新
