SQL Server Buffer Pool の内部構造を徹底解説：Lazy Writer、Checkpoint、DMVまで

はじめに

この記事では、SQL Server の**Buffer Pool（Buffer Cache）**の内部構造を解説します。

InnoDB編、PostgreSQL編、Oracle編に続く第4弾です。SQL Serverもクローズドソースですが、公式ドキュメント、DMV（動的管理ビュー）、そしてPaul Randal氏らのSQL Server Internals系の文献をもとに解説します。

この記事で扱う内容：

• Buffer Poolの基本構造とページ管理
• ハッシュテーブルによるページ検索
• Clock アルゴリズムによるページ置換
• Lazy Writer と Checkpoint の役割分担
• DMVによる可観測性
• 他のDBMSとの比較

---

Buffer Poolの基本

SQL Serverは起動時にメモリの大部分をBuffer Poolとして確保します。ここにデータページ（8KB）をキャッシュして物理I/Oを最小化します。

-- メモリ設定
EXEC sp_configure 'max server memory (MB)', 8192;
RECONFIGURE;

SQL Serverの特徴は動的メモリ管理です。ワークロードに応じてBuffer Poolのサイズを自動的に拡大・縮小します。OSのメモリ通知API（QueryMemoryResourceNotification）を使って、システム全体のメモリ状況を監視しています。

ページの構造

SQL Serverのページは8KBで、各ページにはBuffer Descriptor（BUF構造体）が紐づきます：

-- Buffer Descriptorの情報をDMVで確認
SELECT
    database_id,
    file_id,
    page_id,
    page_type,           -- DATA_PAGE, INDEX_PAGE, TEXT_MIX_PAGE など
    row_count,
    free_space_in_bytes,
    is_modified           -- ダーティページかどうか
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID('MyDatabase')
ORDER BY page_id;

ハッシュテーブルによるページ検索

InnoDB・PostgreSQL・Oracleと同じく、SQL Serverもハッシュテーブルでページを検索します。キーは(database_id, file_id, page_id)の組み合わせです。

ハッシュバケットはBUF_HASH_TABLEで管理され、パーティション分割されています。各パーティションはラッチで保護されます。

---

ページ置換：Clock アルゴリズム

SQL ServerはPostgreSQLと同様にClockアルゴリズムを使います（LRUではありません）。

各バッファには参照ビット（reference bit）があり、アクセスされるとセットされます。ページを追い出す必要があるとき、Clock の針が回りながら：

1. 参照ビットが1 → 0にリセットして次へ（セカンドチャンス）
2. 参照ビットが0 → このページを追い出し候補として選択

これはPostgreSQLのusage_count（0〜5の範囲）より単純な実装です。PostgreSQLは複数回のチャンスを与えますが、SQL Serverは基本的に1回のセカンドチャンスです。

5つのDBMSの置換アルゴリズム比較

DBMS	アルゴリズム	特徴
InnoDB	LRU（New/Old Sublist）	リスト構造、時間ベースの昇格
PostgreSQL	Clock Sweep（usage_count 0〜5）	複数回のセカンドチャンス
Oracle	LRU + Touch Count	回数ベースの昇格、Hot/Cold分割
SQL Server	Clock（参照ビット）	シンプルなセカンドチャンス
Db2	LRU / FIFO / NONE（選択可能）	ワークロードに応じて切り替え

---

Lazy Writer と Checkpoint

SQL Serverには2つのバックグラウンドプロセスがダーティページを書き出します。

Lazy Writer

Lazy Writerはメモリ圧迫時にFreeページを確保するためのプロセスです：

Lazy Writer の動作：
    │
    ├─ 1. Free Listのページ数が閾値を下回ったら起動
    │
    ├─ 2. Buffer Pool内のページをClock順に走査
    │     参照ビット=0 かつ ダーティでない → Free Listに移動
    │     参照ビット=0 かつ ダーティ → ディスクに書き出してからFree Listに移動
    │     参照ビット=1 → 0にリセットしてスキップ
    │
    └─ 3. 十分なFreeページが確保できたら休止

-- Lazy Writerの活動を監視
SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Lazy writes/sec', 'Free list stalls/sec', 'Free pages');

