12 月 8 日に Microsoft 社が開催している SQL Server の Get The Fact セミナーに参加をしてきました。
SQL Server の真実 – Get The Fact セミナー

このセミナーですがチョークトークの形で開催されており、Microsoft 社 の SQL Server Product Manager チームの方と、かなり近い距離でセッションを受けられるとても貴重な機会でした。
# 質問を Twitter でつぶやくと答えてくれたり。

第 1 回は運用管理について以下の内容についてのセッションでした。

1. ログ管理
2. バックアップ & リカバリ
3. でタッチ & アタッチによるデータベースの移動
4. パフォーマンスデータコレクション

まずは、ログ管理について数回に分けてまとめていきたいと思います。
今回はデータ書き込み時にログがどのように使われるかと、ログの基本的な構成について、セッションの内容をふまえ振り返っていきたいと思います。

■データ書き込みの基本動作

トランザクションログについてまとめるにあたり、セミナーでもお話しがあったのですが、データ書き込みの基本動作について軽くまとめてみたいと思います。

SQL Server のデータ書き込みですが、ログキャッシュに書き込み → トランザクションログファイルに書き込み → メモリ上のデータを変更 → チェックポイント発生時にメモリ上のデータをデータファイルに書き込みという流れになります。
# ログキャッシュと、ログファイルへの書き込みタイミングは大抵の場合、タイムラグはさほどないというお話でした。

データ変更時の流れを概要図としてまとめると以下のようになります。
# 概要図なので実際の挙動とは少し違うところがあるのですが。

データ変更 (INSERT / UPDATE / DELETE / TRUNCATE) をした場合、ログに書き込んだ後にすぐにデータファイルを更新するのではなくメモリ上のデータを変更して、その後に実際のデータファイルの更新が行われます。
② の処理でメモリ上のデータは変更されているが、実際のデータファイルが更新されていないデータ (ページ) を [ダーティーページ] (汚れたページ) と呼び、メモリ上のデータをデータファイルに書き込むタイミングを [チェックポイント] と呼びます。

ダーティーページに関しては、以下のクエリで確認をすることが可能です。

SELECT     CASE database_id         WHEN 32767 THEN ‘Resources’         ELSE DB_NAME(database_id)     END AS [DatabaseName],     COUNT(*) AS [TotalPage],     SUM(CONVERT(int,is_modified)) AS [DirtyPage] FROM     sys.dm_os_buffer_descriptors GROUP BY     database_id

TEST というデータベースに対してデータ更新を行った際のダーティーページの情報が以下になります。
TEST データベースでメモリを 8 ページ使用しており、そのすべてのページがダーティページとなっています。
# データを新規に INSERT した時のメモリの情報になります。

チェックポイントが発生し、メモリ上のデータがデータファイルに書き込まれると、その処理で使用していたログファイルの内容 (変更されたデータの情報) はデータファイルに反映された状態となりますので、そこまでの処理が終了しすることで、ログの内容が消せる状態となります。

メモリ上のデータにしか変更が反映されていない状態で、その変更分のログが消されてしまいますと、メモリの内容が無くなった 場合 (シャットダウンや異常終了等) に、変更内容を戻すことができなくなってしまいますので。

 

手動でチェックポイント (CHECKPOINT ステートメントを実行) した後に、ページの状態を取得するクエリを実行した時の結果が以下になります。

ダーティーページがフラッシュされ、0 になっているのが確認できます。
ダーティーページをフラッシュしても、メモリ上にはキャッシュとしてデータは残った状態になります。
更新されたデータをそのままメモリ上に残しておいた方が、そのデータを使用する処理が発生した場合、ディスクからデータを読まずに済みますので。

■トランザクションログファイルの構成

ログファイルは [ldf] という拡張子のファイルで構成がされています。
ログファイルは複数の ldf ファイルで構成することも可能なのですが、ログファイルはシーケンシャルに使われますので複数のファイルで構成しても、ファイルが並行で使用され負荷が分散されるという事はありません。

概要図を書くと以下のようになります。

ログファイルを複数用意するのは、負荷分散ではなく (複数ファイルを用意しても負荷は分散されない) ログファイルを格納しているドライブの容量がなくなり、追加のログファイ
ルを設定しなくなてしまった場合になるのかと思います。
# このような状況が発生しないようにするのがベストなのですけどね。

ログファイルは、データファイルとは異なりファイルグループと言った概念がなく、単一のファイルで構成されているのですが内部的には仮想ログファイル (Virtual Log File : VLF) という単位に分割がされています。

ログファイルはトランザクションログのバックアップを適切に実行することでこの仮想ログが循環されながら使用されることになります。

データ変更時のログの内容ですが、ログレコードとして管理されており、ログレコードには [LSN] (Log Sequence Number) というシーケンシャル No が振られています。

トランザクションログのログレコードの内容は [DBCC LOG] を実行することで確認をすることができます。
DBCC ログは以下の形式で実行を行います。

