tempdb の競合 (SQL Server)

症状

PAGELATCH_UP/EX 待機、tempdb の割り当てページ (PFS/GAM/SGAM) の競合、全般的な遅延。

1. 競合を確認する

tempdb のシステムページ (2:1:1 など) での PAGELATCH 待機は、割り当ての競合を示す。

SQL Server

SELECT session_id, wait_type, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%' AND resource_description LIKE '2:%'
ORDER BY wait_duration_ms DESC;

2. tempdb の使用状況を内訳する

誰が tempdb をどれだけ使っているか。内部オブジェクト (ソート/ハッシュのスピル)、バージョンストア、ユーザーオブジェクトのどれか。

SQL Server

SELECT * FROM sys.dm_db_file_space_usage;
SELECT TOP 10 session_id, user_objects_alloc_page_count, internal_objects_alloc_page_count
FROM sys.dm_db_task_space_usage ORDER BY internal_objects_alloc_page_count DESC;

3. ファイル構成を修正する

tempdb には同じサイズの複数のデータファイルを、CPU コア数と同数 (最大 8) 使う。SQL 2016+ ではセットアップ時にこれが推奨され、旧バージョンではトレースフラグ 1117/1118 を使う。ファイルは高速ディスク (ローカル NVMe) に置く。

4. 暴走クエリを見つける

大きなソート/ハッシュのスピルを伴うクエリが tempdb を膨張させる。メモリ許可が不十分なクエリや悪い計画のクエリを対象とし、インデックス/計画でスピルを減らす。

SQL Server

SELECT TOP 10 t.text, mg.requested_memory_kb, mg.granted_memory_kb
FROM sys.dm_exec_query_memory_grants mg CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) t
ORDER BY mg.requested_memory_kb DESC;

5. 他のデータベースでの類似事象

同じ「一時領域の競合」は、Oracle では TEMP 表領域 (v$tempseg_usage / v$sort_usage)、PostgreSQL では temp_files と work_mem の超過である。根本的な対処は常に同じ: スピルするクエリを修正し、一時領域を高速化する。