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の置換アルゴリズム比較

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 の違い

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

他のDBMSとの比較

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からページが頻繁に追い出されていることを意味し、メモリ不足のサインです。

まとめ

参考

• 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