dbcc LOG (dbname | dbid [,{0|1|2|3|4}[,[‘lsn’,'[0x]x:y:z’]|[‘dir’, 0|1]|[‘numrecs’,num]|[‘xdesid’,’x:y’]|[‘extent’,’x:y’]|[‘pageid’,’x:y’]|[‘objid’,{x,’y’}]|[‘logrecs’,{‘lop’|op}…]|[‘output’,x,[‘filename’,’x’]]|[‘column’,'<value>’]|[‘key’,'<value>’]|[‘nolookup’]|[‘allocid’,BIGINT]…]]])

 

ログレコードの詳細まで表示する場合は、オプションとして [4] を設定します。
[DBCC LOG(N’TEST’, 4)] といった形式で実行することでログレコードの内容を含めて取得することが可能です。

トランザクションをロールバック / ロールフォワードするときなどは、この LSN を使用することで、どの地点まで戻せばよいのかを判断しています。 
データファイルのページでも LSN は保持しており、ページの LSN とログの LSN を比較することで、ロールフォワードの必要があるかを判断しています。
# この辺の詳細は別途まとめる予定です。

ページの情報は [DBCC PAGE] を実行することで、取得ができます。
以下は DBCC PAGE の実行例です。
# DBCC PAGE で情報を取得するためには、トレースフラグ [3604] を有効にする必要があります。

DBCC TRACEON(3604) DBCC PAGE (N’TEST’, 1, 78) DBCC TRACEOFF(3604)

PAGE: (1:78) BUFFER: BUF @0x0000000085FB5F00 bpage = 0x00000000853A8000           bhash = 0x0000000000000000           bpageno = (1:78) bdbid = 5                            breferences = 0                      bcputicks = 0 bsampleCount = 0                     bUse1 = 23176                        bstat = 0xc00009 blog = 0x21212159                    bnext = 0x0000000000000000          PAGE HEADER: Page @0x00000000853A8000 m_pageId = (1:78)                    m_headerVersion = 1                  m_type = 1 m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8200 m_objId (AllocUnitId.idObj) = 45     m_indexId (AllocUnitId.idInd) = 256  Metadata: AllocUnitId = 72057594040877056                                 Metadata: PartitionId = 72057594039173120                                 Metadata: IndexId = 0 Metadata: ObjectId = 2137058649      m_prevPage = (0:0)                   m_nextPage = (0:0) pminlen = 5                          m_slotCnt = 700                      m_freeCnt = 396 m_freeData = 6396                    m_reservedCnt = 0                   m_lsn = (286:13625:2) m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0

ページ情報の [m_lsn] からページが更新された際の LSN を確認することが可能です。

仮想ログにはシーケンシャル No が内部的に振られており、ログレコードはこのシーケンシャル No を考慮しながら仮想ログを先頭から使用しながら書き込まれていきます。

このように書き込みが
行われますので、複数のファイルを用意した場合は以下のように使用されます。

仮想ログファイルは [DBCC LOGINFO] を実行することで確認することができます。
このクエリを実行すると以下のような情報が取得できます。

この情報を確認することで、ログファイルが内部的にいくつの仮想ログに分かれていて、今どの仮想ログが使用されている (再利用ができない) 状態なのかを確認することができます。
Status = 2 は ACTIVE なログが存在している VLF となるため、再利用することができません。
# この辺は SQL Server を実行しているコンピュータでトランザクション ログのサイズが予期せず増大する、または、ログがいっぱいになる に少し記載があります。

この状態の VLF は [トランザクションログのバックアップ] を取得することで、アクティブなログレコードを含んでいない VLF のStatus が 0 (REUSABLE) となります。

REUSABLE に変わった VLF は再利用が可能になりますので、新規にログレコードを書き込むことが可能となります。

現在、アクティブなログを含んでいる VLF に関しては、Status = 2 のままですが、アクティブなログを含んでいない VLF に関しては Status = 0 となり再利用が可能となります。
# アクティブなログを含む (Status = 2) の VLF もログレコードが書き込める (空きがある) のであれば利用されます。

ログの切り捨て時 (再利用のために解放) には、[MinLSN] (最小復旧 LSN：データベース全体をロールバックするために必要となる最初の LSN) が切り捨て可能なスタート地点となります。
この、MinLSN から、最後に書き込まれたログまでを [アクティブなログ] と呼び、このアクティブなログが含まれている仮想ログに関しては、データベースの回復に必要となるため、再利用することはできません。
# MinLSN が実際に見れれば説明がしやすかったのですが、ちょっと取得方法がわかりませんでした。

この Status = 0 の状態の VLF ですが、[DBCC SHRINKFILE] を実行した際に、縮小される単位になります。
VLF はログファイルを作成 (あるいは拡張) したタイミングでファイルサイズとログの個数が決まりますので、使用している VLF の状態によっては、SHRINKFILE をしても思ったよりファイルが小さくならないことがあります。

セミナーではログ管理をするにあたって、このようなログの基本的な動作についてのお話を聞くことができました。
# 基本的に振り返りの内容は、私が間違って理解している個所があるかもしれませんので、間違っていた場合はセミナーの内容ではなく私の理解力不足です…。あと、今回の内容をすべてお話しいただいたのではなく、調べる取りかかりの情報をいただけた形になります。

他にも SQL Server のログ管理 (バックアップ) で重要となる [復旧モデル] についてもお話しがありました。

次の投稿では、この復旧モデルについて振り返ってみたいと思います。