Lazy writes/secが高い値を示す場合、Buffer Poolのメモリが不足しているサインです。max server memoryの増加を検討すべきです。

Free list stalls/secが0より大きい場合、ユーザースレッドがFreeページを待っている状態で、パフォーマンスに直接影響します。

Checkpoint

Checkpointはリカバリ時間を短縮するためにダーティページを書き出します：

Checkpoint の動作：
    │
    ├─ 1. 定期的に起動（recovery interval設定に基づく）
    │     または手動で CHECKPOINT コマンド実行
    │
    ├─ 2. すべてのダーティページをディスクに書き出す
    │     ※ ページはBuffer Poolに残る（追い出さない）
    │
    └─ 3. トランザクションログにチェックポイントレコードを書く
          → リカバリ時はここから再開すればよい

Lazy Writer vs Checkpoint の違い

	Lazy Writer	Checkpoint
目的	Freeページの確保（メモリ管理）	リカバリ時間の短縮（耐障害性）
トリガー	Freeページ不足	定期的 / ログ量 / 手動
ダーティページ	書き出してFree Listに移動	書き出すがBuffer Poolに残す
クリーンページ	Free Listに移動	何もしない

これはInnoDBのpage cleaner（Batch Flush）が両方の役割を兼ねているのとは対照的です。SQL Serverは役割を明確に分離しています。

他のDBMSとの比較

役割	InnoDB	PostgreSQL	Oracle	SQL Server
メモリ確保用	page cleaner（LRU flush）	BGWriter	DBWn	Lazy Writer
リカバリ用	page cleaner（Flush List flush）	Checkpointer	CKPT + DBWn	Checkpoint
緊急フラッシュ	Single Flush	victim書き出し	DBWnに要求	Eager Writer

SQL ServerにはさらにEager Writerがあり、大量の一括操作（BULK INSERT等）時にダーティページを積極的に書き出します。

---

DMVによる可観測性

SQL Serverの強みの一つは、Buffer Poolの状態をDMVで詳細に確認できることです。

Buffer Poolの使用状況

-- データベースごとのBuffer Pool使用量
SELECT
    DB_NAME(database_id) AS database_name,
    COUNT(*) AS cached_pages,
    COUNT(*) * 8 / 1024 AS cached_MB,
    SUM(CASE WHEN is_modified = 1 THEN 1 ELSE 0 END) AS dirty_pages
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY cached_pages DESC;

Buffer Poolのヒット率

-- Buffer Cache Hit Ratio
SELECT
    (a.cntr_value * 1.0 / b.cntr_value) * 100 AS buffer_cache_hit_ratio
FROM sys.dm_os_performance_counters a
JOIN sys.dm_os_performance_counters b
    ON a.object_name = b.object_name
WHERE a.counter_name = 'Buffer cache hit ratio'
  AND b.counter_name = 'Buffer cache hit ratio base';

Page Life Expectancy（PLE）

-- ページがBuffer Poolに留まる平均時間（秒）
SELECT cntr_value AS page_life_expectancy_seconds
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
  AND object_name LIKE '%Buffer Manager%';
-- 300秒（5分）以上が望ましい

PLEはSQL Server固有の重要な指標です。この値が低い場合、Buffer Poolからページが頻繁に追い出されていることを意味し、メモリ不足のサインです。

---

まとめ

テーマ	SQL Server	InnoDB	PostgreSQL	Oracle
置換アルゴリズム	Clock（参照ビット）	LRU（New/Old）	Clock Sweep（usage_count）	LRU + Touch Count
メモリ管理	動的（OS連携）	静的（設定値固定）	静的 + OSキャッシュ	静的（SGA内）
書き出し分離	Lazy Writer / Checkpoint	page cleaner（兼務）	BGWriter / Checkpointer	DBWn / CKPT
可観測性	DMV（非常に豊富）	SHOW STATUS	pg_buffercache	X $BH,V$ LATCH
スキャン耐性	参照ビットリセット	New/Old Sublist	Ring Buffer	Direct Path Read

参考

• Memory Management Architecture Guide – Microsoft
• sys.dm_os_buffer_descriptors – Microsoft
• Inside the Storage Engine: What’s in the buffer pool? – Paul Randal
• SQL Server CHECKPOINT, Lazy Writer, Eager Writer – SQLShack