間にいくつか投稿をはさんでしまいましたが仮想ログファイルの管理オーバーヘッドについてみていきたいと思います。

トランザクション ログのファイルサイズは同一にして、仮想ログファイル数を変更し、トランザクション ログのバックアップの時間の変化を確認してみます。

 

■仮想ログファイルの数とトランザクション ログのバックアップへの影響

---

トランザクション ログの拡張サイズを [1MB] にして、1,000MB まで自動拡張をさせてデータベースでトランザクションログのバックアップを取得してみます。

この時の仮想ログファイルの状況を以下のクエリで取得してみます。

DECLARE @LogInfo TABLE(     [RecoveryUnitId] SMALLINT,     [FileId] SMALLINT,     [FileSize] BIGINT,     [StartOffset] BIGINT,     [FseqNo] SMALLINT,     [Status] SMALLINT,     [Parity] SMALLINT,     [CreateLSN] Numeric(20) ) INSERT INTO     @Loginfo EXEC (‘DBCC LOGINFO’) SELECT     [Status],     COUNT(*) AS [VLF Count] FROM     @LogInfo GROUP BY     [Status]

実行した結果がこちらです。

現在、トランザクション ログは[3,997] の仮想ログファイルで構成されています。
拡張のサイズが 1MB 単位になっている場合、仮想ログファイルが細かな単位で作成されることになります。
# 1MB → 1,000MB に拡張するためには 999 回の自動拡張処理が必要となり、999 × 4 の仮想ログファイルが作成されるので、3,996 個は仮想ログファイルが作成されます。今回は1MB からは始めていなかったので数字通りにはなっていませんが近似値は出ていますね。

[DBCC SQLPERF(‘LOGSPACE’)] で取得をしたログファイルの使用状況がこちらになります。

[TEST] データベースのログ ファイルはほぼ上限値まで使われていることが確認できました。。

それではこの状態で、トランザクション ログのバックアップを取得してみます。
バックアップ時のログがこちらになります。

Processed 123884 pages for database ‘TEST’, file ‘TEST_log’ on file 1. BACKUP LOG successfully processed 123884 pages in 22.603 seconds (42.819 MB/sec).

 

バックアップを取得するのに [22.603 秒] かかっています。

続いて、設定を変更して 100MB 単位で自動拡張をするようにして、トランザクション ログを上限まで使用したデータベースにしてみました。

仮想ログファイルの状態がこちらです。

100 MB 単位の拡張の場合仮想ログファイルは一度の拡張に 8 個に分割されて作成されます。
# 今回は 1MB → 1,000MB の拡張ですので 10 回の拡張が必要となります。最初の仮想ログが何個だったのか確認するのを忘れていたのですが 10 × 8 で 80 個の仮想ログファイルは作成されていますね。(最後の 99 MB の拡張は自動ではなく手動で実施しました。)

トランザクションログの使用状況も先ほどと同様で上限まで使用されていることが確認できます。

この状態でトランザクション ログのバックアップの取得時間を確認してみます。

Processed 127891 pages for database ‘TEST’, file ‘TEST_log’ on file 1. BACKUP LOG successfully processed 127891 pages in 15.463 seconds (64.615 MB/sec).

先ほどは [22.603 秒] だったのですが、今回は [15.463 秒] で取得が終わっています。

この状態だと近似値となってしまうかもしれませんので、各設定で 5 回バックアップを取得して時間を調査してみました。

1MB 拡張	100 MB 拡張
22.603 秒	15.463 秒
24.025 秒	15.332 秒
22.882 秒	16.536 秒
22.791 秒	16.664 秒
23.989 秒	15.825 秒

仮想ログファイルの数によってトランザクション ログのバックアップにかかる時間が変わっていますね。

トランザクション ログのバックアップを取得する際には仮想ログの Status の変更が発生します。
仮想ログのファイル数が多い Status 変更のオーバーヘッドがありバックアップの取得時間に差が出てきているのだと思います。

今回はバックアップで試してみましたが、大量のログ領域を使用する場合にも頻繁に Status の変更が入ると思いますので、同じ傾向になるかと。

今回は 10 秒以内の差でしたが数十 GB 単位のログが必要になる場合には、仮想ログファイルのサイズにも少し考慮をした方が良さそうですね。