セージ の メモ書き

メモこそ命の恩人だ

SQL Server - バッファキャッシュ(バッファプール)

バッファキャッシュ(バッファプール)

  • ストレージのデータをメモリ上にコピーした領域。
  • データのアクセス速度向上が目的。
  • ストレージ領域と同じ構造(8KBのページで管理)

キャッシュの削除

-- ダーティページもクリアしたいなら実行
-- checkpoint;
DBCC DROPCLEANBUFFERS;
  • クエリ実行後、バッファが即座にクリアされる。
    • 即クリアされる仕様なので、一定時間の待機は不要。
  • ダーティページの場合、クリアされない。
    • クリアしたいなら、checkpoint を実行する。
    • セージブログの "チェックポイント" で検索。

キャッシュのページ情報を確認

sys.dm_os_buffer_descriptors (Transact-SQL) - SQL Server | Microsoft Docs

  • "sys.dm_os_buffer_descriptors" 動的管理ビューを使用する。
  • キャッシュ上の各ページが、レコードとして表示される。
    • ページID、ページタイプ、データ行数、空き領域サイズ etc.

各ページ情報(基本)

  • 以下で表示させるとわかりやすい。
    • database_id → DB名の表示
    • page_id → オブジェクト名の表示(テーブル名など)

こんな感じ。メタデータ関連のキャッシュも結構ある。

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'

ページの集計情報

  • レコード数より、バッファ上のページ数がわかる。
  • database_id で集計し、レコード数の COUNT(*) を行う。
  • ページ数からページサイズもわかる。
    • 1ページ:8,192バイト
    • ページ数*8,192
  • 各レコードの空き領域の総和から、バッファ上の全空き領域サイズがわかる。
SELECT
    DB_NAME(database_id) as DB名,
    COUNT(*) AS バッファ上のページ数,
    COUNT(*) * 8192 AS [バッファ上のページサイズ(byte)],
    SUM(free_space_in_bytes) AS [空き領域(byte)]
FROM
    sys.dm_os_buffer_descriptors
GROUP BY
    database_id

試しに、1レコード約5,000バイトのテーブルで、バッファのページ情報を確認してみる。

USE SampleDb;

-- 1レコードに約5,000バイトのデータを登録できる。
-- 1ページ8,060バイトが上限なので、
-- 1レコード1ページで、1ページに約3,000バイトの空き領域が生じる。
CREATE TABLE SampleTable1 (Id int PRIMARY KEY, Data1 char (5000));  

-- 初期化(何度かテストするなら圧縮+キャッシュ削除して試す。ページ数を分かりやすくするため)
-- TRUNCATE TABLE SampleTable1;
DBCC SHRINKFILE ('SampleDb' , 0)
checkpoint;
DBCC DROPCLEANBUFFERS;
GO

-- データ登録前
SELECT
    DB_NAME(database_id) as DB名,
    COUNT(*) AS バッファ上のページ数,
    COUNT(*) * 8192 AS [バッファ上のページサイズ(byte)],
    SUM(free_space_in_bytes) AS [空き領域(byte)]
FROM
    sys.dm_os_buffer_descriptors
GROUP BY
    database_id


--5,000バイト * 1,000件 = 5,000,000バイト (5Mバイト)
DECLARE @Count INT = 1;
WHILE @Count <= 1000 BEGIN
    INSERT INTO SampleTable1 (Id, Data1) VALUES (@Count, 0x01);
    SET @Count += 1;
END


-- データ登録後
SELECT
    DB_NAME(database_id) as DB名,
    COUNT(*) AS バッファ上のページ数,
    COUNT(*) * 8192 AS [バッファ上のページサイズ(byte)],
    SUM(free_space_in_bytes) AS [空き領域(byte)]
FROM
    sys.dm_os_buffer_descriptors
GROUP BY
    database_id

  • 1,000件登録した結果...
  • ページ数:1,012 増加
    • 予想通り。
    • 管理用ページなども含むので12バイト多い。
    • page_type で GROUP BY すればわかる。
  • ページサイズ:8,290,304 バイト増加
    • これは、クエリでページ数に 8,192 かけてる数値。
  • 空き領域:3,114,311 バイト増加
    • 1ページ約3,000バイトの空き領域になる予想。
    • なので、ほぼ想定通りの空き領域になった。

キャッシュの管理情報

SQL Server の Buffer Manager オブジェクト - SQL Server | Microsoft Docs

SELECT * FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%';
  • "sys.dm_os_performance_counters" 動的管理ビューを使用する。
  • Buffer Manager オブジェクトの情報を参照する。
項目 内容
Page life expectancy バッファキャッシュに存在していた平均時間(秒)
Buffer cache hit ratio キャッシュヒット率

拡張バッファキャッシュ

バッファー プール拡張機能 - SQL Server | Microsoft Docs

↑ この図、わかりやすい。クエリ全体の流れがわかる。

  • SQL Server 2014 以降で使用できる。
  • SSD をバッファキャッシュとして使用できる機能。
  • レベル1 (L1) と レベル2 (L2) のバッファキャッシュで構成される。
    • L1:メモリ
    • L2:SSD(これが拡張バッファ部分)
  • L2 のデータはサービス停止により削除される。

Q&A

Q. バッファキャッシュの最大サイズは?

This memory clerk keeps track of commonly the largest memory consumer inside SQL Server - data and index pages. Buffer Pool or data cache keeps data and index pages loaded in memory to provide fast access to data.

Q. 特定ページを意図的にキャッシュする方法は?

  • ページの仕組みである場合、意図的にキャッシュはできない。
    • 該当ページにアクセスするクエリを定期的に実行するくらい ...
    • 専用コマンドなどは用意されてなさそう。
    • oracle の KEEP プール相当の機能もなさそう。(SQL Server 2019時点)
  • メモリ最適化テーブルの場合、データをメモリ上にキャッシュできる。

Q. キャッシュからクリアされるタイミングは?

  • 使用頻度に応じてクリアされる。(LRU)
  • なので、ページごとの更新日時情報を管理してる。
  • ページヘッダーに日時情報はなさそう。
  • ページIDと更新日時の管理用ページがどこかにあるはず。。。



以上