tempdb の競合と領域使用量を診断する

コマンド

-- 1) Current tempdb page latch waits (2:1:1 PFS, 2:1:2 GAM, 2:1:3 SGAM):
SELECT wt.session_id, wt.wait_type, wt.wait_duration_ms, wt.resource_description
FROM   sys.dm_os_waiting_tasks wt
WHERE  wt.wait_type LIKE N'PAGELATCH[_]%'
  AND  wt.resource_description LIKE N'2:%';

-- 2) Sessions using the most tempdb (MB):
SELECT TOP (20) su.session_id, s.login_name, s.program_name,
       (su.user_objects_alloc_page_count - su.user_objects_dealloc_page_count) * 8 / 1024         AS user_obj_mb,
       (su.internal_objects_alloc_page_count - su.internal_objects_dealloc_page_count) * 8 / 1024 AS internal_obj_mb
FROM   sys.dm_db_session_space_usage su
  JOIN sys.dm_exec_sessions s ON s.session_id = su.session_id
WHERE  s.is_user_process = 1
ORDER BY su.user_objects_alloc_page_count + su.internal_objects_alloc_page_count
       - su.user_objects_dealloc_page_count - su.internal_objects_dealloc_page_count DESC;

-- 3) Overall breakdown and version store:
SELECT SUM(unallocated_extent_page_count) * 8 / 1024       AS free_mb,
       SUM(user_object_reserved_page_count) * 8 / 1024     AS user_obj_mb,
       SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_obj_mb,
       SUM(version_store_reserved_page_count) * 8 / 1024   AS version_store_mb
FROM   tempdb.sys.dm_db_file_space_usage;

SELECT DB_NAME(database_id) AS db, reserved_page_count * 8 / 1024 AS version_store_mb
FROM   sys.dm_tran_version_store_space_usage
ORDER BY reserved_page_count DESC;

-- 4) File count / equal sizing and memory-optimized metadata:
SELECT name, type_desc, size * 8 / 1024 AS size_mb, growth * 8 / 1024 AS growth_mb, is_percent_growth
FROM   tempdb.sys.database_files;
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS tempdb_metadata_memopt;

概要

tempdb の割り当てページ (PFS/GAM/SGAM) とシステムテーブルに対する PAGELATCH 待機、tempdb を最も多く使用しているセッション (一時テーブルとソート / ハッシュのスピルの別)、バージョンストアを肥大化させているデータベース、ファイル構成を表示する。

使用する場面

一時テーブルを多用するワークロードで PAGELATCH_UP/EX 待機が増加した場合、tempdb が満杯になった場合 (1105/9002)、またはバージョンストアが増大した場合。

注意

ページ 2:1:1、2:1:2、2:1:3 および 8088 の倍数の位置にある PFS ページは割り当ての競合を意味する。対処は、同じサイズ・同じ拡張設定の複数データファイルである (8 まではコアあたり 1 つ、その後は 4 つずつ追加)。システムテーブルページの競合には、2019 以降でメモリ最適化 tempdb メタデータが利用できる (再起動が必要)。バージョンストアが増大する場合は、最も古いオープン中のスナップショットトランザクションを特定する。tempdb の圧縮は対処にならない。読み取り専用クエリのため安全。