如何監測 SQL Server 查詢對 TempDB 的實際空間消耗

TempDB 是 SQL Server 共享資源，過度消耗會導致全系統效能雪崩。本文提供精確測量單一查詢對 TempDB 讀寫量與內部物件配置的 T-SQL 腳本，助你快速定位「TempDB 殺手」查詢。

TempDB 三大使用場景

1. 內部使用：排序、Hash Join、Work Table、Spool、版本存放區等。
2. 外部使用：使用者建立的臨時表 (#Temp)、表變數 (@TableVar)。
3. 列版本控制：快照隔離、觸發器、MARS、線上索引重建等產生的版本鏈。

Spill to TempDB：查詢執行時記憶體授予不足，溢出資料被迫寫入 TempDB，導致大量 I/O，是效能殺手首選嫌疑對象。

精確測量腳本：單一 Session 查詢前後差值法

DECLARE @read BIGINT, @write BIGINT;

-- 1. 記錄查詢前 TempDB 資料檔 累積讀寫位元組
SELECT @read  = SUM(num_of_bytes_read),
       @write = SUM(num_of_bytes_written)
FROM tempdb.sys.database_files AS dbf
JOIN sys.dm_io_virtual_file_stats(2, NULL) AS fs 
  ON fs.file_id = dbf.file_id
WHERE dbf.type_desc = 'ROWS';

-- 2. 執行待測量查詢（在此處替換你的查詢）
-- SELECT * FROM LargeTable ORDER BY SomeColumn;  -- 會觸發排序溢出

-- 3. 記錄查詢後累積值並計算差值
SELECT 
    tempdb_read_MB  = (SUM(num_of_bytes_read)  - @read)  / 1024.0 / 1024.0,
    tempdb_write_MB = (SUM(num_of_bytes_written) - @write) / 1024.0 / 1024.0,
    internal_use_MB = (
        SELECT internal_objects_alloc_page_count / 128.0
        FROM sys.dm_db_task_space_usage
        WHERE session_id = @@SPID
    )
FROM tempdb.sys.database_files AS dbf
JOIN sys.dm_io_virtual_file_stats(2, NULL) AS fs 
  ON fs.file_id = dbf.file_id
WHERE dbf.type_desc = 'ROWS';

實測案例：爛查詢導致 TempDB 爆增

輸出結果解讀：

• tempdb_read_MB / tempdb_write_MB：查詢造成的 TempDB 物理 I/O 量（含排序溢出、工作表讀寫）。
• internal_use_MB：查詢在執行期間配置的內部物件頁面數（排序、Hash、Spool 等），換算為 MB。

延伸監控：找出佔用 TempDB 最多的 Session

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    tsu.internal_objects_alloc_page_count / 128.0 AS internal_MB,
    tsu.user_objects_alloc_page_count / 128.0   AS user_MB,
    tsu.version_store_reserved_page_count / 128.0 AS version_store_MB
FROM sys.dm_db_task_space_usage AS tsu
JOIN sys.dm_exec_sessions AS s ON s.session_id = tsu.session_id
WHERE tsu.session_id > 50  -- 過濾系統 session
ORDER BY (tsu.internal_objects_alloc_page_count + tsu.user_objects_alloc_page_count) DESC;

常見 TempDB 壓力來源與對策

小結

以差值法測量單查詢 TempDB 消耗，結合 dm_db_task_space_usage 定位內部/使用者/版本存放三大佔用來源，是解決 TempDB 壓力的精準手術刀。建議將測量腳本整合至效能基準測試流程，上線前強制驗證。