セージ の メモ書き

メモこそ命の恩人だ

SQL Server - ダーティページ/チェックポイント

ダーティページ (Dirty page)

ページの書き込み - SQL Server | Microsoft Learn

  • バッファキャッシュ上の変更されたページのこと。
    • ストレージへの書込みが完了していない状態。
    • なので、ダーティ? 汚れてる?
  • 即座にストレージに反映しない理由
    • パフォーマンス向上のため。
  • ストレージに反映するタイミング
    • チェックポイント(後述参照)
    • レイジー書き込み
    • 集中書き込み
  • ダーティページの判別方法

チェックポイント

データベース チェックポイント (SQL Server) - SQL Server | Microsoft Learn

  • ダーティページをストレージに反映する処理の一つ。
  • ストレージへの反映を "フラッシュ" と呼ぶ。
  • チェックポイントの動作は4通り。

自動チェックポイント

https://docs.microsoft.com/ja-jp/sql/relational-databases/logs/database-checkpoints-sql-server#AutomaticChkpt

  • recovery interval オプションを使用。既定値:0
  • 復旧に必要な時間を設定する。
  • チェックポイント発行動作
    • DBエンジンが、設定した復旧時間内で処理できる最大ログレコード数を見積もる。
    • そのレコード数に達した場合、チェックポイントが動作する。
  • 欠点

間接チェックポイント

https://docs.microsoft.com/ja-jp/sql/relational-databases/logs/database-checkpoints-sql-server#IndirectChkpt

  • target_recovery_time を使用。既定値:60秒
  • SQL Server 2012 で導入された機能。
  • 自動チェックポイントの代替機能。
    • なので、"間接" が推奨。
  • "自動" と "間接" の設定があり、"間接" が優先して動作する。
  • 復旧間隔が長い場合
    • チェックポイントの発生頻度が少なくなる。
    • 利点:IOアクセスが減る。
    • 欠点:復旧時間が長くなる。
  • 復旧間隔が短い場合
    • 前述と逆の特徴になる。

以下で内容を確認できる。

SELECT name, target_recovery_time_in_seconds FROM sys.databases

手動チェックポイント

CHECKPOINT (Transact-SQL) - SQL Server | Microsoft Learn

  • 手動でチェックポイントを行う。
  • CHECKPOINT コマンドを使用する。
USE SampleDb;
IF object_id(N'SampleTable1') is not null DROP TABLE SampleTable1;
CREATE TABLE SampleTable1 (Id int PRIMARY KEY, Data1 char (5000));  
--TRUNCATE TABLE SampleTable1;
GO

INSERT INTO SampleTable1 (Id, Data1) VALUES (1, 'xxx');
GO

SELECT 
    DB_NAME(dobd.database_id), 
    OBJECT_NAME(ddpi.object_id), 
    dobd.* 
FROM sys.dm_os_buffer_descriptors as dobd
CROSS APPLY sys.dm_db_page_info (DB_ID(), 1, dobd.page_id, 'DETAILED') as ddpi
WHERE DB_NAME(dobd.database_id) = 'SampleDb' 
AND OBJECT_NAME(ddpi.object_id) = 'SampleTable1';

この時点で is_modified が 1 のダーティページがあることを確認。

-- 手動チェックポイント
CHECKPOINT;

-- チェックポイント後
SELECT 
    DB_NAME(dobd.database_id), 
    OBJECT_NAME(ddpi.object_id), 
    dobd.* 
FROM sys.dm_os_buffer_descriptors as dobd
CROSS APPLY sys.dm_db_page_info (DB_ID(), 1, dobd.page_id, 'DETAILED') as ddpi
WHERE DB_NAME(dobd.database_id) = 'SampleDb' 
AND OBJECT_NAME(ddpi.object_id) = 'SampleTable1';

ダーティページの値が、1 → 0 に変化したことを確認。
コマンドにより、フラッシュできたことを確認。

内部チェックポイント

https://docs.microsoft.com/ja-jp/sql/relational-databases/logs/database-checkpoints-sql-server#EventsCausingChkpt

  • DB管理系のイベント発生時、内部で自動的にチェックポイントされる。
  • バックアップ、サービス停止、設定変更 etc.


Q&A

Q.ダーティページはバッファキャッシュの削除コマンドで残る?

  • 残る。
-- ダーティページの状態で、削除コマンドを実行しても、バッファキャッシュから消えないことを確認。
DBCC DROPCLEANBUFFERS;
-- チェックポイントすれば、バッファキャッシュから消えたことを確認。
CHECKPOINT;
DBCC DROPCLEANBUFFERS;

自動チェックポイントのタイミングは?

  • 時間は関係ないことを確認。
    • ダーティページの状態にして、1時間待機。ダーティページのまま。
  • レコード数?
    • ToDo
    • 何件が閾値か? どういう仕組み?調査したい。



以上