SE の雑記

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

SQL Server のゴーストクリーンアップタスクの基本動作を見てみる その 1

leave a comment

先日は DELETE / TRUNCATE について投稿をしてみました。

SQL Server の DELETE の基本動作を見てみる
SQL Server の TRUNCATE TABLE の基本動作を見てみる

本日は DELETE した際のゴーストレコード (非実体レコード) を削除するゴーストクリーンアップタスク (GhostCleanupTask) について少し見ていきたいと思います。

■事前準備でトレースフラグを設定

ディスクの i/O を少し制御したいのでまずは 2 種類のトレースフラグを設定しておきたいと思います。

  • -T661
    • ゴーストレコードの削除処理を無効
  • -T3505
    • 自動チェックポイントを無効

作業負荷の高いパフォーマンスで実行するときのチューニング SQL Server 2005 の SQL Server 2008 のオプション
INF: コントロール SQL Server のチェックポイントの動作をトレース フラグ 3505 を使用します。

ゴーストレコードを削除するときは実データの削除が発生しますのでデータファイルに対して I/O が発生します。
チェックポイントの発生時にもダーティーページの削除が発生しますのでデータファイルに対しての I/O が発生します。

この 2 種類の処理を手動で実行するようにして、ディスク I/O を制御できる状態にしておきます。

以下のクエリを実行して上記のトレースフラグを設定しておきます。

DBCC TRACEON(661, -1)
DBCC TRACEON(3505, -1)

 

[DBCC TRACESTATUS] を実行して、[661] と [3505] が認識されていれば、準備は完了です
image

 

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

DELETE の投稿をした際にも触れましたゴーストレコード (非実体レコード) もう少し踏み込んで見ていきたいと思います。

今回は以下のテーブルで検証をしています。

CREATE TABLE [dbo].[Table_1](
    [Col1] [char](800) NOT NULL,
    [Col2] [char](800) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
    [Col1] ASC
)
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Table_1] ON [dbo].[Table_1]
(
    [Col2] ASC
) ON [PRIMARY]

 

クラスタ化インデックスと非クラスタ化インデックスを持つシンプルなテーブルを作成しています。
このテーブルに以下のクエリでレコードを挿入します。

SET NOCOUNT ON
GO
INSERT INTO Table_1
VALUES (NEWID(), NEWID())
GO 15

 

これでゴーストレコードを確認する準備が整いました。

DELETE を実行する前にクラスタ化インデックスと非クラスタ化インデックスのページ情報を確認しておきます。
# DBCC PAGE のページ番号は私の環境での値ですので、この値は DBCC IND の状態に応じて適宜変更します。

DBCC IND(N’TEST’, N’dbo.Table_1′, 1)
DBCC IND(N’TEST’, N’dbo.Table_1′, 2)

DBCC TRACEON(3604)
DBCC PAGE(N’TEST’, 1,432, 3)
DBCC PAGE(N’TEST’, 1,440, 3)
DBCC TRACEOFF(3604)

 

[クラスタ化インデックスのスロット 0]

Slot 0 Offset 0x6a7 Length 1607
Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 1607

Memory Dump @0x000000001514A6A7

0000000000000000:   10004406 31313534 38423039 2d363039 372d3431
0000000000000014:   43322d39 4332312d 41333930 32333232 31343432

[非クラスタ化インデックスのスロット 0]

Slot 0 Offset 0x60 Length 1604

Record Type = INDEX_RECORD          Record Attributes =  NULL_BITMAP    Record Size = 1604

Memory Dump @0x000000001615A060

0000000000000000:   16323134 38424132 452d4633 36342d34 3646422d
0000000000000014:   42414141 2d343236 36314131 32374435 44202020

 

それでは、[DELETE FROM Table_1] を実行して、テーブルのデータを削除し、再度ページ情報を確認します。

[クラスタ化インデックスのスロット 0]

Slot 0 Offset 0x6a7 Length 1607

Record Type = GHOST_DATA_RECORD     Record Attributes =  NULL_BITMAP    Record Size = 1607

Memory Dump @0x000000001514A6A7

0000000000000000:   1c004406 31313534 38423039 2d363039 372d3431
0000000000000014:   43322d39 4332312d 41333930 32333232 31343432

 

[非クラスタ化インデックスのスロット 0]

Slot 0 Offset 0x60 Length 1604

Record Type = GHOST_INDEX_RECORD    Record Attributes =  NULL_BITMAP    Record Size = 1604

Memory Dump @0x000000001615A060

0000000000000000:   1a323134 38424132 452d4633 36342d34 3646422d
0000000000000014:   42414141 2d343236 36314131 32374435 44202020

 

DELETE を実行することで削除対象となったレコードは非実体レコードとなります。
データ行の場合は [GHOST
_DATA_RECORD]、インデックス行の場合は [GHOST_INDEX_RECORD] となります。
DELETE 前後で特定のバイトが変わっていることが確認できるかと思います。

[クラスタ化インデックスのスロット 0]

前 : 0000000000000000:   10004406 31313534 38423039 2d363039 372d3431
後 : 0000000000000000:   1c004406 31313534 38423039 2d363039 372d3431

