SQL Server の DELETE の基本動作を見てみる at SE の雑記

レコードを削除するために DELETE という DML (データ操作言語) があります。

今回の投稿はこの DELETE の基本的な動作を見ていきたいと思います。

英語になりますが以下の情報でこの辺りについては細かく解説されています。
Inside the Storage Engine: Ghost cleanup in depth

Contents

1 ■DELETE 実行時のログレコード
2 ■ゴーストレコードについて
3 ■ゴーストクリーンアップタスク

■DELETE 実行時のログレコード

最初に DELETE を実行した際にログにどのようなレコードが記録されるかを確認してみます。

今回 DELETE で使用しているクエリがこちらです。

BEGIN TRAN DELETE_TRAN
DELETE FROM Table_2 WHERE Col1 = (SELECT TOP 1 Col1 FROM Table_2)
COMMIT TRAN DELETE_TRAN

ログのレコードを検索しやすいようにトランザクションに名前を付けて DELETE を実行しています。

それでは実際のログを確認してみたいと思います。
ログレコードは DBCC LOG で確認することができるのですが、[fn_dblog} を使用すると検索も容易にできますので今回はこちらを使用します。

ログを検索するためのクエリがこちらです。

SELECT [Current LSN],[Page ID], [Operation], [Context], [Transaction ID], [Transaction Name]
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] =
(SELECT [Transaction ID] FROM fn_dblog(NULL, NULL) WHERE [Transaction Name] = ‘DELETE_TRAN’)

実行すると以下のような結果を取得できます。

[DELETE_TRAN] というトランザクションでページ ID [1:3400] (0xd48 を 10 進数に変換) に LOP_DELETE_ROWS という操作が行われていることが確認できますね。

■ゴーストレコードについて

DELETE でレコードを削除した時のログの [Context] ですが [LCK_MARK_AS_GHOST] となっています。

DELETE はレコードを削除するという処理ですが、削除したレコードは即時にデータベースから消えるわけではりません。
削除直後は [ゴーストレコード] としてデータベース上に残っています。

それではゴーストレコードの状態を [DBCC PAGE] を使用して確認をしてみます。

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

削除があったページのヘッダーは以下の状態になっています。

Metadata: ObjectId = 565577053      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 104                       m_slotCnt = 10                      m_freeCnt = 7006
m_freeData = 2771                   m_reservedCnt = 0                   m_lsn = (1327:4884:2)
m_xactReserved = 0                  m_xdesId = (0:5238031)              m_ghostRecCnt = 1
m_tornBits = 1007376978             DB Frag ID = 1

[m_ghostRecCnt = 1] となっているのが確認できます。
このヘッダ情報がページ内にどれぐらいゴーストレコードが存在するかを表したものになります。
今回はページ内に 1 レコード存在している状態です。

それでは実レコードも確認をしてみます。

Slot 0, Offset 0x9fd, Length 107, DumpStyle BYTE

Record Type = GHOST_DATA_RECORD Record Attributes = NULL_BITMAP Record Size = 107

Memory Dump @0x00000000160CA9FD

0000000000000000:   1c006800 31454630 37383244 2d414444 432d3434 †..h.1EF0782D-ADDC-44
0000000000000014:   38452d42 3833302d 41393045 38313632 37464535 †8E-B830-A90E81627FE5
0000000000000028:   20202020 20202020 20202020 20202020 20202020 †
000000000000003C:   20202020 20202020 20202020 20202020 20202020 †
0000000000000050:   20202020 20202020 20202020 20202020 20202020 †
0000000000000064:   20202020 010000†††††††††††††††††††††††††††††††    …

今回削除対象となったのは上記のレコードなのですが、[Record Type] が [GHOST_DATA_RECORD] となっているのが確認できます。

これがゴーストレコードになります。

SQL Server では DELETE を実行しても即時には対象のデータをデータベース上から削除するという事はしていません。
DELETE 直後はデータをゴーストレコードとして設定し、データベース上には存在しているが無効なレコードとしています。

■ゴーストクリーンアップタスク

バックグラウンドで稼働している、[ゴーストクリーンアップタスク] (GhostCleanupTask) が実行されたタイミングでデータベース上からデータが削除されます。

ゴーストクリーンアップタスクの情報ですが以下のクエリで確認することができます。

SET NOCOUNT ON
GO
SELECT * INTO #tmp FROM sys.dm_exec_requests WHERE 1 = 0

DECLARE @i int = 0
WHILE (@i < 1)
BEGIN
    INSERT INTO #tmp SELECT * FROM sys.dm_exec_requests
    WHERE command LIKE ‘%GHOST%’
    SELECT @i = COUNT(*) FROM #tmp
END
SELECT session_id, start_time,status,command FROM #tmp
DROP TABLE #tmp

実行結果がこちらになります。

この [GHOST CLEANUP] のタスクが実行されることで DELETE で削除対象となったレコードの実データ削除が定期的に行われています。

[DBCC FORCEGHOSTCLEANUP] という Undocumented な DBCC コマンドを実行することで手動でゴーストクリーンアップタスクを実行することも可能です。

ゴーストクリーンアップタスクが実行されるとヘッダ情報がこのようになります。

Metadata: PartitionId = 72057594045202432                                Metadata: IndexId = 1
Metadata: ObjectId = 565577053      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 104                       m_slotCnt = 9                       m_freeCnt = 7115
m_freeData = 2878                   m_reservedCnt = 0                   m_lsn = (1327:4894:1)
m_xactReserved = 0                  m_xdesId = (0:5238033)             m_ghostRecCnt = 0
m_tornBits = 1925792517             DB Frag ID = 1

[m_ghostRecCnt = 0] となり、ゴーストレコードが無くなっているのが確認できます。

また、[m_slotCnt = 10] → [m_slotCnt = 9] となっているのも確認ができます。
スロットですが、そのページ内に何レコード (何スロット) 存在しているかをあらわすものになります。
ゴーストレコードが存在している場合は、実レコードとして存在した状態になりますので、スロットにもカウントされています。
ゴーストレコードが削除されると実レコードが消えますので、スロットとしてカウントされなくなります。

ページヘッダで [m_lsn = (1327:4894:1)] となっていますので、このレコードをログから取得してみたいと思います。

SELECT [Current LSN],[Page ID], [Operation], [Context], [Transaction ID], [Transaction Name]
FROM fn_dblog(NULL, NULL)
WHERE [Current LSN] = ‘0000052f:0000131e:0001’

[1327 (0000052F) : 4894 (0000131E) : 1 (0001)] のログレコードを取得しています。

[m_lsn] は対象のページを操作した際の LSN (ログシーケンス番号) になるのですが、対象の LSN で行削除 (EXPUNGE_ROWS) の処理が行われているのが確認できますね。

普通に使っている DELETE という処理の基本動作を考えるだけでも奥が深いですね。

SE の雑記