SE の雑記

SQL Server / SQL Database で UPDATE を実行する際に、現在の値と同じ値で更新 (SET 句に指定した変更内容が現在の値と同じ) をした場合の挙動について触れておきたいと思います。

同値による UPDATE によるトランザクションログのレコードの生成について

最初にテスト用のデータを作成しておきます。

DROP TABLE IF EXISTS UpdateTest
CREATE TABLE UpdateTest
(
    C1 int primary key,
    C2 varchar(100)
)
INSERT INTO UpdateTest VALUES(1,'1 かずあき=8GB')

テスト用のデータが生成できたら、次の UPDATE を実行してみます。

UPDATE UpdateTest SET C2 = '1 かずあき=8GB' WHERE C1 = 1

このクエリは C1 = 1 の C2 の値を 「1 かずあき=8GB」→「1 かずあき=8GB」に更新をしています。

つまり「同値」による更新をかけているものとなります。

UPDATE を実行した際に、どのようなトランザクションログが書き込まれているか、次のクエリを実行して確認してみます。

SELECT * FROM sys.fn_dblog(NULL, NULL) WHERE [Transaction ID] IN (
    SELECT [Transaction ID]  FROM sys.fn_dblog(NULL, NULL) WHERE [Transaction Name]= 'UPDATE'
)

ステートメントレベルのトランザクションは実施されているのですが、実際にデータを変更するためのトランザクションログのレコードが生成されていないことが確認できますね。

それでは、現実ではありえないのですが、1 かずあきを 16 GB に変更する UPDATE を実行するとどうなるでしょうか。

UPDATE UpdateTest SET C2 = '1 かずあき=16GB' WHERE C1 = 1

「LOP_MODIFY_ROW」が「LCX_CLUSTERED」に対して実行されており、対象のオブジェクトは「dbo.UpdateTest.PK__UpdateTe__32149A1357F7F422」であるというトランザクションログのレコードが追加されていますね。

もう一度実行した場合は先ほどの 8GB の更新と同様で、同値による更新となりますので、1 かずあきが 16 GB というのは認めないということではなく、同値更新のためにトランザクションログのレコードが生成されていないということが確認できます。

SQL Server では、同一の値で UPDATE を実行した場合は、上記の結果のように、実レコードの変更を行うトランザクションログのレコードが生成されないというケースがあります。

この挙動は UPDATE のテストを実行する場合に、把握していることが重要となります。

UPDATE の性能テストを実施する場合、次のようなクエリを実行してテストを行うと、同値による更新となり、実レコード更新部分のトランザクションログのレコードの生成が行われず、実ワークロードによる負荷と等価にはなりません。

UPDATE UpdateTest SET C2 =C2 WHERE C1 = 1

UPDATE の性能テストを実施する場合には、何らかのデータ変更を伴うクエリで実行する必要があります。

RCSI / スナップショット分離が有効な場合の挙動の違い

上記の検証は、

• Read Committed Snapshot Isolation (RCSI) が無効
• Snapshot 分離レベルが無効

という SQL Server のデフォルトの状態で試してみました。

SQL Server でいずれかを有効 / SQL Database の初期設定 (RCSI 有効 / Snapshot 分離 有効) のように、「行のバージョン情報 (MVCC による動作)」を生成する設定になっている環境では同値による更新の挙動が変わります。

SQL Server で次のクエリを実行して TESTDB の RCSI を有効にしてみます。(これは、ALTER DATABASE [TESTDB] SET ALLOW_SNAPSHOT_ISOLATION ON で Snapshot 分離のみを有効化した場合も同様の挙動となります)

ALTER DATABASE [TESTDB] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO

これで先ほどと同じ、同値による更新を実行してみます。

UPDATE UpdateTest SET C2 = '1 かずあき=8GB' WHERE C1 = 1

先ほどと異なり、同値による更新を実行していますが、先ほどと異なり、実レコードの変更についてトランザクションログのレコードが生成されていることが確認できますね。

RCSI / Snapshot 分離レベルが有効なデータベースで同値による更新を実行した場合については、無効な場合と異なり、レコードの変更についてのトランザクションログのレコードが生成されるという挙動となるようです。

これですが、設定が有効な場合は「レコード内に行のバージョン情報が生成される」ことに起因しているのではないかと思います。

今回、テスト用のデータは PageId 1022240 に格納されていますので、次のようなクエリを実行して、変更前後のデータを確認してみます。

DBCC TRACEON(3604)
DBCC PAGE('TESTDB', 1, 1022240, 3)
UPDATE UpdateTest SET C2 = '1 かずあき=8GB' WHERE C1 = 1
DBCC PAGE('TESTDB', 1, 1022240, 3)
DBCC TRACEOFF(3604)

UPDATE により、同値による更新を実行しているのですが、その前後にページの内容を出力するようにしています。

実際に出力した内容が以下の画像となります。

上が、更新前、下が更新後のページ内の情報となりますが、行のバージョン情報のタイムスタンプとポインターが変わっていることが確認できますね。

RCSI / スナップショット分離レベルが有効なデータベースでは、同値による更新でも、行のバージョン情報の情報を変更する必要があります。

そのため、設定前と比較すると、同値による更新を行った場合に、

• ページ内の行のバージョン情報の更新
• 行のバージョン情報の更新を行うことにより tempdb の行バージョンを生成
• トランザクションログに、レコードの変更用のログを出力する

というような挙動の変化が発生するということになりますので、ワークロードによっては、今までは発生していなかったオーバーヘッドが発生する可能性があります。

まとめ

同値による更新は設定によって挙動が変わりますので、更新系のテストを実施する場合には本投稿のような挙動も、意識しておく必要があるかと思います。