MySQLエキスパートyoku0825が目指す、DBAとしての未来像 を興味深く読ませていただいたのですが、記事の中の次の質問がとても面白い内容でした。
インストールされたばかりのMySQLがあるとして、特定テーブルに1件のレコードを最初にINSERTした場合、アクセスが発生するファイルとその理由をすべて教えてください
MySQLに初めてINSERTするとアクセスが発生するファイルは何かという質問をどう調べるのか で MySQL についてまとめられていますが、SQL Server ではどのようになるのかまとめてみました。
Contents
SQL Server の公式ドキュメント
SQL Server はソースコードが公開されていないので、基本動作については公式ドキュメントからどのような動作となるかを把握します。
SQL Server は、ARIES (Algorithms for Recovery and Isolation Exploiting Semantics) 復旧モデルが採用されているため、発生しているトランザクションについては、Write-ahead logging (WAL) により、トランザクションログの書き込みが発生します。
SQL Server のトランザクションログのアーキテクチャについては次のドキュメントで解説が行われていますので、ログ書き込みの基本原理についてはこの情報から確認することができます。
トランザクションログに書き込まれたデータについては、チェックポイントプロセスによってデータファイル内のページに書き込みが行われます。
書き込み時のページの動作については次のドキュメントで情報を確認できます。
基本動作については、これらのドキュメントから把握を行います。
SQL Server のデータベースを構成するファイル
SQL Server のデータベースのファイル構成については、データベース ファイルとファイル グループ に記載されていますが、シンプルな構成です。
- プライマリデータファイル (mdf)
- セカンダリデータファイル (ndf)
- トランザクションログファイル (ldf)
最小構成のデータベースは「一つのプライマリデータファイル」と「一つのトランザクションログファイル」の構成となっており、二つのファイルで構成が行われています。
そのため、アクセスが発生するファイルだけで考えると、
- トランザクションログファイルに書き込みが行われ、
- チェックポイント発生時にデータファイルに書き込みが行われる。
となるのではないでしょうか。
SQL Server はデータファイル内をページに分割し、ページは複数の役割 (データを格納するもの / インデックスを格納するもの等) があり、それらを組み合わせることで、データベースが構成されています。
冒頭の質問を SQL Server で考えた場合「どのようなページ (領域) に対してアクセスが発生するか」が質問に対して適切な答えとなるのではないでしょうか？
ということで動作をしらべてみる
今回は、SQL Server 2019 に対して新規に作成したデータベースにテーブルを作成した場合の動作としてみていきたいと思います。
作成しているテーブルは次のようなものとなり、このテーブルに「INSERT INTO T1 VALUES(1,NEWID())」をした時の動作についてみています。
DROP TABLE IF EXISTS T1 GO CREATE TABLE T1( C1 int primary key clustered, C2 varchar(36) )
Windows ベースの環境であれば、トレースは Process Monitor でトレースすることができますが、この情報はファイルアクセスベースのトレースとなりますので、この情報のほかに、拡張イベントや windbg などを使ってトレースしています。
トランザクションログ (ldf) の書き込み
ファイルレベルの書き込み
SQL Server はトランザクションは WAL で処理しますので、INSERT を実行した場合には、ファイルアクセスとしては最初にトランザクションログファイル (ldf) への書き込みが発生します。
もう少し踏み込むと
SQL Server ではバックグラウンドプロセスとして「LOG WRITER」が起動しており、ユーザーセッションで発生したトランザクションについては、LOG WRITER のスレッドで、ログレコードが ldf ファイルに対して書き込みが行われます。
物理ファイル (ldf) への書き込みの前段階として、トランザクションログのレコードは、最初に、最初にメモリ上のログプール (ログバッファ) に書き込みが行われ、その後、コミットしたタイミングでディスクにフラッシュされます。
上記の Process Monitor で取得できていたのは、このフラッシュの動作となります。
ログプールへの書き込み (メモリアクセス) については、Process Monitor では取得ができないため、拡張イベントで情報を取得してみます。
拡張イベントで取得した INSERT を実行した際のトレースが上記になるのですが、Process Monitor で取得がされていたのは「database_log_flush」の情報となります。
ログをフラッシュする前に「log_single_record」として、ログレコードが生成されているのですが、このログレコードについてはメモリ上に生成が行われています。
SQL Server はステートメントは自動コミットされますので、自動コミットのコミットが実行されたタイミングで、database_log_flush が発生し、ldf ファイルに書き込みが行われます。
この database_log_flush のタイミングの情報が Process Monitor で取得できた、ldf ファイルへの物理書き込みの情報となります。
データファイル (mdf) の書き込み
ファイルレベルの書き込み
WAL でログファイルに書き込みが行われるた変更は、実際のデータとしても反映が行われる必要があります。
SQL Server では、データファイルへの書き込みはチェックポイントが発生した際に書き込みが行われますので、Process Monitor で mdf に対して書き込みが行われたタイミングでは、チェックポイントが発生しており、このタイミングでデータファイルへの永続化が行われています。
もう少し踏み込むと
ユーザーが明示的にチェックポイント (CHECKPOINT) を実行した場合は、ユーザーセッション内でチェックポイントが実行されますが、通常は、バックグラウンドタスクとして起動している CHECKPOINT スレッドによってチェックポイントが実行されます。
これにより、メモリ上のダーティーページがデータファイルにフラッシュされ、データファイル上で永続化が行われますが、ここに至るまでには様々なページの書き込みが行われています。
テーブルを作成した直後はスキーマ情報のみがデータベース内に書き込まれており、最初の INSERT を実行したタイミングでデータ領域の割り当てが行われているようです。
データページに対して書き込みを行う際には、データを格納する領域であるエクステントを確保する必要があります。
そのため、GAM (Global Allocation Map) から使用可能なエクステントの確保を行います。
確保されているページ内の空き領域については PFS (Page Free Space) で管理がされていますので、割り当てたページについて PFS の情報を更新します。
インデックスの割り当て情報については、IAM (Index Allocation Map) で管理がされているため、このページの情報に情報を格納する必要があるのですが、最初に、ページのフォーマットをする必要があるため初回アクセス時にはページフォーマットを実行し、その後 IAM にデータの格納を行います。
IAM では、インデックスツリーに対して、どのページを見ればデータが格納されているかを判断することができるようになっていますので、次のデータであれば 352 ページにデータが格納されているということが確認できます。
データのページ情報を確認すると、352 ページに格納されていることが確認できますね。
実際には、このページに格納する前にも、ページのフォーマットが行われてから格納が行われます。
最初にこのような変更がメモリ上のデータページ内で行われます。
これらの操作は「トランザクションログレコードの書き込み」として行われている操作でもあるといえますので、上記のページの変更はトランザクションログの書き込みで実際に行われていることともいえるのではないでしょうか。
チェックポイントが発生したタイミングで、Process Monitor で確認できた、ダーティーページをメモリからディスクにフラッシュする操作により、データファイルへの書き込みが行われます。
新規に作成したテーブルに対して INSERT を行う場合には、簡単に思いつくものだけでもこのような変更が行われています。
なお、データが 1 件だけですと Index Page は作成しないようで、インデックスツリーについてはこのタイミングでは存在してないようです。
1 ページ目にデータが収まらなくなった場合に、2 ページ目にインデックスページを作成して、3 ページ目以降に次のデータを書き込むというような動作となっているようなので、インデックスツリーを格納するインデックスページについては、データが増えてきたタイミングで作成されているようです。(1ページ内にデータが収まっているのであれば、first_page/root_page が同一になっており、2ページ目にデータを格納し、インデックスページが作成された場合には、root_page が最初のインデックスページを指し示すようになります)
最後に
今回の投稿では、ざっくりと INSERT した際にどのような書き込みが行われているのかを書いてみましたが、これだけでは足りていない箇所がたくさんあります。
新規にデータを割り当てた際のシステムアロケーションテーブルへの変更や、ダーティーページを管理するための Dirty Page Table (DPT) 、DCM (差分変更マップ) への変更についても考慮する必要がありますし、ページに格納されるデータについても、ページヘッダーの情報の書き換えなども実際には行われています。
テーブルの構造によっては使用されるページも変わりますし、クエリストアが有効な場合には、クエリストアの情報を書き込むような処理も行われますので、本投稿の内容は「INSERT した場合に変更される情報のほんの一部」となります。
SQL Server は、データベースのファイル構造はシンプルですが「どのような情報が変更されているか」で考えると、最初の INSERT によって変更される情報というのは奥深いものがあります。
SQL Server の動作を詳細に勉強するため / どこまで把握ができているかを確認するためには、
インストールされたばかりのMySQLがあるとして、特定テーブルに1件のレコードを最初にINSERTした場合、アクセスが発生するファイルとその理由をすべて教えてください
は、SQL Server 観点で考えても、とても面白い質問ですね。