前 : 0000000000000014:   43322d39 4332312d 41333930 32333232 31343432
後 : 0000000000000014:   43322d39 4332312d 41333930 32333232 31343432

[非クラスタ化インデックスのスロット 0]

前 : 0000000000000000:   16323134 38424132 452d4633 36342d34 3646422d
後 : 0000000000000000:   1a323134 38424132 452d4633 36342d34 3646422d

前 : 0000000000000014:   42414141 2d343236 36314131 32374435 44202020
後 : 0000000000000014:   42414141 2d343236 36314131 32374435 44202020

 

DELETE では対象となったレコードのステータスビットをゴーストレコード用のものに変更を行います。
ゴーストレコードかどうかはステータスビットの状態によって判断できるということが確認できますね。

ゴーストレコードは以下のクエリでも確認することができます。

SELECT
    index_type_desc, index_level,
    record_count, ghost_record_count
FROM
    sys.dm_db_index_physical_stats(
    DB_ID(N’TEST’), OBJECT_ID(N’dbo.Table_1′),
    NULL, NULL, ‘DETAILED’)
ORDER BY index_id ASC,index_level DESC

こちらは 100 件のレコードが格納されているときの状態です。
image

クラスタ化インデックスも非クラスタ化インデックスも B-Tree (Balanced Tree) 構造で管理されます。
index_level の数字の低いもの程リーフに近くなります。
index_level = 0 の record_count は両方とも 100 となっているのが確認できますね。

それでは DELETE を実行した後に再度情報を取得してみます。
image
リーフのレコードがすべてゴーストレコード (ghost_record_count) になっているのが確認できますね。
# 全件を削除してもルートと中間ノードにはゴーストレコードが発生していないのが興味深いですね。

[DBCC FORCEGHOSTCLEANUP] を実行してゴーストクリーンアップタスクを実行するとこのようになります。
image

各ノードからゴーストレコードが削除されているのが確認できますね。
# 1 レコードがゴーストレコードとして残ってしまう理由がいまいちわかっていないのですよね…。

 

■ゴーストクリーンアップタスクのディスク I/O を確認してみる

それでは、以下のクエリを実行して DELETE 時のディスク I/O を確認してみます。
# I/O を確認したいので対象となるデータは 10,000 件に増やしています。

DELETE FROM Table_1
WAITFOR DELAY ’00:00:10′
CHECKPOINT
WAITFOR DELAY ’00:00:10′
DBCC FORCEGHOSTCLEANUP
WAITFOR DELAY ’00:00:10′
CHECKPOINT

パフォーマンスモニタで取得した値がこちらです。
image
image

 

青がデータファイル / 赤がログファイルに対してのディスク I/O  になります。
10 秒間隔でクエリを実行していますので、最初の山から

  1. DELETE FROM Table_1
    • 削除対象レコードをデータファイルから Read
    • 削除のログレコードをログファイルに Write
  2. CHECKPOINT
    • メモリ上のダーティーページをデータファイルに Write
  3. DBCC FORCEGHOSTCLEANUP
    • ゴーストレコードクリーンアップタスクのログレコードをログファイルに Write
  4. CHECKPOINT
    • メモリ上のダーティーページをデータファイルに Write

となります。

今度はダーティーページの状態を見てみたいと思います。

DELETE 直後のメモリ上のページの状態を以下のクエリで取得します。

SELECT
    page_type,
    page_level,
    SUM(row_count) As row_count,
    SUM(free_space_in_bytes) free_space_in_bytes,
    is_modified
FROM
    sys.dm_os_buffer_descriptors
WHERE
    database_id = DB_ID()
    AND
    page_type IN (N’INDEX_PAGE’, N’DATA_PAGE’)
GROUP BY
     page_type,
     page_level,

     is_modified
ORDER BY
    page_type ASC,
    page_level ASC

image

[is_modified = 1] がメモリ上のデータは更新されているがディスク上のデータは更新されていないものになります。
全件 DELETE をした場合、大半のページがダーティーページになっていることが確認できますね。

チェックポイントを実行するとこのようになります。
image

メモリ上にのみ更新されていたデータがディスクに書き込まれますので、ダーティーページがなくなります。

それではこの状態 (メモリ上にデータがロードされている) で、 [DBCC FORCEGHOSTCLEANUP] を実行してダーティーページを確認してみます。
image

メモリ上に存在しているゴーストページはダーティーページとなりますので、[is_modified = 1] のページが発生していますね。
それでは CHECKPOINT を実行して再度ページ情報を取得します。
image

ダーティーページが消えていますのでメモリ上のデータがディスクに書き込まれているのが確認できます。

DELETE してゴーストレコードにした後のチェックポイント / ゴーストレコードを削除するための処理を実行した後のチェックポイントでデータファイルに対して書き込みが発生したのはこのような動きになっているからだと思います。

長々と書いてしまったので今回はここまでで。
次の投稿でもゴーストクリーンアップタスクについて引き続きみていきたいと思います。

Share

Written by Masayuki.Ozawa

4月 6th, 2011 at 11:08 pm

Posted in SQL Server

Tagged with

«
»

Leave a Reply