SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

tempdb のロギング最適化による最小のログ記録の動作を確認してみる

leave a comment

SQL Server の tempdb では、ロギング最適化という動作により、トランザクションログの書き込みを最小限にするようにされています。

最近は、「SQL Server を使いこなす」という観点での勉強を進めており、その中でトランザクションログの書き込み内容の解析も多少できるようになってきましたので、tempdb のロギング最適化による、最小のログ記録の動作についても、実際のトランザクションログの書き込み内容を元にしてみていきたいと思います。
(本投稿の最小のログ記録については、一括挿入を実行する際等の最小ログ記録とは別の動作です)

データの変更を行った場合、トランザクションログには「変更前のデータ」(Before Image)「変更後のデータ」(After Image) が記録されているというのが一般的な理解ではないでしょうか。
次のようなテーブルをユーザーデータベースに作成します。
(tempdb にテーブルを作成した場合は動作が異なりますので注意してください)

SET NOCOUNT ON
DROP TABLE IF EXISTS T1
CHECKPOINT
CREATE TABLE T1 (
	C1 uniqueidentifier DEFAULT NEWSEQUENTIALID()
	, C2 varchar(60)
)

 
このテーブルにデータの変更を行った際のトランザクションログに記録される内容を見てみましょう。
まずは、INSERT を実行してみます。

INSERT INTO T1(C2) VALUES('1 かずあき = 8GB')

INSERT の場合は「RowLog Contents 0」に INSERT したデータが登録されますので、次のような情報がトランザクションログに登録されます。
image
DELETE を実行した場合の考え方も INSERT と基本は変わりません。

DELETE FROM T1

 
削除を行ったレコードの情報が「RowLog Contents 0」に格納されますので、削除対象のレコードの情報を確認することができます。
image
最後に UPDATE を実行してみます。

UPDATE T1 SET C2 = '2 かずあき = 16GB'

 
UPDATE を実行した場合は「RowLog Contents 0」に Before Image、「RowLog Contents 1」に After Image が記録されます。
image
データの変更の仕方によるのですが、「1 かずあき = 8GB」を「2 かずあき = 16GB」に変更した場合、「GB」という文字列については共通の文字となっていますので、GB より前のデータを変更するというようなログの記録となります。
「1 かずあき = 8GB」→「2 かずあき = 8GB」というような変更を行った場合は、先頭の数字部分のにも変更となりますので、トランザクションログの書き込みについては次のようになります。
image
これがトランザクションログの書き込みの通常の動作となります。
それでは、tempdb に一時テーブルを作成した場合の動作で見てみましょう。

(テーブル変数も同様の動作となります)
一時テーブルのデータ書き込みにつてもトランザクションログの記録は行われていますので、通常のテーブルと同様のアプローチでログの内容を確認することはできます。
次のクエリでテーブルを作成します。

(ポイントとなるのは「ヒープ」で一時テーブルを作成していることです)

SET NOCOUNT ON
DROP TABLE IF EXISTS #T1
CHECKPOINT
CREATE TABLE #T1 (
	C1 uniqueidentifier DEFAULT NEWSEQUENTIALID()
	, C2 varchar(60)
)

 
テーブルの作成が終わったら、データの投入を行ってみます。

INSERT INTO #T1(C2) VALUES('1 かずあき = 32GB')

 
INSRET 後にテーブルのデータを確認すると次のようになります。

SELECT Operation, AllocUnitName,[Log Record Length],[RowLog Contents 0], [RowLog Contents 1], [RowLog Contents 2]
FROM sys.fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_INSERT_ROWS' AND AllocUnitName LIKE 'dbo%'
GO

image
RowLog Contents は「0x」となっており、登録したデータが登録されていないことが確認できますね。

1 かずあきが 32 GB は許せないから記録されていないということではなく、この動作が「ロギング最適化による最小のログ記録」の動作となります。
では、次にこのような UPDATE を実行して、かずあき単位を修正してみます。

UPDATE #T1 SET C2 = '2 かずあき = 16GB'

 
この時のログの記録内容がこちらになります。
image
Before Image は登録されていますが、After Image は登録が行われていません。
DELETE を実行した場合は、削除したレコードの登録が行われます。
image
tempdb のデータは、セッションが破棄されたタイミングや、SQL Server のサービスが再起動したタイミングで削除される揮発性のあるデータとなります。
そのため、トランザクションログの書き込みについては、最適化が行われており、その最適化の一環として、「ヒープテーブルに対してのトランザクションログの書き込み」の動作が通常のテーブルとは異なっています。
先ほどはヒープで一時テーブルを作成しましたが、主キーを付けてデータを登録してみます。

SET NOCOUNT ON
DROP TABLE IF EXISTS #T1
CHECKPOINT
CREATE TABLE #T1 (
	C1 uniqueidentifier DEFAULT NEWSEQUENTIALID() PRIMARY KEY
	, C2 varchar(60)
)

ヒープ構造ではない一時テーブルについては、通常のテーブルのようにログレコードが生成されていることが確認できますね。
image
ヒープと B-Tree 構造の一時テーブルの単純な INSERT で大きく性能差は出ていないように見受けられましたが、トランザクションログの内容を分析すると、ドキュメントや書籍に書いている情報が「実際にはどのような動作となっているのか?」を調査 / 学習するのに役に立ちますので、トランザクションログの解析ができるということは無駄なスキルにはならなさそうですね。

Share

Written by Masayuki.Ozawa

1月 5th, 2020 at 4:47 pm

Leave a Reply